Persistence

---
name: persistence
description: Use when code reads or writes a datastore, written or reviewed, in any project whatever its architecture — beside the family's data-access skill whenever its templates are written, since the obligations they satisfy are stated here — who owns a transaction, where a driver error is translated and what its context carries, how constraint and table names are derived, how rows map back, which stored type a value gets, how a conflicting or older write is resolved, how a batched write is sized, how a paged read orders, how a schema change ships safely, and whether data-access code logs. Owns these store-generic rules and which of them bind given the store's properties; it binds no library and writes no file. The data-access files that satisfy them are the architecture family's — `flat-persistence` in the `pyhouse-flat` plugin, `hex-persistence` or `hex-store-repository` in the `pyhouse-hex` plugin — and the error classes, translation with the cause chained and its fallback are `exception-catalog`'s. - Also when asked whether a repository may commit, why a partial write happened, what a constraint should be named, whether to use a database enum type, whether a timestamp column needs a timezone, how to stop a stale update overwriting a newer one, how to deduplicate writes, how large a batched write's chunks may be, how to page through a table without skipping rows, whether a migration is safe to deploy, whether a repository should log, how a migration chain starts, or whether a cache may take writes.
---

# Persistence — the store-generic obligations

The obligations every program meets where it keeps data in a store, whatever its architecture family
and whatever the store. Each is stated so that it survives a change of driver, store or family; none
binds a library. The file that satisfies them — a table, a repository class, a revision — is written
under the project's own data-access skill: the architecture family's where one is installed, the
project's own layout where none is. **Which identifier a new row gets is not here**: the identity
scheme is the family's, because the two families answer it differently for reasons of their own.

## When to use vs. neighbours

- A flat-layered service's data-access package, its tables, its repository classes and where its
  migration history lives → `flat-persistence`, in the `pyhouse-flat` plugin, which binds these rules
  to one stack.
- A hexagonal service's relational adapter behind a port, its paired revision and its unit of work →
  `hex-persistence`, in the `pyhouse-hex` plugin; an aggregate on a key-value or document store →
  `hex-store-repository`, in the same plugin.
- The error classes a translation produces, translation with the cause chained, the mandatory
  fallback, and what `context` may and may not carry → `exception-catalog`.
- Which scope logs a failure the data-access code raised, and the event a success earns →
  `python-logging`.
- The declared type a row is mapped into, and which builtin holds an instant, an identifier or an
  exact decimal in memory → `python-style`.
- The store's connection settings, and where they are built → `python-settings`.
- What the repository class is called → `naming`; that constraint and table names are generated by one
  declared convention is here (rules 7, 13), and `naming` treats them as frozen once shipped.
- Which identifier a new row gets and where it is minted → the architecture family — `hex-application`,
  in the `pyhouse-hex` plugin, and `flat-persistence`, in the `pyhouse-flat` plugin, are examples.
- Whether two stores, or two data-access packages, should be one → `coupling`.
- Several distributions sharing one store, and which of them owns its schema → `python-workspace`.
- A unit a broker or a sender delivers at least once — when it is acknowledged, how its redelivery is
  bounded, why a redelivery changes nothing → `at-least-once-delivery`; rule 16 is the store's side of
  its ordering.
- Testing data-access code against a real store → `test-principles`, and the family's own test skill.

## Which rules bind — the store's properties

A rule whose property the store lacks has nothing to be true about. Answer these once per store,
before reading the rules:

| Does the store have… | If no, these do not apply |
|---|---|
| multi-statement transactions | rules 1, 2 — nothing spans statements, so nothing declares an owner |
| named constraints, and a driver that reports the violated one's name | rule 7, and the constraint name in rule 6 — without named constraints there is no name for three artifacts to agree on; where the driver reports no name, the migration still takes its names from the convention and the translator branches on the error's code |
| typed columns and constraints | rules 11, 13 |
| secondary indexes | rule 12 |
| a conditional or conflict-resolving write — a conflict clause, a `MERGE`, a conditional put | rule 15 — resolution is then the store's merge-time mechanism (rule 17), and rule 16 needs a single writer per key |
| a schema this program owns | rules 10–13, 19, 20 — they are the owner's; the program declares only what it reads and carries no migration history for it |
| a schema versioned by migrations | rules 19, 20 |
| two versions of the program running against it at once — a rolling deploy, or processes of different releases sharing one store | rule 19's expand-then-contract split — a single process upgrades the store where it starts, before its first statement, and one revision carries the whole change; such a program refuses to open a store stamped with a revision it does not know |

**Rules 3–6, 8, 9, 14, 16–18, 21 and 22 hold whatever kind of store it is**, SQL or not — a columnar store,
a document store, a key-value store or a vendor-managed index. A store answering *no* to every question
is not a poor fit for this skill: the rules that lapse lapse because their subject does not exist.

## Rules

### Transactions

1. **Transaction ownership is one declared decision per callable.** A callable either *accepts* a live
   connection or transactional handle and never commits or rolls back, or *opens and owns* one for the
   whole of its work. The two forms are mutually exclusive for one callable, and no connection is held
   in instance state between calls. A joining callable is handed the open handle, never a factory: one
   that opens its own is in a different transaction, and the defect shows up as a partial write rather
   than an error. Which form a callable takes is part of its contract: it decides how a caller composes
   it.
2. **Where one write spans more than one statement, it is one transaction, owned by the callable that
   spans them.** Splitting it across two connection blocks reopens the partial-write race the owning
   callable exists to close.
3. **No transaction spans two stores.** A unit writing to both writes first the store the other refers
   to, and is made whole either by a retry that completes it — each write idempotent by its key — or by
   compensation; which of the two is the family's. **A record has one authoritative store, the one its
   writes go to**: a cache or an index on another store holds only a copy that may be lost and rebuilt
   from it, and two copies of one record that both accept writes are not allowed.

### Driver errors and constraint names

4. **The data-access edge is where a driver error is translated, on every public method.**
   Translation with the cause chained, the mandatory fallback and the choice of the most specific class
   are `exception-catalog`'s rules 8–10; what this rule adds is where they bind — nothing above the
   data-access code ever sees the driver's type, and that covers a read, and the opening of the
   connection and the transaction, which fail with the driver's errors as surely as a write does.
5. **Several data-access classes on one store share its translation** — the driver-error types, the
   error-code classes and the fallback — from one module once a second class exists; only a branch for
   one constraint stays with the class that owns it, and every class moves in the commit that extracts
   it.
6. **The identifying context of a storage error is the offending field and the full constraint name.**
   What `context` may carry is `exception-catalog`'s rule 11; the constraint name is the one rule 7
   generates, so the caller and the tests can assert on it. An error that is not a refusal of the data
   carries the key or record it was addressing, where there is one.
7. **Constraint names are a contract, generated from one convention declared once.** A migration, a
   translator branch and a test must be able to name the same constraint without any of them inventing
   it; names left to the backend break all three. The translator matches on the full name the convention
   generates, never on a fragment of it — a fragment also matches a sibling constraint and reports the
   wrong field. Renaming one is a breaking change: the schema definition, the translator and the revision
   change in the same commit.

### Rows and stored values

8. **Rows cross the data-access boundary as the program's own declared types, and the mapping is a pure
   function the data-access code owns.** No IO. It normalizes what the driver hands back, including
   giving a naive timestamp its offset. Where a natural key is normalized, it is normalized once, here,
   so one unit test can pin the form — in Python, never inside the store's query language. The
   declared-type obligation itself is `python-style`'s.
9. **Every instant is stored unambiguously** — with its offset, or normalized to UTC where the store's
   type holds none — never as a naive wall-clock value. A default the store fills in is used only where
   the store is what stamps the row — a creation instant, which a row written by a migration then gets
   too; an instant fixed with the change, such as rule 16's ordering stamp, is written by the writer and
   never defaulted. A modification instant the program maintains is set in the update itself, because a
   store default fires only on insert.

### Schema

10. **The stored shape — column types, or a record's key and payload — is a design decision, not a
    transcription of the record's fields.** Choose it from what the value means and how it will be
    queried, and record the consequence where it bites — an on-delete choice is answered by what the
    data-access code's delete raises.
11. **A closed value set is a constraint over a text column, not a database enum type.** It then matches
    the program's own enum (`python-style` rule 12), and widening the set is an ordinary migration rather
    than a type alteration. The same holds for a length limit: it is a rule of the program, checked where
    the program checks it, not a stored type that needs a migration to change.
12. **Index what is filtered, joined and sorted on.** A foreign key gets no index for free, and a paged
    read's ordering column is as load-bearing as its filter's.
13. **A table name is derived from the record it holds by one rule the project declares once** —
    singular or plural, the same derivation for every table — so a name is derivable rather than
    remembered, and no table's name is chosen on its own.
14. **Data-access code creates no table, collection or index as a side effect of reading or writing;
    the schema changes only through its owner's migrations (rule 19).**

### Conflicts, ordering and reads

15. **Where a write resolves a conflicting row rather than refusing it, the resolution is explicit** —
    the key matched on is never among the columns updated, and an empty update set resolves to *do
    nothing*. The write names its conflict columns and its update columns; writing back the key you
    matched on is a no-op at best and a statement failure on a partial index. "Nothing to update" is a
    real case and must not become an update with an empty assignment list, which is a syntax error. A
    write that refuses a conflict translates it (rule 4).
16. **An older write never replaces a newer one for the same key.** Where writes for one key can arrive
    out of order, each carries the record's ordering stamp — a version or an instant fixed when the
    change was made or observed, the same on every delivery of it, never the time of a delivery attempt
    or of the write — and the write is conditional on it: a guarded conflict clause, a conditional put,
    or, where the store resolves at merge time, keeping the newest by it (rule 17). A store that can
    make no write conditional needs a single writer per key.
17. **Deduplication and conflict resolution belong to the store's own write-time or merge-time
    mechanism, never to an application read-before-write per row.** Where the write can resolve a
    conflict, rule 15 says how; where the store deduplicates at merge time, the store is configured for
    it once, in the schema — keeping the newest by rule 16's ordering stamp wherever an older write can
    arrive after a newer one — and a read that must see one row per key before the merge asks the store
    for its deduplicated view. Looking up which rows already exist before inserting costs a round trip
    the store never needed, and it still admits the duplicates two concurrent runs write between one
    run's read and its write. **A column aggregated across writes is resolved the same way,
    in the write or the merge, never by reading it first.** **Where one statement writes several inputs,
    those sharing one key are collapsed by that key before it is built** — the newest by rule 16's
    ordering stamp winning wherever that stamp guards the write, the last otherwise, or aggregated as the
    conflict clause would — because a store resolving conflicts per statement may refuse to touch one row
    twice within it. Collapsing a batch already in memory reads nothing from the store and is bounded by
    the batch, so it is not the read-before-write this rule forbids.
18. **A read that promises an order produces it deliberately, never by insertion accident, and a paged
    read — by cursor or by offset — orders by a total order, a cursor carrying every column of it.** A
    limited read resumed from the last row's value of a column that is not unique skips every row
    sharing that value beyond the page's edge — rows written together share one timestamp, so a
    single boundary drops thousands of them with no error — and an offset page over the same order
    repeats or skips them as the ties fall. Order by the column plus a unique tiebreaker and resume
    strictly after the pair (keyset pagination), or do not limit the read.

### Schema changes

19. **A migration runs before the new code touches the store — as a deploy step where there is one —
    and never twice at once.** Where two versions of the program run against one store at once, every
    schema change is compatible with the code still running, so a change that needs both halves lands in
    two releases, never in one revision. Expand first — add the table, or the column nullable or with a
    default the store fills in — while the old code keeps working against it. Contract in a later
    release, once nothing running reads or writes what is removed or tightened — a column dropped or
    renamed, a `NOT NULL` added, a constraint narrowed. A column added `NOT NULL` with no default passes
    a schema diff and a round trip over an empty database, then fails the deploy on a populated table.
    The migration is applied by an existing tool for the store wherever one fits. A hand-written runner
    is written only where none does, and does what a tool would — records each applied version in the
    store itself, applies in order, stops at the first failure, and never runs twice at once. That
    exclusion is a lock the store or the migration tool provides, or else one dedicated job per deploy,
    never a run from every replica at start-up.
20. **Each schema change is one new revision whose downgrade reverses its upgrade, in the opposite
    order, and a round trip proves it, on a chain rooted at its first real revision.** The suite runs the history up, down to the base and up again
    against a real store; a downgrade nothing runs is a reverse path nobody has proven. A later change is
    a new revision, never an edit to a shipped one, and a revision a tool generated is a draft, reviewed
    against rule 19 before it is committed. **A new chain is rooted at its first real revision**, with no
    placeholder ahead of it for every store to replay as a no-op. **A baseline exists only over a schema
    that already exists**: that schema's definition frozen by hand the day it is written, never built from the
    schema definition as it stands when the revision runs, written once into an empty chain, and each
    existing store marked at it rather than run through it — or the chain stops being replayable from zero.

### Batched writes

21. **Where a write takes a batch — because the volume, the memory bound or the store calls for it,
    never by default — it is one statement per chunk, never a statement per row in a loop, and each
    chunk stays within the store's per-statement limit, its size a named constant computed once.**
    Inputs sharing a key are collapsed before each statement is built (rule 17). The limit is the
    store's — bind parameters per statement for a SQL driver, where one statement binds chunk size ×
    every value one row binds, a default the client fills in included; items or bytes per request
    elsewhere — and a batch crossing it fails at execute time on size alone. The constant is a fact of
    the store, not a tunable: a batch size chosen for memory or throughput is a setting
    (`python-settings` rule 5), capped by it. It is named beside the statement that reads it, never a
    literal at a call site, and is the default of the size the batch method takes, so a test passes a
    small one to cross a chunk edge (`test-principles`, *Datastore contract* rule 7). A statement per
    row turns ten thousand rows into ten thousand round trips; on a store that penalises small writes
    instead — a columnar store merging parts — the constant is a floor.

### Logging

22. **Data-access code logs nothing.** It re-raises every failure, so `python-logging`'s log-once rule
    never lands on it, and a success is its caller's to log.

## Hard stops

- Choosing or adding the error class a translation raises → stop, use `exception-catalog`.
- Deciding which identifier a new row gets, or where it is minted → stop, that is the architecture
  family's identity scheme (`hex-application`, in the `pyhouse-hex` plugin; `flat-persistence`, in the
  `pyhouse-flat` plugin); with neither installed, the project picks one scheme for every table, and this
  skill states no policy.
- Deciding where a data-access package sits, or what its files are → stop, use the architecture
  family's data-access skill (`flat-persistence`, in the `pyhouse-flat` plugin; `hex-persistence` or
  `hex-store-repository`, in the `pyhouse-hex` plugin), or the project's own layout where none applies.