Business Rules - Databases & SQL

This document lists the fundamental business rules of the Databases & SQL domain, each identified by a unique BR-NNN code. Every rule is technology-independent and describes an invariant constraint on the behavior of a relational database management system.

---

BR-001 - Transaction atomicity

Code: BR-001 Name: Every Transaction is atomic: all-or-nothing Description: A set of operations wrapped in a Transaction must be applied entirely or not at all. If any operation fails, the whole transaction is undone (rollback) and the database returns to the state prior to BEGIN. No visible partial states are allowed. Rationale: Guarantee the atomicity property (the "A" in ACID) and data integrity in the presence of failures. Priority: CRITICAL Actors: Backend Developer, DBMS Violation: inconsistent state with partial writes → forced rollback. Traceability: glossary ACID, Transaction, Savepoint, Write-Ahead Log; SQL:2016 §4.35.

---

BR-002 - Referential integrity enforced by constraints

Code: BR-002 Name: Foreign keys must reference existing rows Description: A column defined as FOREIGN KEY cannot contain a value that does not exist in the referenced table (except NULL if allowed). DELETE/UPDATE operations on the referenced row follow the declared policy (RESTRICT, CASCADE, SET NULL). Constraints are enforced by the DBMS, not by the application. Rationale: Guarantee relational consistency and prevent orphan rows. Priority: HIGH Actors: DBA, Backend Developer Violation: ConstraintViolated event; the operation is rejected with an integrity error. Traceability: glossary Referential integrity, Foreign key, Constraint; SQL:2016 §11.8.

---

BR-003 - Schema migrations are versioned and ordered

Code: BR-003 Name: Every SchemaMigration has a unique version and applies in deterministic order Description: Changes to the Schema happen only through versioned SchemaMigrations, applied in a monotonic sequence. An already-applied migration is not re-run; a new migration applies only after all previous ones. Migration state is recorded in a control table. Rationale: Make schema evolution reproducible, traceable, and identical across environments (dev/staging/prod). Priority: HIGH Actors: DBA, Migration Tool, Backend Developer Violation: non-deterministic order → divergent schema across environments. Traceability: glossary DDL, Schema; state-machine migration-lifecycle.md; SchemaMigrationApplied event.

---

BR-004 - Application queries use prepared statements

Code: BR-004 Name: Every query with variable input must use bound parameters, never string concatenation Description: Queries that include values coming from outside must be built with prepared statements and bound parameters (?/$1/:name), so values are never interpreted as SQL. Concatenating input into SQL strings is forbidden. Rationale: Prevent SQL injection and enable execution-plan reuse. Priority: CRITICAL Actors: Backend Developer Violation: SQL injection vulnerability; code rejected in review. Traceability: glossary Prepared statement, SQL injection; OWASP A03:2021.

---

BR-005 - Indexes serve access patterns, not every column

Code: BR-005 Name: An Index is created to support real queries, balancing reads and writes Description: Indexes speed up selective reads but slow down writes and consume space. An Index should be created when a frequent query filters/sorts/joins on a selective column, and removed if unused. Composite indexes follow the left-prefix order of the most selective predicates. Rationale: Optimize read performance without excessively penalizing writes. Priority: MEDIUM Actors: DBA, Query Optimizer, Backend Developer Violation: over-indexing (slow writes) or recurring full table scans (slow reads). Traceability: glossary Index, Composite index, B-tree index; IndexCreated event; QueryPlan.

---

BR-006 - The isolation level is chosen deliberately

Code: BR-006 Name: Every Transaction operates at an explicit, appropriate isolation level Description: The isolation level (READ COMMITTED, REPEATABLE READ, SERIALIZABLE, etc.) determines which concurrency anomalies (dirty read, non-repeatable read, phantom) are possible. The choice must balance correctness and contention; higher levels reduce anomalies but increase locks/aborts. Rationale: Guarantee concurrency correctness without needlessly degrading concurrency itself. Priority: HIGH Actors: Backend Developer, DBA Violation: unhandled concurrency anomalies (inconsistent data) or excessive contention. Traceability: glossary Isolation level, MVCC; SQL:2016 §4.35.4.

---

BR-007 - Verified, restorable backups

Code: BR-007 Name: A recent, tested backup always exists for recovery Description: The database must have periodic backups (full + incremental/WAL) whose restorability is verified with restore tests. Backup frequency and retention honor the declared RPO/RTO objectives. An untested backup is not considered valid. Rationale: Guarantee recoverability in case of failure, corruption, or human error. Priority: CRITICAL Actors: DBA Violation: irreversible data loss in a disaster. Traceability: glossary Write-Ahead Log, Replica; BackupCompleted event.

---

BR-008 - The connection pool is bounded and monitored

Code: BR-008 Name: Database connections are managed via a pool with a maximum limit Description: Applications access the database through a connection pool with a maximum number of connections sized to the server resources. Idle connections are recycled; those exceeding the pool wait or fail with an explicit timeout, never opening unbounded connections. Rationale: Prevent DBMS resource exhaustion and cascading degradation. Priority: HIGH Actors: Backend Developer, DBA Violation: ConnectionPoolExhausted event; new requests error out or wait indefinitely. Traceability: glossary Connection pool; ConnectionPoolExhausted event.