Documentation
¶
Overview ¶
Package dbschema: global ThreatModel alias sequence (#452).
The ThreatModel alias is a globally-unique, monotonically-increasing integer. It was originally allocated with a single-row SELECT ... FOR UPDATE against the alias_counters '__global__'/'threat_model' row, taken on every threat-model create. Once #451 made every write transaction SERIALIZABLE that one hot row became a serialization bottleneck: on Oracle (snapshot serializable) a waiter that sees the counter row committed-modified since its snapshot raises ORA-08177, converting lock contention into abort-and-retry churn on a single row.
A database sequence removes the contention entirely. NEXTVAL is non-transactional — it does not participate in the caller's snapshot, so it can never raise ORA-08177 / 40001, and concurrent creates each get a distinct value without serializing. The trade-off is gaps on rollback, which the alias contract already tolerated (a rolled-back create previously "released" its number; now it simply leaves a gap).
Package dbschema: append-only triggers for audit_entries and version_snapshots.
T19 (#356): even with the application-level "audit-emit on every mutation" instrumentation, two failure modes remain:
- a code path that mutates a row but forgets to call the audit-emit helper produces a silent change.
- a code path with PATCH access to the audit_entries table itself, an admin running a raw SQL DELETE, or a hostile migration can erase or alter history.
The fix is a DB-level trigger on audit_entries and version_snapshots that raises an exception on any UPDATE, and on DELETE of rows younger than a per-table age floor derived from retention config (#453). The trigger is the last-line defense — if it fires, something at the application or operator layer is trying to mutate immutable history, and the right behavior is to refuse the operation.
The triggers are installed idempotently via CREATE OR REPLACE; the two dialects (PostgreSQL and Oracle ADB) need slightly different syntax. SQLite (used by some narrow unit tests) does not support BEFORE-statement triggers in the same way; we skip on that dialect — single-process SQLite is single-writer and the at-rest tampering scenarios this guards against don't apply to an in-memory test DB.
Package dbschema: owner-aware catalog probes for the startup schema checks (#736).
The pre-migration invariant checks (CheckDuplicateUserProviderIdentities, #724; EnsureSparseUserEmailIndex, #720) each gate on "does the users table exist?" before doing any work. That gate used gorm.Migrator().HasTable, which on Oracle resolves through USER_TABLES -- a view scoped to objects *owned by the connected user*. On a deployment where the runtime user does not own the schema (the DDL-less-runtime-user model cmd/server/main.go already contemplates), USER_TABLES and USER_INDEXES are empty for that session even though USERS exists under another owner, so both checks returned nil having checked nothing, silently, with no log line.
Everything here probes ALL_TABLES / ALL_INDEXES filtered to SYS_CONTEXT('USERENV','CURRENT_SCHEMA') instead: the schema an unqualified identifier actually resolves against on this session, which is exactly the schema TMI's own unquoted DDL reads and writes. That covers both the owner-is-the-connected-user case (CURRENT_SCHEMA defaults to the connected user) and the separated case (a logon trigger or ALTER SESSION SET CURRENT_SCHEMA points the runtime user at the owning schema). When the table still isn't found, the caller logs rather than returning silently.
Package dbschema: removal of system_settings rows for setting keys the registry no longer declares (#813).
rate_limit.requests_per_minute and rate_limit.requests_per_hour were seeded into system_settings on every fresh database but read by no handler -- actual rate limiting runs off server.disable_rate_limiting and server.ratelimit_public_rpm. #812 classified them, which cleared the live GET/DELETE 404 that #809 reported, and #813 removes them outright.
Removing the declarations alone is not sufficient. Existing deployments (RDS and k3s both) already hold the seeded rows, and a row whose key has no registry entry resolves to VisibilityInternal -- so LIST would keep showing it while GET and DELETE 404, which is exactly the inconsistency #809 was about. Deleting the declaration without deleting the row would therefore re-create the bug on every existing install while fixing it only for new ones.
Deletion is unconditional rather than restricted to seeded-origin rows. The keys are leaving the product: an operator-set value for a setting nothing reads has no effect to preserve, and leaving explicit-origin rows behind would reproduce the orphan case above for precisely the installs most likely to notice. The keys and row count are logged; values never are.
Ordering matters. This must run BEFORE BackfillSystemSettingOrigin. expectedSeedValues (system_setting_origin_backfill.go) derives from models.DefaultSystemSettings(), which projects the registry -- so once these defs are gone, a surviving NULL-origin rate_limit row no longer matches any known default and the backfill would stamp it explicit moments before this function deletes it. Harmless, but it inflates the backfill's reported row count and makes upgrade logs misleading.
Idempotent and cheap in steady state: after the first successful run the DELETE matches nothing. Safe on every boot, mirroring BackfillSystemSettingOrigin and DeduplicateGroups.
DML, not DDL -- so the Oracle PrepareStmt hazard that makes a repeated byte-identical DDL string silently no-op through gorm.DB.Exec does not apply here.
Package dbschema: pre-migration dedupe of groups(provider, group_name) (#704).
ensureGroupExists (api/database_store_gorm.go) and UpsertGroup (api/group_repository.go) both upsert a group keyed on (provider, group_name) via an ON CONFLICT / MERGE clause, but until #704 that pair was backed only by two separate non-unique indexes (idx_groups_provider, idx_groups_group_name) — the conflict target itself was unbacked. On Oracle, MERGE INTO does not require a unique index on its match condition, so two concurrent first-time upserts for the same (provider, group_name) could each fall through to their INSERT branch and silently create duplicate group rows. api/models/models.go now backs the pair with uniq_groups_provider_group_name, but a database that already carries such duplicates would fail AutoMigrate's CREATE UNIQUE INDEX (ORA-01452 / PostgreSQL 23505). DeduplicateGroups runs ahead of AutoMigrate to clear any existing duplicates first.
Package dbschema: retirement of the four dead metadata timestamp indexes (#784) and the INITRANS raise on METADATA and its surviving indexes (#783).
#783 measured a false ORA-08177 on back-to-back BulkReplace calls against the same entity with no concurrent writer: GORM emits a bare CREATE TABLE, so metadata blocks carry Oracle's default INITRANS 1 (2 for index blocks), and the second transaction recycles the first one's ITL slot on the very blocks it just wrote. Ten delete-then-insert metadata callers still run inside SERIALIZABLE parent transactions, so INITRANS 16 is defense in depth. The aggravator was four timestamp-leading indexes whose right-edge leaf block every same-batch row landed on; nothing reads metadata by created_at or modified_at, so they are dropped rather than rebuilt.
AutoMigrate never drops an index and never sets physical attributes, so both are raw DDL here, on the EnsureUserProviderLookupUnique template.
Package dbschema: pinned-session DDL execution for startup migrations (#734).
CREATE/DROP INDEX takes an exclusive DDL lock on the target table. USERS is hot (login updates, sparse inserts), and Oracle's DDL_LOCK_TIMEOUT defaults to 0 -- "fail immediately" -- so a single concurrent DML holder makes the statement raise ORA-00054 (resource busy) rather than wait. During a rolling deploy the old pods are still issuing UPDATE users SET last_login..., which made the retry budget alone (3 attempts x 20ms, all inside the same DML window) effectively one shot: startup aborted and the pod crash-looped until the window happened to clear.
DDL_LOCK_TIMEOUT is a session setting, and database/sql gives no guarantee that two calls through the pool land on the same Oracle session -- so the ALTER SESSION and the DDL it configures must run on one pinned *sql.Conn, the same reason and pattern as the advisory lock in advisory_lock.go.
Package dbschema: PostgreSQL default-isolation defense-in-depth (#450).
#448/#449 thread &sql.TxOptions{Isolation: LevelSerializable} through the retry wrapper so every write transaction runs SERIALIZABLE. That is the primary, cross-database, leak-proof mechanism. This installer adds a belt-and-suspenders backstop on PostgreSQL only: it pins the connecting role's default_transaction_isolation to 'serializable', so any connection that somehow bypasses the wrapper still starts its transactions at the safe level rather than READ COMMITTED.
Scope and limits:
- PostgreSQL only. Oracle ADB has no ALTER DATABASE/ALTER ROLE default-isolation knob and blocks the logon-trigger workaround; enforcement there is per-transaction via the wrapper's TxOptions, which this package cannot and does not change. The Oracle side of #450 is verification, not configuration.
- ALTER ROLE ... SET takes effect for sessions opened AFTER it runs. Connections already in the pool keep their session default until they are recycled (or the server restarts). Because this is only a backstop behind the per-transaction wrapper, that lag is acceptable.
Package dbschema: schema-version fingerprint (#480).
GORM AutoMigrate is introspection-heavy: for every model it issues a series of per-object existence checks (USER_TABLES / USER_INDEXES / USER_CONSTRAINTS on Oracle, the information_schema equivalents on PostgreSQL) before deciding what, if anything, to create. For the full TMI schema that is hundreds of sequential statements. Against a local database each is sub-millisecond and the cost is invisible; against a remote database (Oracle ADB) every one pays a full network round-trip (~300-900 ms observed), so a single AutoMigrate pass takes many minutes — long enough that the in-cluster server pod was killed by its liveness probe mid-migration and crash-looped (#479), and long enough to stretch every production pod start, rollout, and scale-up (#480).
The fix is to skip the introspection pass when the schema is already current. On a successful migration we record a fingerprint of the model set in a tiny single-row table (tmi_schema_versions). On the next boot we compute the same fingerprint and, if it matches what is recorded, we skip AutoMigrate entirely — turning hundreds of round-trips into two (ensure the stamp table, read one row). The fingerprint changes iff a model's migratable definition changes, so a mismatch (or a missing stamp) conservatively falls back to a full AutoMigrate, which remains additive and idempotent (#474).
Package dbschema: unique sparse-email index for NULL provider_user_id rows (#720).
idx_users_provider_lookup (provider, provider_user_id, #701) does not constrain sparse rows on either engine, so two concurrent email-only sparse inserts (performSparseUserInsert, api/authorization_enrichment.go) can both succeed and leave duplicate USERS rows for the same (provider, email). EnsureSparseUserEmailIndex closes that gap with a unique index restricted to sparse rows (provider_user_id IS NULL) only. This cannot be a GORM struct tag: the predicate/expression differs per dialect and AutoMigrate cannot express either portably, so it is raw DDL run from dbschema at the same three startup entry points as #704/#724.
The two engines reach "does not constrain sparse rows" differently, and an earlier version of this comment got it wrong in a way that cost a production outage (#760). PostgreSQL treats NULLs as distinct, so a partially-NULL key is never a duplicate. Oracle does NOT: it omits a b-tree entry only when EVERY key column is NULL, so ('tmi', NULL) twice is a duplicate there. idx_users_provider_lookup is therefore function-based on Oracle (see userProviderLookupDDL) specifically so sparse rows key as all-NULL and fall out of the index. Do not "simplify" it back to a plain composite unique index.
Package dbschema: one-time backfill of system_settings.origin for rows that predate the column, plus the CHECK constraint that enforces its invariant going forward (#794).
SystemSetting.Origin distinguishes a row SeedDefaults inserted from one an operator deliberately set (api/models/system_setting.go). NULL reads as SEEDED -- the fail-safe direction for data loss -- but that same polarity is wrong for a row that already existed before this column shipped: on every live deployment (RDS, k3s) a pre-existing row was, under the old rules, either the only value the operator could see or one they had genuinely edited via the admin API. Leaving it NULL after upgrade would silently hand its authority to config, which is exactly the class of regression #794 introduced this column to prevent (oracle-db-admin review: the change is otherwise inert on the deployments that had the 2026-08-20 outage, because SeedDefaults skips rows that already exist and nothing else ever stamps a pre-existing row).
BackfillSystemSettingOrigin stamps a NULL-origin row explicit only when it shows operator intent: modified_by is set (an admin API write, or a dbtool import, from before Origin existed), or its value no longer matches what SeedDefaults would seed for that key today. Every other NULL-origin row -- one that still holds the untouched registry default -- is left alone, so it correctly reads as seeded going forward. This is deliberately not a blanket `UPDATE ... SET origin = 'seeded' WHERE origin IS NULL`: that would demote genuinely admin-set rows the moment their value happens to equal the current default.
The value-comparison signal cannot see through at-rest encryption (see expectedSeedValues): an encrypted row is left on the modified_by signal alone and otherwise stays NULL. That is a deliberate choice, not an oversight -- oracle-db-admin review, #794. Defaulting an encrypted-and-unmatched row to explicit would have reproduced the 2026-08-20 outage class on every encrypted deployment (a seeded, encrypted auth.oauth_callback_url row would keep outranking a correctly-set env var, exactly as it did before this column existed), so this backfill prefers "correctly seeded" as the default reading for a row it cannot evaluate, at the cost of a narrow, accepted gap: a pre-#794 dbtool-imported encrypted value that never recorded modified_by (dbtool's import path did not stamp it before Origin existed) reads as seeded rather than explicit after upgrade. Every writer as of #794 -- SettingsService.Set, SeedDefaults, and dbtool's config-import and seed paths -- stamps Origin directly, so this gap cannot recur going forward.
Package dbschema: pre-migration duplicate-identity check for users (#724).
api/models/models.go backs users(provider, provider_user_id) with the unique idx_users_provider_lookup (#701). On a database that never had this index before, AutoMigrate's CREATE UNIQUE INDEX genuinely aborts with ORA-01452 / PostgreSQL 23505 if duplicate rows already exist. But on a database that already had the pre-#701 *non-unique* index by that name, AutoMigrate does not reach that failure at all (see the index-existence probe below and #732) -- so CheckDuplicateUserProviderIdentities only hard-aborts when the index is genuinely about to be created; otherwise it logs the duplicates loudly and lets startup continue, since the constraint isn't being enforced regardless of what this check does. Unlike groups (#704), users get no automatic dedupe here: merging user identities repoints ownership and access rows across many tables and is a security-sensitive operator decision, not something startup code should do silently.
Package dbschema: one-time upgrade of a pre-#701 non-unique idx_users_provider_lookup to a genuine UNIQUE index (#732).
#701 changed the GORM tag on users(provider, provider_user_id) from `index:` to `uniqueIndex:`. On a database created after that, AutoMigrate builds the index unique and there is nothing to do. On a database that already carried the plain index under the same name, AutoMigrate never upgrades it -- GORM's migrator reconciles an index's *existence*, never its uniqueness -- so the constraint #701/#710 intended has been silently absent on every pre-#701 deployment since.
On Oracle it is worse than a no-op: gorm-oracle's HasIndex binds the tag's lowercase name as a query *value* against USER_INDEXES.INDEX_NAME, which holds the uppercase name TMI's unquoted DDL actually created, so HasIndex always answers "absent"; AutoMigrate re-issues CREATE UNIQUE INDEX every pass, Oracle rejects it at name resolution with ORA-00955 (not the ORA-01452 a duplicate-data failure would raise), and TMI's own ignoreOracleAlreadyExists swallows that as benign.
AutoMigrate cannot express the fix, so it is raw DDL here: drop the non-unique index and recreate it UNIQUE, gated on the table being duplicate-free.
Package dbschema provides database schema validation for TMI. It validates that the database schema matches the expected structure defined by GORM models in api/models/models.go.
Index ¶
- Constants
- func AcquireMigrationLock(ctx context.Context, db *gorm.DB, name string) (release func(), err error)
- func BackfillSystemSettingOrigin(ctx context.Context, db *gorm.DB) (int64, error)
- func CheckDuplicateUserProviderIdentities(db *gorm.DB) error
- func ComputeModelsFingerprint(models ...any) string
- func DeduplicateGroups(db *gorm.DB) (int64, error)
- func DropRetiredMetadataIndexes(ctx context.Context, db *gorm.DB) error
- func EnsureMetadataInitrans(ctx context.Context, db *gorm.DB) error
- func EnsureSparseUserEmailIndex(ctx context.Context, db *gorm.DB) error
- func EnsureSystemSettingOriginCheckConstraint(ctx context.Context, db *gorm.DB) error
- func EnsureUserProviderLookupUnique(ctx context.Context, db *gorm.DB) error
- func ExecDDL(ctx context.Context, db *gorm.DB, ddl string) error
- func InstallAuditAppendOnlyTriggers(ctx context.Context, db *gorm.DB, floors AuditFloorConfig) error
- func InstallPostgresDefaultIsolation(ctx context.Context, db *gorm.DB) error
- func InstallThreatModelAliasSequence(ctx context.Context, db *gorm.DB) error
- func PruneRetiredSystemSettings(db *gorm.DB) (int64, error)
- func ReadSchemaStamp(db *gorm.DB) (fingerprint string, appliedAt time.Time, found bool, err error)
- func RecordSchemaFingerprint(db *gorm.DB, fp string) error
- func SchemaFingerprintCurrent(db *gorm.DB, desired string) bool
- func UserProviderLookupIndexIsUnique(db *gorm.DB) (bool, error)
- func WithMigrationLock(ctx context.Context, db *gorm.DB, name string, fn func() error) error
- type AuditFloorConfig
- type ColumnSchema
- type ConstraintSchema
- type IndexSchema
- type SchemaValidationResult
- type TableSchema
Constants ¶
const MigrationLockName = "tmi_schema_migration"
MigrationLockName is the one cross-replica lock every schema-evolution entry point serializes on: the server's startup migration (runMigrationsLocked), `tmi-dbtool --schema`, and the deprecated InitAuthWithConfig path. They all run the same DDL against the same schema, so they must contend for the same name -- a second name would serialize each entry point against itself and nothing else (#737).
const ThreatModelAliasSequenceName = "tmi_threat_model_alias_seq"
ThreatModelAliasSequenceName is the sequence that allocates the global ThreatModel alias on PostgreSQL and Oracle. Unquoted, Oracle folds the identifier to upper case; api.AllocateNextAlias references the same literal when issuing NEXTVAL, and must be kept in sync.
Variables ¶
This section is empty.
Functions ¶
func AcquireMigrationLock ¶
func AcquireMigrationLock(ctx context.Context, db *gorm.DB, name string) (release func(), err error)
AcquireMigrationLock takes an exclusive, server-wide named lock that is released by calling the returned function. Used to serialize startup-time migrations across multiple replicas. The function blocks until the lock is acquired (subject to context cancellation).
On PostgreSQL: uses pg_advisory_lock with a deterministic int64 derived from sha256(name). On Oracle: uses DBMS_LOCK.ALLOCATE_UNIQUE + DBMS_LOCK.REQUEST. Other dialects return an error.
The release function is idempotent and safe to defer. SEM@dea6869d1be777f8e297ccf30db5f7c272b226f9: lock a named schema-migration advisory lock, dispatching to the correct dialect implementation (reads DB)
func BackfillSystemSettingOrigin ¶
BackfillSystemSettingOrigin stamps origin = 'explicit' on every NULL-origin system_settings row that shows operator intent -- modified_by is set, or its value differs from (or is not covered by) what expectedSeedValues reports SeedDefaults would seed for that key today. Every other NULL-origin row is left as NULL, which now reads as seeded (see the package comment).
The modified_by signal is written only by SettingsService.Set (the admin PUT and dbtool import paths). ReEncryptAll used to stamp it on every row it rotated, which would have made this backfill read a seeded row as operator-set; since #805 it writes only the ciphertext column, so key rotation can never manufacture intent. No install ever ran reencrypt before that fix, so no remediation of existing rows is needed.
Idempotent and cheap in steady state: once a row is stamped, it no longer matches `origin IS NULL` and is never re-examined. Safe to run on every boot, ahead of AutoMigrate, mirroring DeduplicateGroups (group_dedupe.go). SEM@9d11bae97f4fbff310c3bb9d258f4a55808f8abc: backfill explicit origin onto pre-existing system settings rows showing operator intent (writes DB)
func CheckDuplicateUserProviderIdentities ¶
CheckDuplicateUserProviderIdentities detects duplicate users(provider, provider_user_id) rows ahead of AutoMigrate (#724). If idx_users_provider_lookup is not yet present, AutoMigrate is about to attempt CREATE UNIQUE INDEX and would abort with ORA-01452 / SQLSTATE 23505 against these rows -- this returns an actionable, named-rows error instead of that opaque abort. If the index already exists (a pre-#701 database whose non-unique index AutoMigrate cannot upgrade in place -- #732), that abort was never actually going to happen, so this logs the duplicates at ERROR and returns nil so startup continues -- unchanged behavior for every deployment that currently boots. Unlike groups (#704), users get NO automatic dedupe: merging user identities repoints ownership and access rows and is a security decision an operator must make. NULL provider or provider_user_id rows are excluded -- a composite unique index never constrains a row where any key column is NULL. SEM@637f0bdb33357d5c2d47d7c06b0e6e214c1962e6: detect duplicate user provider identities and return an actionable startup error only when the unique index would genuinely fail to create (reads DB)
func ComputeModelsFingerprint ¶
ComputeModelsFingerprint returns a stable SHA-256 over the schema-relevant shape of the given GORM models: each model's type identity, its fields, the Go type of each field, and the field's `gorm` struct tag (which carries column name, type, size, index, unique, and constraint directives). It thus changes iff a model's migratable definition changes, so a recorded fingerprint equal to the freshly computed one means AutoMigrate would be a no-op. Model and field ordering are normalized (sorted) so merely reordering struct fields or the model slice does not change the result. SEM@70c02e3f4b4dd833280d8f3ca9d152b483013ffe: compute a stable SHA-256 fingerprint over GORM model schemas (pure)
func DeduplicateGroups ¶
DeduplicateGroups finds groups(provider, group_name) keys with more than one row, keeps the earliest row per key (by first_used, tie-broken by internal_uuid for determinism), repoints referencing rows in group_members, threat_model_access, and survey_response_access from the removed duplicates onto the survivor, and deletes the duplicates. Each key is resolved in its own transaction. It is idempotent — a concurrent replica that already resolved a key leaves fewer than two rows for it, and the key is skipped — and cheap (one query) when there are no duplicates, so it is safe to run on every boot immediately before AutoMigrate creates uniq_groups_provider_group_name. SEM@bb40881560ec43c848a818a906635c7d26b0b603: dedupe groups(provider, group_name) and repoint child rows before the unique index is created (writes DB)
func DropRetiredMetadataIndexes ¶
DropRetiredMetadataIndexes removes the four timestamp-leading metadata indexes that #784 took out of models.Metadata from a database that still carries them. Idempotent: on a current database it costs one catalog query per retired name.
Safe, and required, to call on every boot after AutoMigrate, including when the #480 fingerprint fast path skips AutoMigrate: AutoMigrate never drops an index, so nothing else would ever remove them.
Runs on PostgreSQL and Oracle only. SQLite is the unit-test dialect and carries no production data, so it is left untouched (the design's chosen no-op path, pinned by TestDropRetiredMetadataIndexes_SQLiteIsNoOp).
Never aborts startup on a DDL failure, only on a failure to read the catalog: a retired index that survives is a write-cost nuisance, not a correctness problem, and the next boot or `tmi-dbtool --schema` retries. SEM@9d11bae97f4fbff310c3bb9d258f4a55808f8abc: drop retired metadata timestamp indexes if present, warning on failure (mutates DB)
func EnsureMetadataInitrans ¶
EnsureMetadataInitrans raises INITRANS to metadataInitransTarget on the Oracle metadata table and its six surviving indexes (#783). Idempotent: on a database already at or above the target it costs three catalog queries. No-op on every other dialect; INITRANS is an Oracle physical attribute.
Safe, and required, to call on every boot after AutoMigrate, including when the #480 fingerprint fast path skips AutoMigrate: physical attributes are not part of the model fingerprint, and AutoMigrate could not set them anyway.
Order matters at the call sites: DropRetiredMetadataIndexes runs first so the four retired indexes are never rebuilt here.
Never aborts startup on a DDL failure, only on a failure to read the catalog. ALTER TABLE ... MOVE ONLINE and ALTER INDEX ... REBUILD ONLINE are privileged, take DDL locks, and can fail on a busy table or under a runtime user without ALTER; in every such case the database is left as it was (each statement is its own transaction) and the next boot or `tmi-dbtool --schema` retries. The outcome is decided by re-reading the catalog after the DDL, not by trusting the DDL's error. SEM@b01ccb8e475aed5b956de76b96fe25b3de6076d0: raise INITRANS on the Oracle metadata table and indexes below target (writes DB)
func EnsureSparseUserEmailIndex ¶
EnsureSparseUserEmailIndex creates a unique index over (provider, email) restricted to rows with NULL provider_user_id, closing the duplicate-user gap idx_users_provider_lookup cannot cover on its own -- that index does not constrain sparse rows on either engine (see the package comment for why the two engines differ on how), so two concurrent email-only sparse inserts (#720) could otherwise both succeed. PG/SQLite use a partial index; Oracle a function-based index whose key expressions evaluate to NULL (hence excluded from the index) for every non-sparse row. email is not-null on models.User (size:320), so a NULL-email divergence between the two dialects' NULL handling is not reachable via the normal insert path (EnrichAuthorizationEntry already 400s an empty email).
Idempotent; safe -- and required -- to call on every boot after AutoMigrate, including when the fingerprint fast path skips AutoMigrate entirely (this index is not part of the model fingerprint). Pre-existing sparse duplicates produce an actionable, named-rows error instead of being auto-merged, mirroring #724's operator-driven policy for user identities.
Probes for the index by name first (oracle-db-admin review, 1.8.4): Oracle has no CREATE INDEX IF NOT EXISTS, so an unconditional Exec would re-issue the DDL -- and hit a swallowed-but-ERROR-logged ORA-00955 -- on every boot forever, exactly the failure mode ensureSchemaVersionTable (schema_version.go) already exists to avoid. Skipping both the duplicate scan and the DDL once the index is confirmed present also means this degrades to a single cheap catalog lookup in steady state instead of a full-table GROUP BY on every boot. SEM@9d11bae97f4fbff310c3bb9d258f4a55808f8abc: build a dialect-appropriate unique index over sparse user emails (writes DB)
func EnsureSystemSettingOriginCheckConstraint ¶
EnsureSystemSettingOriginCheckConstraint adds systemSettingOriginCheckName to system_settings, restricting origin to NULL, 'seeded', or 'explicit'. Unlike EnsureSparseUserEmailIndex/EnsureUserProviderLookupUnique, the CHECK clause itself is dialect-independent -- PostgreSQL and Oracle both accept `CHECK (origin IS NULL OR origin IN ('seeded','explicit'))` verbatim -- so only the idempotency story differs. Neither engine has an `ADD CONSTRAINT IF NOT EXISTS`, so the catalog is probed first, the same pattern #732/#736 use for indexes and tables.
A no-op on SQLite: it is a test-fixture-only dialect here (see sparseUserEmailIndexExists), and unlike CREATE INDEX, SQLite's ALTER TABLE has no ADD CONSTRAINT form at all -- there is nothing to probe or run. SEM@9d11bae97f4fbff310c3bb9d258f4a55808f8abc: add the origin CHECK constraint to system_settings, idempotently per dialect (writes DB)
func EnsureUserProviderLookupUnique ¶
EnsureUserProviderLookupUnique brings idx_users_provider_lookup to its intended UNIQUE definition on a database where it is missing or non-unique (#732). Idempotent: on a correct database it costs one catalog query.
Safe -- and required -- to call on every boot after AutoMigrate, including when the #480 fingerprint fast path skips AutoMigrate entirely: an index's uniqueness is not part of the model fingerprint, so a stamped database carrying the pre-#701 index would otherwise never be reconsidered.
The upgrade is gated on users(provider, provider_user_id) being duplicate-free. When duplicates exist the index genuinely cannot be made unique, so this logs the offending rows at ERROR and returns nil, matching CheckDuplicateUserProviderIdentities' (#724) operator-driven policy: merging user identities repoints ownership and access rows across many tables and is a security decision startup code must not make on its own.
Note the deliberate asymmetry with EnsureSparseUserEmailIndex (#720), which hard-aborts startup on pre-existing duplicate sparse emails. That index is new, so a database carrying such duplicates has never had them tolerated and failing loudly is safe. This index predates #701 on every deployment that has the problem, so those databases boot today and must keep booting; aborting here would take down every pre-#701 deployment on upgrade (oracle-db-admin review, #732).
This function never aborts startup on a DDL failure -- only on a failure to read the catalog at all. A runtime user without DDL privileges, a busy table, or an unexpected DDL error all leave the database exactly as it was (or restored to it, see below) and log ERROR; the next boot, or a `tmi-dbtool --schema` run by an admin-privileged user, retries. The alternative -- crash-looping a server over an index it may not be privileged to touch -- is a larger outage than the missing constraint. SEM@b01ccb8e475aed5b956de76b96fe25b3de6076d0: upgrade the users provider-lookup index to UNIQUE, recreating it if needed (writes DB)
func ExecDDL ¶
ExecDDL is execMigrationDDL for callers outside this package (test fixtures such as test/testdb.Truncate on Oracle). Same contract: DDL must never go through gorm.DB.Exec/Raw on Oracle (#763), and the statement text must come from code, not from user input. SEM@9d72eab25ff7508fb014a7b6d27836306f85bb2c: expose pinned-session DDL execution to other packages (mutates DB)
func InstallAuditAppendOnlyTriggers ¶
func InstallAuditAppendOnlyTriggers(ctx context.Context, db *gorm.DB, floors AuditFloorConfig) error
InstallAuditAppendOnlyTriggers installs triggers on audit_entries, version_snapshots, and system_audit_entries that block all UPDATEs and block DELETEs of rows younger than a per-table age floor. Idempotent across re-runs (the server reinstalls on every boot, so floor changes take effect on restart).
Retention policy (#453, #400): aged-out pruning is a supported, in-app operation — the scheduled AuditPruner deletes rows older than the configured retention, and the trigger's age floor (retention minus a 1-day clock-skew margin, never below a hard minimum) permits exactly that. There is no bypass flag or privileged session state: an attacker holding the app's DB credentials cannot delete or modify any row younger than the floor, and cannot UPDATE any row ever. Audit rows are immutable evidence; a pruned version snapshot simply makes the corresponding rollback return 410 Gone.
On Oracle ADB the triggers use CREATE OR REPLACE TRIGGER; on PostgreSQL, CREATE OR REPLACE FUNCTION + DROP TRIGGER IF EXISTS + CREATE TRIGGER. SQLite is skipped. SEM@c3167c5165bed5f9d97b7f7eef032894393a917a: install DB-level append-only triggers on audit tables, blocking UPDATE and recent DELETE (mutates DB schema)
func InstallPostgresDefaultIsolation ¶
InstallPostgresDefaultIsolation pins the connecting role's default_transaction_isolation to 'serializable' on PostgreSQL, as defense-in-depth behind the per-transaction SERIALIZABLE wrapper. It is a no-op on every other dialect. Non-fatal by contract: callers log and continue on error (the wrapper remains the authoritative mechanism).
ALTER ROLE CURRENT_USER is used rather than ALTER DATABASE because it is the portable form — it works on managed PostgreSQL (e.g. Heroku) where the connecting role owns its own settings but may not own the database. SEM@080eef4c36738f6b82a5dddaff40f2580081b8bc: pin the current PostgreSQL role default transaction isolation to serializable; no-op on other dialects (mutates shared state)
func InstallThreatModelAliasSequence ¶
InstallThreatModelAliasSequence creates the global ThreatModel alias sequence if it does not already exist, seeded above every alias currently in use so its first NEXTVAL can never collide with the unique threat_models.alias index.
It is idempotent and safe to run on every boot: once the sequence exists the seed step is skipped, so an already-advanced sequence is never moved backwards (which could otherwise hand out a value an in-flight create already holds). SQLite and any other dialect keep the row-counter allocator and are no-ops here. SEM@15d1523404ac67830fbe68f72a41c9683aa564b6: install the threat model alias DB sequence for the active dialect, skipping unsupported dialects (reads DB)
func PruneRetiredSystemSettings ¶
PruneRetiredSystemSettings deletes system_settings rows whose keys the registry no longer declares, returning the number of rows removed.
SEM@32d2fef5a48bbd2ea65b9aa7d5232510b52a4aca: delete system_settings rows for keys the registry no longer declares (writes DB)
func ReadSchemaStamp ¶
ReadSchemaStamp returns the recorded schema fingerprint and when it was applied, for callers that need to report a mismatch rather than act on it (tmi-dbtool's preflight, #807). found is false when no stamp row exists (a pre-#480 database, or one never migrated by a stamping binary). Ensures the stamp table exists, so the one side effect is an empty table on a database that never had one. SEM@9d72eab25ff7508fb014a7b6d27836306f85bb2c: fetch the stored schema fingerprint stamp with its applied-at time (reads DB)
func RecordSchemaFingerprint ¶
RecordSchemaFingerprint upserts the single stamp row to `fp`. Callers invoke it only after a successful AutoMigrate, so a subsequent boot computing the same fingerprint can take the fast path. The upsert is portable across dialects (update-then-insert rather than ON CONFLICT / MERGE); server callers run it under the cross-replica migration advisory lock, so the update/insert pair cannot race a second writer. SEM@70c02e3f4b4dd833280d8f3ca9d152b483013ffe: store the schema fingerprint in the DB stamp table after migration (writes DB)
func SchemaFingerprintCurrent ¶
SchemaFingerprintCurrent reports whether the database already records a schema fingerprint equal to `desired`. When true, the caller may safely skip the introspection-heavy AutoMigrate pass (#480).
Any error — the stamp table does not exist yet, the database user lacks DDL permission to create it, or no row has been written — is treated as "not current" so the caller falls back to running AutoMigrate. Such conditions are logged at debug, never surfaced, because an unavailable fast path must never turn into a failed boot. SEM@70c02e3f4b4dd833280d8f3ca9d152b483013ffe: report whether the stored schema fingerprint matches the desired one (reads DB)
func UserProviderLookupIndexIsUnique ¶
UserProviderLookupIndexIsUnique reports whether idx_users_provider_lookup is currently a valid UNIQUE index with the definition this package intends, so a caller that needs the upgrade to have actually happened can say so.
"The definition this package intends" is dialect-specific and is not the same as the catalog's UNIQUENESS column on Oracle: there it must also be function-based and ENABLED (#760), because a plain UNIQUE index over (provider, provider_user_id) reports as UNIQUE while enforcing stricter NULL semantics than PostgreSQL. See userProviderLookupDDL.
EnsureUserProviderLookupUnique deliberately never aborts a server boot over a DDL failure -- crash-looping a server over an index it may not be privileged to touch is the larger outage. `tmi-dbtool --schema` is the opposite case: it is the admin-privileged remediation path, and exiting 0 having silently failed to do the one thing the operator ran it for is the wrong outcome (oracle-db-admin review, #732). Returns false, not an error, when the users table does not exist. SEM@605e29546fe60dc8ac69862013475720a74dea8b: report whether the users provider-lookup index is currently valid and unique (reads DB)
func WithMigrationLock ¶
WithMigrationLock runs fn while holding the named cross-replica advisory lock, releasing it on every exit path.
SQLite (used by narrow unit tests and some tooling) has no advisory locks; AcquireMigrationLock reports that as "unsupported dialect", and fn runs unlocked -- a single-process in-memory SQLite is inherently single-writer. Any other acquisition failure is returned without running fn: proceeding unserialized is exactly the concurrent-DDL exposure the lock exists to prevent. SEM@ebd32e782424ee1fd1698669b7522b6ab3eccf42: acquire the schema-migration advisory lock, run a callback, then release it (mutates DB)
Types ¶
type AuditFloorConfig ¶
type AuditFloorConfig struct {
AuditRetentionDays int
VersionRetentionDays int
TombstoneRetentionDays int
SystemAuditRetentionDays int
}
AuditFloorConfig carries the retention configuration used to derive the per-table delete age floors baked into the append-only triggers at install time. The values come from the same env config the audit pruner reads (AUDIT_RETENTION_DAYS, VERSION_RETENTION_DAYS, TOMBSTONE_RETENTION_DAYS, SYSTEM_AUDIT_RETENTION_DAYS), so the trigger floor and the pruner cutoff cannot drift within one boot. SEM@c3167c5165bed5f9d97b7f7eef032894393a917a: carry per-table retention days used to derive append-only trigger age floors (pure)
type ColumnSchema ¶
type ColumnSchema struct {
Name string
DataType string
IsNullable bool
DefaultValue *string
IsPrimaryKey bool
}
ColumnSchema represents the expected schema for a column SEM@6b74774f78533f16518466a3a49931403eb6c263: expected schema descriptor for a table column including type, nullability, and primary key flag (pure)
type ConstraintSchema ¶
type ConstraintSchema struct {
Name string
Type string // CHECK, FOREIGN KEY, UNIQUE
Definition string
ForeignTable string // For foreign keys
ForeignColumns []string // For foreign keys
}
ConstraintSchema represents the expected schema for a constraint SEM@6b74774f78533f16518466a3a49931403eb6c263: expected schema descriptor for a database constraint including type, definition, and foreign key refs (pure)
type IndexSchema ¶
IndexSchema represents the expected schema for an index SEM@6b74774f78533f16518466a3a49931403eb6c263: expected schema descriptor for a database index including name, columns, and uniqueness (pure)
type SchemaValidationResult ¶
type SchemaValidationResult struct {
Valid bool
Errors []string
Warnings []string
TotalMigrations int // Repurposed: total expected tables
AppliedMigrations int // Repurposed: tables found
DatabaseSchemaValid bool
MigrationConsistent bool
}
SchemaValidationResult represents the result of schema validation SEM@b27f2faa569c2df7076488c50b0a808fe812a34d: aggregate result of a database schema validation check, including errors and table counts (pure)
func ValidateSchema ¶
func ValidateSchema(db *sql.DB) (*SchemaValidationResult, error)
ValidateSchema validates the actual database schema against expected tables. This is a PostgreSQL-specific validation that checks essential tables exist. SEM@acf29174839ed9f1cb1950265092e2bdacdcb5bd: validate that required database tables exist (reads DB)
func ValidateSchemaWithMigrations
deprecated
func ValidateSchemaWithMigrations(db *sql.DB, migrationPath string) (*SchemaValidationResult, error)
ValidateSchemaWithMigrations is deprecated - kept for backward compatibility. Use ValidateSchema instead which validates against GORM models.
Deprecated: This function references legacy SQL migrations that are no longer used. SEM@acf29174839ed9f1cb1950265092e2bdacdcb5bd: deprecated: validate schema using GORM table checks instead of legacy migrations (reads DB)
func ValidateSchemaWithTables ¶
func ValidateSchemaWithTables(db *sql.DB) (*SchemaValidationResult, error)
ValidateSchemaWithTables validates schema by checking that essential tables exist. This replaces the migration-based validation now that TMI uses GORM AutoMigrate. SEM@073a204f272e91f38206995db7daac899361ce87: validate database schema by checking all essential tables exist (reads DB)
type TableSchema ¶
type TableSchema struct {
Name string
Columns []ColumnSchema
Indexes []IndexSchema
Constraints []ConstraintSchema
}
TableSchema represents the expected schema for a table SEM@6b74774f78533f16518466a3a49931403eb6c263: expected schema descriptor for a database table including columns, indexes, and constraints (pure)
func GetExpectedSchema ¶
func GetExpectedSchema() []TableSchema
GetExpectedSchema returns the complete expected database schema SEM@8ea37221e3186b49d52e78d8834a4e6dd35d2b93: return the full canonical database schema as a list of table descriptors (pure)
func GetTimmySchema ¶
func GetTimmySchema() []TableSchema
GetTimmySchema returns the expected schema for the Timmy AI assistant tables SEM@151ddeb133e95de990f71ca2c03be3261fe3de07: return the expected table schemas for the Timmy AI assistant (pure)
Source Files