Signet — Database Design (v0.1)

PostgreSQL data model behind the registry: entity relationships, table specifications, indexing strategy, hot-path queries, and migration policy. The DDL source of truth is [api/migrations/](../../api/migrations/). Related: architecture.md §3–§5, attestation-schema.md

1. Modeling Principle

**records is the only source of truth.** It stores signed bundles verbatim (DSSE envelope + transparency-log proof, as JSONB). Every other table is a *derived index*: rebuildable from records by re-running extraction. This is load-bearing for NFR-3 — a mirror ingesting the daily dump reconstructs an identical database, and a schema bug in a derived table is fixed by re-extraction, never by data repair.

Consequences:

• Derived tables have no independent lifecycle: ON DELETE CASCADE from

records, and no UPDATEs except full re-extraction.

• No foreign key from record_refs.target_record_id to records.id — a

verification may arrive at a mirror before the attestation it references (dangling refs resolve at extraction time, not insert time).

• coverage is a materialized *read model*, recomputed by the trust engine;

it may lag records by seconds and that's by design.

2. Entity Relationships

%%{init: {'theme': 'base', 'themeVariables': { 'fontSize': '14px' }}}%%
erDiagram
    identities ||--o{ records : signs
    identities ||--o{ identities : operates
    identities ||--o{ reputation_events : accrues
    identities ||--|| scores : has
    records ||--o{ subjects : "binds to"
    records ||--o{ scope_files : covers
    records ||--o{ claims : asserts
    records ||--o{ record_refs : references
    records }o--o{ coverage : "feeds (via trust engine)"

3. Table Specifications

3.1 identities

One row per proven identity (key fingerprint or Fulcio SAN). operator_id self-references for agent identities (trust model §3); an agent's row must point at a human/org row, enforced at the application layer (the DB can't know which is which — tier semantics live in code).

3.2 records

JSONB is storage, not query surface: **no GIN index on bundle**. Anything queried gets extracted to a typed column at ingest. This keeps write amplification predictable and the hot read path off JSONB entirely.

3.3 Derived indexes

• **subjects** — one row per statement subject. The composite index

(repo_url, commit_sha) INCLUDE (record_id) makes record-lookup-by-commit index-only. purl is partial-indexed (WHERE purl IS NOT NULL).

• **scope_files** — one row per (record, file[, line-range]). Indexed by

file_sha256 for "has this exact content ever been audited?" queries — content-hash lookup works across repos (vendored copies, forks).

• **claims** — one row per (record, status, CWE). Feeds per-CWE coverage.
• **record_refs** — the social graph edges (verification/flag → target).

Indexed by target for "all verifications of attestation X".

3.4 Trust engine tables

• **reputation_events** — append-only ledger of scoring events (trust model

§4.1 event table). cause_ref links to the record or advisory that caused it: every score is explainable. Never updated, never deleted; decay is applied at read/compute time via occurred_at, not by mutating rows.

• **scores** — current computed E, G, R per identity plus

params_version (the signed trust-parameter config used). Snapshot table: overwritten each engine cycle; history is reconstructible from events.

3.5 coverage (read model)

PK (repo_url, commit_sha, path) — the coverage query is a PK-prefix scan returning all paths for a commit. vuln_classes text[] holds covered CWEs (read filtering happens API-side; the array avoids a per-CWE row explosion). disputed is denormalized from open flags so the read path never joins.

3.6 AI verifications (ai_verifications, ai_verification_findings)

Automated AI security assessments of catalog packages and their history (V15). Distinct from the signed peer-verification record — an AI verification is *not* an attestation and carries no reputation weight; it is a pre-screen the analysis fleet (e.g. a local LLM) produces to triage packages until a human or agent audit lands. Not part of the record read model, so it never affects coverage or trust scores.

• **ai_verifications — append-only; one row per run**, so the table *is*

the history. (ecosystem, name, version) is a soft reference to package_releases (no FK, matching package_insights); the newest row per package is its current assessment. Carries model, status (queued→running→completed/failed), verdict (clean/findings/inconclusive), risk_level, summary, raw findings jsonb, confidence, token counts, and created_at/completed_at. Indexed (ecosystem, name, version, created_at DESC) for the timeline + latest lookup. A methodology jsonb (V16) records how the run was produced — sources (repo, commit, sampled files), parts (files/bytes analyzed, truncation, budget), techniques (the vulnerability-check categories applied), and parameters (model, temperature, token budget) — so an assessment is reproducible.

• **ai_verification_findings** — normalized findings per run (path, line range,

vuln_class, severity, title, detail), mirroring how scope_files/claims derive from records. Read API: GET /v1/ai-verifications?ecosystem&name&version.

4. Hot-Path Queries

Coverage by commit (the <100 ms path — index-only, no joins):

SELECT path, file_sha256, confidence, vuln_classes, status, disputed
FROM coverage
WHERE repo_url = $1 AND commit_sha = $2;

Bulk SBOM check (one round trip for up to 5,000 purls):

SELECT s.purl, c.path, c.confidence, c.status, c.disputed
FROM subjects s
JOIN coverage c ON c.repo_url = s.repo_url AND c.commit_sha = s.commit_sha
WHERE s.purl = ANY($1::text[]);

Content-hash lookup (cross-repo: "was this exact file audited anywhere?"):

SELECT r.id, sf.path, sf.line_start, sf.line_end
FROM scope_files sf JOIN records r ON r.id = sf.record_id
WHERE sf.file_sha256 = $1 AND r.record_type = 'audit';

Verifications + flags for one attestation (record detail page):

SELECT r.id, r.record_type, r.signer_id, r.created_at
FROM record_refs rr JOIN records r ON r.id = rr.record_id
WHERE rr.target_record_id = $1;

5. Sizing & Scaling Posture

Deliberately deferred: read replicas, table partitioning, and any cache tier — all mechanical to add later because reads are already single-index-scan shaped.

Measured envelope (50k synthetic packages / 20k AI runs / 10k coverage rows, 2026-08): point lookups (dossier, coverage, per-package AI history) hold at ~16ms p50 — single-index-scan shaped as designed. Catalog page 1 is ~30ms with package_releases_discovered_idx (V33; 158ms without — the ORDER BY needs the matching composite). Whole-table aggregates recomputed per request are the first thing to buckle at the next order of magnitude: /v1/stats ~110ms and /v1/ai-verifications/stats ~172ms at this scale, and deep OFFSET pagination (page=1000) ~400ms. Revisit with a short-TTL cache (stats) and keyset pagination (catalog) when those cross ~500ms in production measurement — both are the mechanical additions deferred above, now with concrete trigger numbers.

6. Migration & Query-Code Policy

• Migrations: Flyway files in server/src/main/resources/db/migration/

(V1__init.sql, …). Forward-only in production. A migration that rewrites a derived table ships as *drop + re-extract from records*, not as data surgery.

• Query code: hand-written SQL via Spring JdbcClient with named

parameters — queries stay reviewable SQL, never string concatenation; schema/code drift is caught by Testcontainers-backed repository tests (backend.md §8).

• Determinism rule: any column an engine computation reads must be

reproducible from (records, params config, seed set) — nothing the trust engine consumes may originate outside the dump. This is checked in review, not enforceable mechanically; it's the invariant that keeps mirrors honest.