27 — Database reference model

Purpose

database/reference-schema.sql is the canonical relational reference for Milestone 0. The selected migration/ORM layer may express it differently, but table purpose, ownership, immutability, uniqueness and deletion behavior must remain equivalent.

Conventions

PostgreSQL.
Opaque UUID identifiers.
UTC timestamptz values.
jsonb only for versioned canonical documents or evidence whose shape is governed by an external schema.
Frequently queried ownership, status and timestamp values remain typed columns.
Workspace-owned tables include workspace_id directly or inherit it through a mandatory parent with authorization-safe queries.
Soft deletion is used only where history or external references require it; otherwise explicit deletion with audit is preferred.

Identity records

The model defines users, revocable sessions, invitations, password-reset tokens, workspaces and memberships. Token values are stored as hashes. Personal workspaces are ordinary workspaces with one owner membership.

Playbook records

playbooks contains stable identity. playbook_versions contains immutable published packages or mutable draft versions. Published rows have a content digest and cannot be updated in place. Favorites and collections reference stable playbook identity; generated runs reference exact versions.

Private publication evidence remains explicit: append-only playbook_review_attestations bind a human review, validation state, lint count, limitations acknowledgement and safety-regression state to one exact draft digest. Static evaluation cases and immutable results record target, fixture and environment digests separately; legacy rows without those bindings are retained but cannot satisfy lifecycle promotion.

Repository records

A repository may be manual or associated with a forge integration. Profile revisions and completed snapshots are immutable. Findings belong to an exact snapshot and may be dismissed or resolved without changing the evidence.

Composition and generated tasks

Composition drafts are mutable. Final generation stores all resolved input and policy snapshots plus the exact rendered bytes and digest. The database term remains generated_run for compatibility with the product specification; the UI should call it a Generated task until direct execution exists.

Integrations and secrets

Integration metadata and encrypted secret envelopes are separate. An integration secret can be rotated without rewriting historical snapshots. The plaintext token never appears in the database.

Jobs and operations

Jobs contain lease, attempt, progress and redacted error state. Audit events are append-only. Support bundles are artifacts and must never include secret envelopes.

Immutability

The application layer is authoritative, but database protections should prevent accidental updates to:

published playbook versions;
repository profile revisions;
completed repository snapshots;
generated runs;
completed evaluation results;
audit events.

A trigger or restricted repository API may enforce this. Tests must prove direct application updates are rejected.

Deletion

Deleting a user disables login and applies configured personal-data deletion behavior.
Deleting a repository never changes the frozen profile snapshot inside a generated run.
Deleting generated artifacts may retain immutable prompt text and metadata.
Deleting an integration deletes encrypted credentials and prevents refresh but may retain normalized historical evidence.
Built-in published playbook versions are not deleted by ordinary users.

Migration requirements

The first migration creates all MVP tables and indexes in dependency-safe order. Later destructive changes use expand/migrate/contract. Every migration test starts from an empty database and upgrades from the latest released fixture.

3.8 KiB Raw Permalink Blame History