Database Schemas¶
Several Trust Stack libraries ship a durable backend. The canonical DDL for the PostgreSQL / SQLite tables is shown below; the libraries also create these tables automatically on first use.
task-dedupe¶
-- Schema for the durable task stores (truststack-task-dedupe).
--
-- Both SqliteTaskStore and PostgresTaskStore are created lazily on first use;
-- the DDL is documented here for reference and for provisioning Postgres ahead
-- of time. The SQLite layout uses a TEXT payload (JSON); the Postgres layout
-- uses JSONB.
-- ---------------------------------------------------------------------------
-- SQLite (SqliteTaskStore) — stdlib sqlite3, payload as JSON text.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tasks (
id TEXT PRIMARY KEY, -- engine-assigned uuid4 hex
fingerprint TEXT NOT NULL, -- 16 hex-char intent fingerprint
payload TEXT NOT NULL -- Task serialized as JSON (Task.model_dump_json)
);
-- Speeds up exact-fingerprint short-circuit lookups.
CREATE INDEX IF NOT EXISTS idx_tasks_fingerprint ON tasks (fingerprint);
-- ---------------------------------------------------------------------------
-- Postgres (PostgresTaskStore) — asyncpg, payload as JSONB.
-- The default table name is "tasks"; PostgresTaskStore(dsn, table=...) lets you
-- override it (the value is validated as a Python identifier).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tasks (
id TEXT PRIMARY KEY, -- engine-assigned uuid4 hex
fingerprint TEXT NOT NULL, -- 16 hex-char intent fingerprint
payload JSONB NOT NULL -- Task serialized as JSON (Task.model_dump_json)
);
-- Speeds up exact-fingerprint short-circuit / has_fingerprint() lookups.
CREATE INDEX IF NOT EXISTS idx_tasks_fingerprint ON tasks (fingerprint);
entity-canon¶
-- Schema for truststack-entity-canon entity stores.
--
-- Both backends create their own table lazily on first use, so you do NOT need
-- to run this by hand. It is reproduced here for reference, for code review, and
-- for operators who prefer to pre-provision tables under migration control
-- (e.g. Alembic / Flyway) rather than rely on lazy CREATE TABLE IF NOT EXISTS.
--
-- * SqliteEntityStore (entity_canon.stores.SqliteEntityStore) -> SQLite
-- * PostgresEntityStore (entity_canon.stores.PostgresEntityStore) -> PostgreSQL
--
-- The model is a single `entities` table: a stable id, a canonical display name,
-- and a JSON/JSONB list of aliases (alternate surface forms used for matching).
-- Writes are upserts keyed on `id`, which is how a "merge" persists an updated
-- alias list onto an existing row instead of inserting a duplicate.
-- ===========================================================================
-- SQLite (entity_canon.stores.SqliteEntityStore)
-- ===========================================================================
-- Aliases are a JSON-encoded list[str] stored in a TEXT column (json.dumps /
-- json.loads at the application boundary). Writes use
-- INSERT OR REPLACE INTO entities (...) for idempotent upserts on `id`.
CREATE TABLE IF NOT EXISTS entities (
id TEXT PRIMARY KEY, -- stable entity identifier (uuid4 hex)
name TEXT NOT NULL, -- canonical display name (non-blank)
aliases TEXT NOT NULL DEFAULT '[]' -- JSON-encoded list[str] of aliases
);
-- ===========================================================================
-- PostgreSQL (entity_canon.stores.PostgresEntityStore, asyncpg)
-- ===========================================================================
-- Aliases are stored as JSONB. The store applies the DDL below on first use.
-- * Writes: INSERT INTO entities (...) ... ON CONFLICT (id) DO UPDATE
-- SET name = EXCLUDED.name, aliases = EXCLUDED.aliases
-- -> idempotent upserts; backs bulk_import merges.
-- * Reads: SELECT id, name, aliases FROM entities (all)
-- SELECT ... FROM entities WHERE id = $1 (get)
-- * Deletes: DELETE FROM entities WHERE id = $1 (delete)
-- -> the asyncpg "DELETE <n>" command tag yields the row count
-- that backs the REST DELETE /entities/{id} 204 vs 404.
CREATE TABLE IF NOT EXISTS entities (
id TEXT PRIMARY KEY, -- stable entity identifier (uuid4 hex)
name TEXT NOT NULL, -- canonical display name (non-blank)
aliases JSONB NOT NULL DEFAULT '[]'::jsonb -- list[str] of aliases
);
-- ---------------------------------------------------------------------------
-- Optional operational indexes (NOT created by the store; add if you need them)
-- ---------------------------------------------------------------------------
-- The PRIMARY KEY on `id` already covers get() and delete(). The two indexes
-- below are useful at scale and are safe, additive enhancements:
--
-- * a case-insensitive index on `name` to accelerate exact-name lookups /
-- reporting queries over the canonical surface form;
-- * a GIN index on the JSONB `aliases` to support containment queries such as
-- "which entity has alias X?" (aliases @> '["X"]'::jsonb).
--
-- CREATE INDEX IF NOT EXISTS entities_name_lower_idx
-- ON entities (lower(name));
--
-- CREATE INDEX IF NOT EXISTS entities_aliases_gin_idx
-- ON entities USING gin (aliases jsonb_path_ops);
shipped-or-not¶
-- Schema for SqliteAuditStore (truststack-shipped-or-not).
--
-- This file is the canonical reference for the durable audit trail. The DDL
-- below is mirrored verbatim by shipped_or_not.audit._SCHEMA and is created
-- lazily by SqliteAuditStore.initialize() / .connect() on first use. Both
-- statements are idempotent (IF NOT EXISTS), so re-running is safe.
--
-- Storage model
-- -------------
-- Each verification is stored as one immutable, append-only row. The full
-- audit report (VerificationResult.to_report(), JSON) lives in `payload`;
-- `url` and `verified_at` are denormalized out of that JSON into their own
-- columns purely to make per-URL, time-ordered history() lookups fast.
--
-- Chronology
-- ----------
-- `id` is a monotonically increasing AUTOINCREMENT key, so ORDER BY id ASC is
-- exactly insertion (= chronological) order. history(url) and all() both rely
-- on this rather than parsing `verified_at`.
--
-- Round-tripping
-- --------------
-- history()/all() rehydrate each row by validating `payload` back into a
-- VerificationResult (Pydantic coerces ISO-8601 strings to datetimes), so a
-- persisted result is recovered losslessly.
CREATE TABLE IF NOT EXISTS verifications (
id INTEGER PRIMARY KEY AUTOINCREMENT, -- monotonic insertion order (chronological)
url TEXT NOT NULL, -- the verified URL (denormalized from payload)
verified_at TEXT NOT NULL, -- ISO-8601 UTC timestamp (denormalized from payload)
payload TEXT NOT NULL -- VerificationResult.to_report() as a JSON document
);
-- Speeds up per-URL history() lookups; results are returned ORDER BY id ASC
-- (chronological) after filtering on url.
CREATE INDEX IF NOT EXISTS idx_verifications_url ON verifications (url);
-- Reference queries (informational; the store issues parameterized equivalents)
-- ---------------------------------------------------------------------------
-- Record a verdict:
-- INSERT INTO verifications (url, verified_at, payload) VALUES (?, ?, ?);
-- Replay one URL's history (oldest first):
-- SELECT payload FROM verifications WHERE url = ? ORDER BY id ASC;
-- Replay everything (oldest first):
-- SELECT payload FROM verifications ORDER BY id ASC;
meta-token-vault¶
-- =====================================================================
-- Schemas for meta_token_vault durable backends.
--
-- Token VALUES are always stored encrypted via the configured Encryptor
-- (FernetEncryptor in production; NoopEncryptor is DEV ONLY and leaves the
-- value in cleartext). All other columns are stored in cleartext.
--
-- Token shape (see meta_token_vault.models.Token):
-- id uuid4 hex, primary key
-- value secret material, ENCRYPTED at rest
-- app_id Meta app id
-- scopes granted scopes
-- issued_at issue time (UTC)
-- expires_at expiry (UTC), NULL = never expires
--
-- "Active" token selection is performed in Python, not SQL: of all tokens
-- for an app whose expires_at is NULL or in the future, the one with the
-- greatest issued_at is returned.
-- =====================================================================
-- =====================================================================
-- SqliteTokenStore (stdlib sqlite3). Created on construction.
-- Datetimes are ISO-8601 strings (UTC); scopes are comma-separated.
-- =====================================================================
CREATE TABLE IF NOT EXISTS tokens (
id TEXT PRIMARY KEY, -- Token.id (uuid4 hex)
app_id TEXT NOT NULL, -- Meta app id
value TEXT NOT NULL, -- encrypted token secret (Encryptor.encrypt output)
scopes TEXT NOT NULL, -- comma-separated scope list ("" when empty)
issued_at TEXT NOT NULL, -- ISO-8601 UTC issue time
expires_at TEXT -- ISO-8601 UTC expiry, NULL = never expires
);
-- Speeds up get_active/all lookups that filter by app_id.
CREATE INDEX IF NOT EXISTS idx_tokens_app_id ON tokens (app_id);
-- Upsert performed by put():
-- INSERT INTO tokens (id, app_id, value, scopes, issued_at, expires_at)
-- VALUES (?, ?, ?, ?, ?, ?)
-- ON CONFLICT(id) DO UPDATE SET
-- app_id=excluded.app_id, value=excluded.value, scopes=excluded.scopes,
-- issued_at=excluded.issued_at, expires_at=excluded.expires_at;
-- =====================================================================
-- PostgresTokenStore (asyncpg). Created automatically on first use; shown
-- here for reference / manual provisioning. Default table name is
-- meta_token_vault_tokens (configurable via the `table` argument; validated
-- as a Python identifier). Datetimes are TIMESTAMPTZ; scopes a text array.
-- =====================================================================
CREATE TABLE IF NOT EXISTS meta_token_vault_tokens (
id TEXT PRIMARY KEY, -- Token.id (uuid4 hex)
app_id TEXT NOT NULL, -- Meta app id
value TEXT NOT NULL, -- encrypted token secret (Encryptor.encrypt output)
scopes TEXT[] NOT NULL DEFAULT '{}', -- granted scopes
issued_at TIMESTAMPTZ NOT NULL, -- issue time (UTC)
expires_at TIMESTAMPTZ -- expiry, NULL = never expires
);
-- Speeds up get_active/all lookups that filter by app_id.
CREATE INDEX IF NOT EXISTS idx_meta_token_vault_tokens_app_id
ON meta_token_vault_tokens (app_id);
-- Upsert performed by put():
-- INSERT INTO meta_token_vault_tokens (id, app_id, value, scopes, issued_at, expires_at)
-- VALUES ($1, $2, $3, $4, $5, $6)
-- ON CONFLICT (id) DO UPDATE SET
-- app_id=excluded.app_id, value=excluded.value, scopes=excluded.scopes,
-- issued_at=excluded.issued_at, expires_at=excluded.expires_at;
-- =====================================================================
-- Key-value / secret-manager backends (no SQL schema).
--
-- The following backends are schemaless from a SQL perspective; they store
-- each token as one encrypted, JSON-serialised document. Layout reference:
--
-- AwsSecretsManagerTokenStore
-- Secret name : {prefix}/{app_id}/{token_id} (prefix default "meta-token-vault")
-- Value : JSON {id, app_id, value(encrypted), scopes, issued_at, expires_at}
-- Tags : meta_token_vault:app_id = <app_id>
-- meta_token_vault:managed = "true"
--
-- AzureKeyVaultTokenStore
-- Secret name : {prefix}-{app_id}-{token_id} (prefix default "mtv")
-- Value : same JSON document as above
-- Tags : meta_token_vault_app_id = <app_id>
-- meta_token_vault_managed = "true"
--
-- HashiCorpVaultTokenStore (KV v2)
-- Path : {mount_point}/{path_prefix}/{app_id}/{token_id}
-- (mount_point default "secret", path_prefix default "meta-token-vault")
-- Data : { "token": <same JSON document as above> }
--
-- In every case the JSON "value" field is the Encryptor.encrypt output, never
-- the plaintext token. issued_at / expires_at are ISO-8601 UTC strings.
-- =====================================================================