latent-sre/save-toolkit/database-reliability

Database reliability

Keep data correct, durable, and fast, and make schema change safe in production. Our apps run on PCF and bind to managed relational services (Postgres / MS SQL; a few apps embed SQLite, which is not an operated engine — the one rule that matters is in stack-profile) — you operate at the application + data layer, not the DB platform internals. software-engineer writes persistence and migration code, loading api-craft when it serves an HTTP contract; this skill owns operating it safely, diagnosing it, and proving recovery.

Authority: inspect only through the lane's granted, output-protected read paths.

Live changes use production-change-gate, including its current

approval, incident-envelope, recovery, and re-entry rules. The human owner or approved protected

automation applies them; the agent diagnoses and prepares. An explicitly assigned isolated scratch

rehearsal follows its target, data-use, and actor scope; this skill grants no tools or live authority.

Core decision rules

Migration safety

• Prefer backward-compatible, online changes with no long table locks and no unbounded lock waits,

assessed on production-scale data rather than a tiny development table.

• Every migration needs a tested recovery strategy, not necessarily a reverse script. Choose the

path that preserves writes and data: a demonstrably lossless backout, a roll-forward or compensating fix, restore/PITR, or an expand/contract transition. Never offer a destructive reversal merely to make a packet look reversible.

• Use expand → contract when running code depends on the schema: expand with the compatible

shape; backfill in bounded batches; dual-write/read while both shapes coexist; switch only after verification; contract in a later deploy after proving nothing reads the old shape.

• Never rename or drop a shape in the same deploy while currently running code still uses it.
• Freeze the exact engine, version/edition, topology, table size, traffic pattern, migration-tool

transaction mode, and compatibility window before choosing DDL. Treat missing target facts as [unverified], not as permission to assume an online path.

Performance, durability, and ownership

• An actual/analyzed plan executes the statement. Default to a plan-only form. An executing

plan of INSERT/UPDATE/DELETE/MERGE on production is a live change even when rolled back; a transaction is not a universal rollback because effects may escape it.

• Production SQL needs DBA-authorized access and bounded load and output. Existing authorization

can cover read-only diagnostics, including an executing plan of a read-only query, without a change/recovery packet; mutations, their executing plans, and parameter changes use production-change-gate under the authority rule. Dashboard access alone does not grant SQL execution.

• Index for measured query patterns, avoid N+1 access and unbounded result sets, and verify any fix

with before/after evidence. Hand query/ORM implementation to software-engineer with the plan and contract.

• Backups must be monitored and restored in a drill. An untested backup does not prove recovery,

RPO, or RTO. Verify replication and failover rather than assuming them.

• Use scoped database credentials, never an application admin role. No unbounded UPDATE/DELETE:

require a predicate and row-count sanity check, plus the tested recovery strategy.

• During a DB-driven incident, the agent diagnoses and hands off; mitigation follows

production-change-gate, applied by the human owner or approved protected automation.

Read only the conditional procedure the request needs

Load every matching row and no others. Do not infer an engine/version, live authority, or recovery claim from the request. The entrypoint rules remain authoritative after a reference is loaded.

Output format

Summarize applicable evidence already gathered with its [verified], [sourced], or [unverified] labels and taint; refresh it when the target, state, or time window changes.

• Migrations: compatibility sequence, engine/version assumptions, production-scale lock/risk

assessment, forward change, verification, tested recovery strategy, and owner. Implementation goes to software-engineer.

• Performance: plan-only versus executing evidence labelled, with measured before/after results.
• Incidents/recovery: current evidence, hypothesis labels, human action boundary, and measured

recovery gaps. For a proposed live change, reference the gate record and identify missing approval or recovery evidence.