Three-Tier Design & Security

Primentra separates the browser, the server and the database into three tiers with a fixed responsibility each. This page describes the tiers, the security controls on them, and the path a single request takes.

The three tiers

Frontend (browser)

React and Vite run entirely in the browser. The frontend renders the UI, holds local state, and calls the REST API for every data operation. No business logic lives here — it is a display and interaction layer.

API server (Express + Node.js)

The Express server is a thin pass-through on port 3001. It validates the incoming request and calls the matching stored procedure. It contains zero raw SQL.

Database (SQL Server)

All business logic lives in stored procedures — the shipped schema defines more than 180 of them. The EAV (Entity-Attribute-Value) schema stores attribute values without a schema migration per field. Every write runs inside a transaction that the stored procedure opens and commits.

Security layers

Primentra applies defence in depth across all three tiers.

Transport

Production traffic is TLS 1.2 or later, terminated by IIS. The API server binds to localhost:3001 only, so it is never directly reachable from outside the machine.

Input validation

The API validates every request body before the database is reached. An invalid request returns HTTP 400 immediately.

SQL injection prevention

Every database call is a parameterized stored procedure call through the mssql driver. The service account (primentra_svc) holds SELECT, INSERT, UPDATE, DELETE, EXECUTE and REFERENCES on the dbo schema, plus membership of db_ddladmin so it can create the Full-Text catalog and indexes. It has no server-level permissions and no access outside the Primentra database.

RBAC permissions

Users belong to roles. Roles hold CRUD permissions per model, entity or attribute, plus a moderator flag for configuration access. The API enforces the permission server-side before every data operation.

Error sanitization

SQL error numbers, stack traces and connection strings are written to the server log but never returned to the browser.

Request flow

Every API call follows the same path:

1. User action — React calls api.js, which issues a fetch() with a JSON body.
2. HTTPS to IIS — the TLS handshake completes and the certificate is validated.
3. ARR proxy — IIS routes /api/* to Express on port 3001.
4. Validation — asyncHandler validates the input and calls the stored procedure.
5. SQL Server — the stored procedure runs inside a transaction and writes the audit entry.
6. Response chain — the result travels back: database, Express, IIS, browser.
7. UI update — React updates state and the component re-renders.

Related

• System Architecture — the same four layers as a diagram
• Database Schema — the tables the stored procedures write to
• Roles & Permissions — how the RBAC matrix is configured
• Error Log — where sanitized server errors are recorded
• Production Deployment (IIS) — configuring TLS and the ARR proxy