Skip to content

Data model & RLS

The data layer is PostgreSQL 16 + pgvector with no ORM. Every query is hand-written and parameterised; records are plain structs scanned with pgx.RowToStructByName.

Row-Level Security

RLS is the platform's primary per-user isolation boundary — defence in depth underneath the WHERE user_id = … clauses the repositories already write. Even a buggy query cannot return another user's rows.

Three identities

The context carries Identity{ UserID, Role }. Role is one of exactly three string constants that must match the SQL policy literals:

Role Who sets it Scope
service ServiceRLSContext on the /auth group Pre-login and system flows (eID upsert, refresh identity lookup, bootstrap). Full access
admin Auth middleware, on admin JWTs Full access to all rows
user Auth middleware, on ordinary JWTs Only the caller's own rows

The repository helper withRLS(ctx, fn) wraps each query in a transaction and publishes the identity as session settings, so the policies know the caller.

Tables with RLS

users                     organizations            organization_memberships
gov_applications          gov_application_events   gov_references
gov_notifications         gov_payments             gov_appointments
user_integrations         user_recovery_codes      security_events
superadmin_accounts       sso_tokens               audit_log
oauth_consents            oauth_access_tokens      oauth_auth_codes

The gov_* tables carry a fourth policy — officer — so a caseworker with gov.review sees the applications assigned to them.

The API must not connect as a superuser

RLS does not apply to BYPASSRLS roles or table owners. The app therefore connects with a least-privilege role, and in production a boot guard verifies this and refuses to start otherwise.

Adding a new table

Every new table holding per-user data needs its own policies:

ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

CREATE POLICY my_table_service ON my_table
  USING (current_setting('app.role', true) = 'service');
CREATE POLICY my_table_admin ON my_table
  USING (current_setting('app.role', true) = 'admin');
CREATE POLICY my_table_self ON my_table
  USING (user_id::text = current_setting('app.user_id', true));

Key tables

Table What it holds
users Citizens — keyed by civil_id; role_id, token revocation cutoff
roles · permissions · role_permissions Dynamic RBAC catalogue
superadmin_accounts · superadmin_invites Separate super-admin accounts, invite allow-list
user_recovery_codes Hashed TOTP recovery codes
login_events Sign-in history, lockout accounting
Table What
organizations Organisations, with state-registry data
organization_memberships Membership and roles
org_stamps Organisation stamp images
Table What
gov_services · gov_life_events Catalogue
gov_applications · gov_application_events Applications and their timeline
gov_references Issued certificates
gov_notifications · gov_payments · gov_appointments Notifications, payments, appointments
gov_service_events Service-level events
Table What
registry_services · registry_service_versions Service passport and version history
registry_evidences · registry_service_evidences Evidence catalogue and links
registry_life_events Life events
relay_platforms · relay_routes Downstream agencies and routing
relay_requests · relay_assignments · relay_events Relayed requests, assignments, history
Table What
gateway_services · gateway_request_logs Service catalogue and telemetry
applications · application_services RP registry and service grants
oauth_clients · oauth_consents OIDC clients and consent
oauth_auth_codes · oauth_access_tokens · oauth_challenges Flow state
admin_api_keys · developer_apps Admin keys, developer apps
Table What
ai_prompts Admin-editable scope / instructions prompt layers
ai_knowledge Knowledge-base corpus plus pgvector embeddings (HNSW index)
themes · site_appearance Named themes, site-wide appearance
platform_settings Key-value platform settings
audit_log Hash-chained, append-only log

Migrations

  • File names: N_name.up.sql + N_name.down.sql in platform-core/migrations/.
  • embedded into the Go binary — no SQL files to ship into containers.
  • The compose migrate service runs on every up and skips what is applied.
  • Numbering range: app migrations live outside the foundation's range (migrations/RANGE) or numbers collide.

Editing the knowledge base

The ai_knowledge corpus is in migration 48. Keep the slug stable — embeddings refresh on boot or via POST /api/v1/admin/ai/knowledge/reindex.

Connection management

Setting What
DB_MAX_OPEN_CONNS Pool ceiling
DB_MAX_IDLE_CONNS Idle connections
DB_CONN_MAX_LIFE_MINS Connection lifetime

The pgx pool is traced through otelpgx, so slow queries show up in traces.