Database architecture

How project databases are isolated

Database architecture explains how PostgreSQL control-plane state and per-project SQLite application state are separated.

The separation determines backup scope, scaling, query authorization, and which service may access each file.

Use it before schema design, deployment topology changes, or incident investigation.

Requirements for Database architecture

• Prerequisite — Basic familiarity with PostgreSQL and SQLite.
• Prerequisite — A project whose application data and platform metadata can be classified.
• Required value — Project identity, node/route generation, database root, backup root, metrics root, and worker executable boundary.
• Required value — Ownership of control-plane, project-data, and organization-metrics backups.

Control plane and data plane

PostgreSQL coordinates tenant and service state. Each FFDB project owns an application SQLite file. Database workers open those files through trusted paths and apply request-specific limits and immutable auth context.

Defense in depth

• Server-side SQL parsing and statement classification
• SQLite preparation and resource limits
• Authorizer denial of protected internal objects
• Generated RLS views and INSTEAD OF triggers
• Stable error normalization without raw SQLite internals

Trace one request

A client request never opens a database file directly. The API authenticates the credential and project, dispatches to the bounded worker for that project database, and returns an ordered public result or stable error envelope.

request:
  route: POST /v1/projects/{project_id}/query
  credential: end-user session or project developer key
control_plane:
  store: PostgreSQL
  resolves: organization, project, credential, node placement
data_plane:
  file: /var/lib/ffdb/projects/{database_id}.sqlite3
  process: bounded ffdb-database-worker
  enforcement: parser + authorizer + limits + RLS
response:
  success: ordered columns and lossless tagged values
  failure: stable error code and X-Request-Id

Database architecture workflow

• 1. Classify each datum as platform metadata, project application data, object bytes, or maintenance state.
• 2. Trace an HTTPS request through API verification and the isolated worker.
• 3. Confirm no application component receives a raw database path or PostgreSQL credential.
• 4. Assign backup and restore procedures to both data planes.

Verify database architecture

Every datum has one authoritative store and every query crosses the documented verification and worker boundaries.

Troubleshoot database architecture

• Application code opens a project SQLite file directly — remove that path and use the API.
• Project rows appear in PostgreSQL — revisit the data classification.

Continue from Database architecture

• Design parameterized queries.
• Define migrations and row-level policies.