# Database Architecture

All operational state lives in PostgreSQL through Supabase. Each organisation has its own dedicated database instance with no shared infrastructure between tenants.

## Supabase Stack

The data layer consists of several coordinated services running within the Docker Compose stack.

{% @mermaid/diagram content="graph TD
subgraph "Application"
API\["sfp Server"]
Workers\["Hatchet Workers"]
end

```
subgraph "Supabase"
    Kong["Kong Gateway"]
    GoTrue["GoTrue Auth"]
    PostgREST["PostgREST"]
    Realtime["Realtime Engine"]
    PG[("PostgreSQL<br/>RLS · pgcrypto · Vault")]
end

API --> Kong
Kong --> GoTrue
Kong --> PostgREST
API --> Realtime
Workers --> Kong
PostgREST --> PG
GoTrue --> PG
Realtime --> PG" %}
```

**PostgreSQL** stores all application data with row-level security (RLS) policies enforcing access boundaries at the database level. **GoTrue** handles authentication including OAuth and SAML SSO. **PostgREST** exposes a REST interface over database tables. **Kong** acts as the internal API gateway. The **Realtime Engine** tracks table changes and pushes updates to subscribed WebSocket clients.

The API server communicates through the Supabase JavaScript client, which routes queries through Kong to PostgREST. All queries pass through RLS policies, providing an authorisation layer that operates independently of application code.

## Schema

The database schema covers several domains.

**Identity and access** uses Supabase's `auth` schema for user accounts, authentication identities (email, OAuth, SSO), and sessions. The `public` schema extends this with `accounts` and `accounts_memberships` tables that model teams and role-based access.

**Project configuration** stores registered projects, platform associations (GitHub or Azure DevOps), environment definitions, and operational settings.

**Operational state** covers deployment records, build metadata, release definitions, environment allocations, and task execution history. JSONB columns store flexible data that varies by operation type, with GIN indexes for querying.

**Integration credentials** are stored encrypted in a dedicated table. Integration metadata (provider type, scope, status) is stored in plaintext for querying; actual credentials (private keys, tokens) are encrypted with pgcrypto using a key from Supabase Vault.

**Document storage** provides collection-based persistence for build manifests, changelogs, and configuration snapshots. High-volume collections use dedicated tables with optimised indexing while maintaining the same API interface.

## Real-time Updates

When a Hatchet worker writes a state change to the database — deployment progress, test results, environment status — the Supabase Realtime engine detects the change and pushes it through WebSocket channels to subscribed clients.

{% @mermaid/diagram content="sequenceDiagram
participant UI as Client
participant RT as Realtime Engine
participant PG as PostgreSQL
participant Worker as Hatchet Worker

```
UI->>RT: Subscribe to task
Worker->>PG: Write progress
PG-->>RT: Change detected
RT-->>UI: WebSocket push" %}
```

Subscriptions support filtering by resource type and identifier, so clients receive only updates for the resources they are watching.

## Migrations

Schema changes are managed through versioned SQL migration files applied by an init container (`supabase-migrations`) that runs before the application server starts. Migrations are idempotent where possible. During `sfp server update`, the migration lifecycle is handled automatically — services are stopped, migrations run, and services restart.

## Vault

The encryption key for integration credentials is stored in Supabase Vault, a built-in secret management facility. The key is generated automatically during server initialisation via a `SECURITY DEFINER` function accessible only to `service_role`. It persists in the database across restarts and is never exposed in environment variables or configuration files.