Architecture & documentation

Deliverable docs for the platform: architecture diagram, data model, deployment guide and security checklist.

Architecture diagram

  authorised sources                ingestion                    serving
  ------------------                ---------                    -------
  Postgres / MySQL  ->  +---------------------------+  ->  +----------------------+
  CSV / Excel       ->  | detect -> validate ->     |      | generated REST API   |
  JSON / XML        ->  | normalise -> dedupe ->    |      | filter / page / search|
  3rd-party API     ->  | version (immutable log)   |      | OpenAPI docs + SDKs  |
                        +---------------------------+      +----------------------+
                                    |                                 |
                              consent registry              auth: JWT / API keys / roles
                                    |                                 |
                        +---------------------------+      +----------------------+
                        | Postgres (managed cloud)  |      | audit log + metrics  |
                        +---------------------------+      +----------------------+

Data model

Table	Columns
organisation	id, name, plan, created_at
member	id, org_id, user_id, role (owner/admin/editor/viewer)
source	id, org_id, name, kind, status, consent_id, created_by
consent	id, source_id, granted_by, scope, expires_at, revoked_at
schema_version	id, source_id, version, fields jsonb, change_note, created_at
record	id, source_id, schema_version, payload jsonb, checksum, ingested_at
ingest_run	id, source_id, rows_in, rows_written, duplicates, errors, finished_at
api_key	id, org_id, hashed_key, prefix, scopes, last_used_at, revoked_at
audit_log	id, org_id, actor_id, action, target, metadata jsonb, at

Deployment guide

1Provision the managed Postgres backend and run migrations in order; every table ships with row-level security plus explicit role grants.
2Seed the organisation, owner member row and role enum before inviting anyone else.
3Store provider credentials as backend secrets — never in the repo, never in client code.
4Deploy the app; server functions and API routes ship with it, no separate service to operate.
5Point external callers at the stable published URL and register webhook/cron callers against the public API prefix.
6Verify: health check, one authorised ingest run end to end, one authenticated read, one audit-log entry.

Security checklist

Consent-gated ingestion

No source can be read without a stored, unexpired consent record naming the grantor and scope.

Row-level security

Every table denies by default; policies scope reads and writes to the caller's organisation.

Roles in a separate table

Roles live in a members table checked server-side — never on the user profile, never in client storage.

Server-side validation

Every payload is schema-validated on the server, not only in the browser form.

Least-privilege keys

API keys are hashed at rest, prefixed for identification, scoped per endpoint and revocable.

Audit everything

Source changes, schema approvals, key issuance and exports all write immutable audit rows.

No PII in public reads

Public endpoints project explicit safe columns; learner data is never exposed anonymously.

Immutable version history

Schema versions and ingest runs are append-only so any transform can be replayed or rolled back.

Stack notes

This platform runs on TypeScript end to end: TanStack Start for the app and its server functions, managed Postgres for storage, and generated OpenAPI docs for consumers. NestJS/FastAPI, Redis, Elasticsearch, Docker and Kubernetes are not part of this runtime — their roles are covered by server functions, Postgres indexes and full-text search, and the managed deploy pipeline respectively.