67 lines
3.8 KiB
Markdown
67 lines
3.8 KiB
Markdown
# 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.
|