Thalarch Data / SQL

Database changes are data changes. Treat correctness, compatibility, rollback/roll-forward, and production scale as first-class constraints.

Preflight

Identify:

• database engine/version where known;
• ORM/query layer;
• migration framework;
• transaction model;
• schema ownership;
• production rollout constraints;
• query/test tooling.

Do not write engine-specific syntax based on memory if the actual engine/version can be confirmed.

Query correctness

Review:

• stable ordering;
• null semantics;
• duplicate/cardinality behavior;
• joins and fan-out;
• transaction isolation assumptions;
• race/lost-update behavior;
• timezone/collation/encoding where relevant;
• parameterization of untrusted values.

Never concatenate untrusted input into SQL identifiers/expressions without an explicit safe allowlist/escaping mechanism appropriate to the engine.

ORM discipline

Inspect generated/actual query behavior when performance or correctness depends on it.

Watch for:

• N+1 queries;
• accidental eager loading;
• lazy loads outside a valid session/context;
• row explosion from joins;
• unintended cascades;
• entity equality/identity mistakes;
• transaction boundaries hidden by repository helpers.

A unit test with a mocked repository does not prove ORM/database semantics.

Migrations

For production-compatible changes, prefer staged expand/migrate/contract patterns when old and new application versions may overlap.

Before a destructive or locking migration, consider:

• table/index size;
• lock duration;
• backfill strategy;
• nullable/default behavior;
• concurrent writers;
• retry/restartability;
• rollback vs roll-forward plan;
• replication/online-DDL constraints.

Never assume a migration is safe merely because it succeeds on an empty test database.

Performance

Use query plans/metrics before adding indexes or rewriting queries.

Check:

• selectivity and index order;
• full scans;
• sort/hash spills;
• repeated round trips;
• pagination strategy;
• connection-pool saturation;
• transaction duration;
• cache consistency.

Every new index has write/storage/maintenance cost.

Testing

Prefer real-database integration evidence for behavior that depends on SQL engine semantics, constraints, transactions, or ORM mappings. Use containerized/test DB infrastructure already present in the project when possible.

Verification

Report separately:

• schema/migration validation;
• query/ORM integration tests;
• performance evidence;
• production rollout assumptions that remain UNVERIFIED.