# S16 — Store state in persistent storage

Status: REFINED

Agreed after the grill-me interview; shared understanding confirmed.

## User story

As a dance school maintainer, I want application data to be stored persistently,
so that confirmed changes and saved scheduling results survive server restarts
and application crashes.

## Storage decision

Use PostgreSQL with relational tables for schools, dancers, coaches, groups,
shows and their relationships. Use Flyway to version and apply database schema
changes.

The existing domain has shared relationships and operations on individual school
components. Related changes must be atomic, and multiple maintainers must be able
to work without silently overwriting each other's changes. These requirements
favor relational storage with transactions and referential constraints.

A document database was considered: a whole-school document would align with
JSON import/export. However, the agreed requirements provide no concrete benefit
for that approach over relational storage. The application owns a fixed domain
model; school-defined arbitrary fields and structures are not required.

JSON remains the import/export format, rather than the primary storage model.

## Scope and deployment

- Support multiple schools and concurrent maintainers.
- Target one production environment with one active backend instance.
- A separate database service and its operational management are acceptable.
- Multiple active backend instances, distribution across servers or regions, and
  automatic resumption of calculations are outside S16.
- Keep the implementation within existing project architecture and coding
  standards; this specification grants no standards waiver.

## Durability and consistency

- Confirm a mutation as successful only after its database transaction commits
  durably. All confirmed domain changes and saved scheduling results survive an
  application restart or crash.
- Persist schools and all their maintained domain data, relationships and saved
  planning state, rather than only a subset of the existing in-memory state.
- Related mutations either succeed completely or leave the previous state intact.
  This includes deleting a dancer and cleaning up memberships, deleting a group
  and cleaning up show references, deleting a school and its children, and
  replacing a school through import.
- Validate imports before publishing the replacement. Preserve the current
  import/export behavior and identity semantics.
- Enforce valid relationships through database constraints alongside application
  validation.

## Concurrent changes and scheduling

- Use one persistent revision per school for optimistic concurrency control.
- Mutations must identify the school revision on which they are based. Check
  that revision and commit the change and new revision atomically.
- Reject stale mutations with a clear conflict message; do not silently overwrite
  newer changes. The user can reload current data and decide again.
- Changes to different components of the same school may therefore conflict.
  Finer-grained conflict detection is outside S16.
- Retain the scheduling input revision check: a result computed from an outdated
  input revision must not replace current planning state.
- After restart, calculations interrupted by the restart must be recognizably
  interrupted, rather than remain indefinitely marked as running. They do not
  resume automatically. Previously saved results remain available.

## Database failures and schema changes

- Refuse mutations while the database is unavailable and provide a clear failure
  message. Do not acknowledge successful storage or fall back to volatile writes.
- Connection and Flyway migration failures at startup must be clearly visible;
  the application must not present itself as ready for normal use when required
  database initialization has failed.
- Maintain the database schema through versioned Flyway migrations.

## Transition from in-memory storage

Start with an empty database. Existing data is transferred through the current
JSON export/import path; no separate in-memory migration mechanism is required.
Document that existing data must be exported before stopping the old application
for the transition, because its current state exists only in memory.

## Deferred work

Database backup and restore are outside S16 and will be specified separately in
`specs/S17_database-backup-and-restore`. That specification is intentionally empty
for now. S16 does not promise recovery after loss of the database or its volume.

## Acceptance criteria

- [ ] PostgreSQL stores the complete maintained domain state and saved scheduling
      results, with Flyway managing the relational schema.
- [ ] Confirmed changes remain available after both a normal restart and an
      application crash, using the same persistent database.
- [ ] Failed related mutations and failed school replacement imports leave no
      partial changes or dangling references.
- [ ] Two edits based on the same school revision cannot silently overwrite one
      another: the stale edit receives a clear conflict response, and the user
      can reload and retry.
- [ ] Scheduling results based on outdated input revisions are rejected.
- [ ] Interrupted calculations are recognizably interrupted after restart;
      previously saved results remain available.
- [ ] Database unavailability prevents successful mutation acknowledgements;
      startup connection and migration failures are clearly reported.
- [ ] An empty database can be initialized through Flyway and populated through
      existing JSON import, preserving import/export behavior and identities.
- [ ] Setup and transition instructions cover the database service, persistent
      storage, connection configuration, migrations and export-before-transition.

## Implementation handoff

This specification records the agreed design and behavior. Database provisioning,
application changes and validation are subsequent implementation work.
