Database schema

CommonLayer keeps all state in one PostgreSQL database (version 16 or later; developed against 18): 121 tables and 2 views in 18 migration files (schema version 21) under crates/commonlayer-database/migrations/, embedded into the binaries and applied with clctl migrate, which also grants the least-privilege runtime role its privileges. This page explains the schema by domain and lists the invariants the database enforces by itself, independent of application code.

The column-level definition is the SQL in the migration files; this page does not repeat every column. Where the implementation added tables or columns beyond the original plan, that is noted.

Conventions

• Primary keys are UUIDv7 generated by the application, unless stated.
• Enumerations are text with a CHECK (col IN (…)) constraint, never PostgreSQL enum types, so adding a value is an ordinary migration.
• Money is <name>_minor bigint plus currency char(3); never numeric money, never floating point (ADR-0014).
• Digests are text CHECK (digest ~ '^sha256:[a-f0-9]{64}$').
• Case-insensitive identifiers (email, username, slug) use citext.
• updated_at is maintained by the shared trigger set_updated_at().
• Financial, audit and artifact-history rows are never cascade-deleted (ON DELETE RESTRICT).
• Extensions: citext, pg_trgm, btree_gist, pgcrypto.

Custom SQLSTATEs

Triggers raise stable error codes that the application maps to API error codes (commonlayer_core::Error::from(sqlx::Error)):

Migrations and ownership

Each migration file belongs to the domain crate that owns its tables. Only that crate writes those tables.

Numbers 0017–0019 are unused (0020 and 0021 were numbered to leave room for parallel work).

Foreign keys point only to tables in the same or an earlier file. Up to 0013 owners edited their own file in place (no database existed that had to be migrated); from 0014 on every change is a new file, because development, test and stack databases already exist. A process refuses to start when an embedded migration below the newest applied one is missing (Db::ensure_schema_current).

Privileges. The table owner is the migration role. The runtime role that the processes use gets, per table and derived from the catalogue: DML on ordinary tables, INSERT only on append-only tables, INSERT and UPDATE (dispatched_at) on domain_events, no DELETE on artifact_digests, SELECT on ledger_balances (plus the UPDATE (updated_at) that row locks need; the guard trigger refuses every direct write), read-only on artifact_tag_history and _sqlx_migrations, and never TRUNCATE, TRIGGER, REFERENCES or CREATE (crates/commonlayer-database/src/roles.rs).

Platform foundation

• jobs: the job queue (ADR-0004). Partial unique index on unique_key for pending and running jobs; claim index on (queue, priority DESC, run_at) for pending jobs. Extra columns beyond the plan: unique_until_started (lets the outbox dispatcher be re-queued while it runs), trace_parent, updated_at.
• domain_events: the outbox, with a seq bigserial, routing columns (project, organization, subject user), actor and dispatched_at. Append-only except for setting dispatched_at.
• scheduler_runs: (task, scheduled_for) primary key; an insert that conflicts means another scheduler replica already enqueued that slot.
• platform_settings: runtime-tunable settings with a version.
• idempotency_keys: stored responses for the Idempotency-Key header, scoped by caller scope and principal.

Identity and organizations

• users holds humans and service accounts (kind). CHECKs: humans have an email; a service account has no password, never platform_admin, exactly one owner (organization or project) and a sa_role matching that owner (column added by WP-02).
• sessions, api_tokens, user_tokens (verification, reset, email change) and oauth_states store only SHA-256 hashes of their secrets. The PKCE verifier in oauth_states is sealed with SecretBox.
• user_identities links external logins (github, oidc:<name>), unique per (provider, subject).
• organization_members with roles member, billing_manager, org_admin; a deferred constraint trigger keeps at least one org_admin per organization that is not deleted (CL004).
• billing_profiles belong to exactly one organization or one user. Billing data is never written to the audit log.
• Views effective_project_roles (project members plus active maintainer assignments, security_reviewer assignments as their own role) and effective_grants (platform role, organization roles, project roles and service-account roles, with scope slugs and names) give the authentication middleware all grants of a principal in one query.

Audit

• audit_logs has no foreign keys (rows outlive their subjects), is append-only and indexed by project, organization, actor, resource and action. changes is a redacted top-level diff: keys matching the secret-name denylist show ***.
• audit_checkpoints form a hash chain over batches of audit rows, written by the audit.checkpoint job (rows older than five minutes) and verified by clctl audit verify. This gives tamper evidence for the audit log; it is stored in the same database, so it detects changes by someone who did not also rewrite the checkpoints (see the threat model).

Projects

• projects carries three independent state columns on purpose: lifecycle_state (proposed, in_review, active, underfunded, maintenance_degraded, unmaintained, deprecated, archived, rejected), funding_state (not_configured, fully_funded, partially_funded, underfunded, surplus, paused) and security_state (not_scanned, no_known_issues, issues_within_sla, issues_overdue, stale_scan). A funded project can have overdue vulnerabilities and an unfunded one can be clean. A generated tsvector column with a GIN index and a trigram index on name serve search. Extra cached columns: funding_target_minor, funding_runway_bps, artifact_kinds, manifest_public_keys (WP-10).
• project_categories are seeded with the slugs the manifests use (ADR-0017).
• project_repositories: one primary repository per project (partial unique index); status becomes verified when the repository's commonlayer.yaml names the project.
• project_members holds contributor and project_admin; the maintainer role is not stored there, it derives from active assignments.
• migration_candidates is the Bitnami migration catalog loaded by catalog sync.

Artifacts and registry

• artifact_digests are never deleted; digest, media_type and size_bytes are immutable (trigger, CL003). Rejected submissions keep a digest row in state rejected so the public digest page can explain the failure.
• artifact_tags: a trigger refuses to move, delete or make mutable a tag flagged immutable (<version>-r<revision>); another trigger writes every change to artifact_tag_history (append-only), so history cannot be skipped.
• artifact_submissions and artifact_submission_steps (added by WP-11) record the pipeline; each step has a status, attempts, error and the evidence a resumed run continues from. artifact_verifications store the policy result and the effective policy snapshot.
• verification_policies: the platform default (project_id NULL, seeded) and optional stricter project policies; one active version per scope.
• release_history is append-only.
• registry_events (pull and push notifications from Distribution) roll up into artifact_pull_stats; retention_plans/retention_plan_items and preservation_checks record retention and preservation.

Security

• vulnerabilities keyed by a canonical id (CVE preferred, then GHSA, then the source id), with vulnerability_aliases; records later found to be the same are merged (merged_into).
• vulnerability_occurrences belong to one digest forever: publishing a fixed image never changes the occurrences of an older digest.
• vulnerability_findings are the unit of triage per (project, vulnerability), with episodes for reopening. vulnerability_finding_deadlines (added by WP-12) hold one row per deadline per episode; a trigger refuses to clear a breach or an approaching notice (CL003) (ADR-0029). vulnerability_finding_events is the append-only timeline.
• vulnerability_exceptions: CHECK (decided_by IS DISTINCT FROM requested_by) and a unique index allowing one active exception per finding.
• security_reports and security_report_messages store bodies encrypted with SecretBox (body_enc).
• security_advisories get CLSA-YYYY-NNNN identifiers on publication from security_advisory_sequences (gapless per year among published advisories).
• security_digest_index maps digest strings to ids for the public digest routes, because the artifacts port has no lookup by digest value (WP-12 deviation).

Maintenance

• maintainer_assignments: one active primary and one active secondary per project (partial unique index, I7); an exclusion constraint (btree_gist) forbids two non-rejected assignments for the same (project, slot_key) with overlapping tstzrange(starts_at, ends_at) (I8). No column references a balance: replacing a maintainer touches no financial row.
• maintainer_assignment_events is append-only.
• eligibility_evaluations store inputs and rule_results verbatim so a decision can be re-derived; override_state keeps an administrator's override next to the computed state. CHECKs refuse self-override; appeals cannot be resolved by the appellant; only one open appeal per evaluation.
• Further self-dealing CHECKs: a maintainer cannot approve or decide their own application, waive their own task request, create or lift their own suspension, or review their own contribution.
• contributions here means community work (pull requests, reviews, fixes), not money.

Funding

The funding tables are specified in detail in the ledger specification. In short:

• ledger_accounts, ledger_transactions, ledger_entries and project_treasuries are append-only. ledger_balances is written only by the entry trigger, which enforces currency agreement (CL008) and non-negative internal balances (CL001); a guard refuses any other writer (CL005).
• allocation_slots of an active policy must sum to its monthly target and funding_contribution_items must sum to their contribution (deferred constraint triggers, CL007).
• community_reward_approvals is append-only; a trigger refuses an approver who is the recipient (CL006).
• payout_runs: CHECK (mode = 'dry_run' OR approved_by IS DISTINCT FROM initiated_by).
• payment_webhooks are unique per (provider, event_id).
• Added by WP-14: project_funding_controls (pause state), funding_receipt_sequences (gapless receipt numbers per year), and extra columns on contributions, obligations, payouts and payout runs.

GitHub and notifications

• github_webhook_deliveries are keyed by GitHub's delivery id, so a redelivery is acknowledged and counted but never processed twice.
• webhook_endpoints have exactly one owner (user, organization or project) and store their signing secret sealed with SecretBox.
• notification_deliveries are unique per (event, channel, recipient).
• notification_github_channels (added by WP-15) configure which public events open an issue in a project's repository.

Invariants enforced by the database

These hold for any client that connects with the application's credentials, whatever the application code does. They do not hold against a database superuser or table owner who disables triggers; see the open items in the threat model. crates/commonlayer-database/tests/it/invariants.rs tests them.

Working with the schema

make up          # start PostgreSQL and the other development services
make db-check    # apply every migration to an empty scratch database, twice
make migrate     # apply migrations to the development database (DEV_DATABASE_URL, as its owner)
make db-reset    # drop, recreate and migrate the development database

clctl migrate applies pending migrations with database.migration_url (the owner; falls back to database.url when unset, as in plain development) and grants the role named by database.runtime_role; clctl db check-role then proves that database.url is least privilege. make dev-backend does both with a local runtime role. Production runs them as a separate step (configuration refuses database.migrate_on_start = true in production).