Three-Tier Design & Security
Three-Tier Design
Primentra uses a three-tier architecture that separates concerns cleanly across the browser, server, and database.
Frontend (Browser) React + Vite runs entirely in the browser. It renders the UI, manages local state, and calls the REST API for all data operations. No business logic lives here — the frontend is a display and interaction layer only.
API Server (Express + Node.js) The Express server is a thin pass-through layer that runs on port 3001. It validates incoming requests and calls the appropriate stored procedure. It contains zero raw SQL — all database logic is delegated to stored procedures.
Database (SQL Server) All business logic lives in 80+ stored procedures. The EAV (Entity-Attribute-Value) schema stores dynamic entity attributes without requiring schema migrations. Every write runs inside a transaction managed by the stored procedure.
Security Layers
Primentra applies defence-in-depth across every tier.
Transport All production traffic is TLS 1.2+ via IIS. The API server binds to localhost:3001 only — it is never directly reachable from outside the machine.
Input validation All request bodies are validated at the API layer before the database is ever reached. Invalid requests return HTTP 400 immediately.
SQL injection prevention Every database call uses parameterized stored procedure calls via the mssql driver. The primentra_svc service account has SELECT, INSERT, UPDATE, DELETE, EXECUTE, and REFERENCES on the dbo schema, plus db_ddladmin for Full-Text Search — scoped to the Primentra database only, with no server-level permissions.
RBAC permissions Users belong to Roles. Roles hold granular CRUD permissions per entity (Create / Read / Update / Delete) plus a Moderator flag for configuration access. The API enforces permissions server-side before every data operation.
Error sanitization Internal error details — SQL error codes, stack traces, connection strings — are logged server-side but never returned to the client.
Request Flow
Every API call follows the same path through the system:
- User action — React calls api.js → fetch() with JSON body
- HTTPS → IIS — TLS handshake, certificate validated
- ARR proxy — IIS routes /api/* to Express on port 3001
- Validation — asyncHandler validates input, calls stored procedure
- SQL Server — Stored procedure runs in transaction, writes audit log
- Response chain — Result travels back: DB → Express → IIS → browser
- UI update — React updates state, component re-renders
Error path: if the stored procedure throws, asyncHandler catches it, logs it to errorlog.txt and the ErrorLog table, sanitizes the message, and returns HTTP 500 to the client.