Schema Conventions

Every table follows these conventions:

Convention	Rule	Example
Primary key	id, UUID v4 as TEXT (SQLite) or UUID (PostgreSQL)	id TEXT PRIMARY KEY
Timestamps	created_at, updated_at — UTC, never null	created_at TEXT NOT NULL
Soft delete	deleted_at — nullable, for audit-sensitive records	deleted_at TEXT
Foreign keys	{entity}_id	realm_id, user_id, engine_id
Indexes	idx_{table}_{columns}	idx_secrets_realm_key
Unique constraints	uq_{table}_{columns}	uq_realms_user_slug
Check constraints	chk_{table}_{rule}	chk_leases_ttl_positive
Booleans	is_ prefix	is_active, is_revoked
Enums	TEXT with CHECK constraint (SQLite) or custom type (PG)	status TEXT CHECK(status IN (...))

Soft Delete vs Hard Delete

• Soft delete (set deleted_at): realms, secrets, engines, policies, leases, users
• Hard delete (remove row): sessions, nonces, expired tokens, migration tracking
• All queries on soft-deletable tables MUST include WHERE deleted_at IS NULL unless explicitly querying deleted records.

Timestamp Format

• SQLite: ISO 8601 string 2026-04-01T12:00:00Z (always UTC)
• PostgreSQL: TIMESTAMPTZ (always UTC)
• Application code: time.Now().UTC() — never local time