patrick/Polaris

Watch

Fork 0

PH1.1 — Schema 0002: master data tables and typed mapped classes #9

New issue

Closed

opened 2026-09-11 09:02:09 +00:00 by bart · 2 comments

bart commented

2026-09-11 09:02:09 +00:00

Collaborator

Parent

Row PH1.1 in docs/12-implementation-plan.md (branch, owned paths and doc section are defined there).

What to build

Migration 0002 creates every master-data table from docs/08-data-model.md (vehicle, ingest_record, site, presence_block, presence_window, catalogue tables, site_suitability, job, person with preferred_username, crew, crew_member, crew_skill, shift) with all indexes, soft-delete columns and geography(…, 4326) geometry, and the matching typed SQLAlchemy mapped classes.

Acceptance criteria

alembic upgrade head then downgrade -1 round-trips cleanly on a real Postgres (testcontainers)
Every table, column, constraint and index in docs/08-data-model.md for these aggregates exists in the migration
Mapped classes are fully typed and pass mypy --strict; catalogue values are table rows, not enums
Soft-delete (deleted_at) present on all master data; no hard-delete path

Blocked by

#4 — API skeleton: core, router discovery, healthz, db + audit hook, alembic 0001, CLI

## Parent Row **PH1.1** in `docs/12-implementation-plan.md` (branch, owned paths and doc section are defined there). ## What to build Migration `0002` creates every master-data table from docs/08-data-model.md (vehicle, ingest_record, site, presence_block, presence_window, catalogue tables, site_suitability, job, person with `preferred_username`, crew, crew_member, crew_skill, shift) with all indexes, soft-delete columns and `geography(…, 4326)` geometry, and the matching typed SQLAlchemy mapped classes. ## Acceptance criteria - [x] `alembic upgrade head` then `downgrade -1` round-trips cleanly on a real Postgres (testcontainers) - [x] Every table, column, constraint and index in docs/08-data-model.md for these aggregates exists in the migration - [x] Mapped classes are fully typed and pass `mypy --strict`; catalogue values are table rows, not enums - [x] Soft-delete (`deleted_at`) present on all master data; no hard-delete path ## Blocked by - #4 — API skeleton: core, router discovery, healthz, db + audit hook, alembic 0001, CLI

bart added the

ready-for-agent

label

2026-09-11 09:02:09 +00:00

bart added a new dependency

2026-09-11 09:02:22 +00:00

#4 PH0.2 — API skeleton: core, router discovery, healthz, db + audit hook, alembic 0001, CLI

bart referenced this issue

2026-09-11 09:02:22 +00:00

PH1.2 — Schema 0003: planning tables and typed mapped classes #10

bart added a new dependency

2026-09-11 09:02:22 +00:00

#10 PH1.2 — Schema 0003: planning tables and typed mapped classes

patrick referenced this issue

2026-09-11 09:27:09 +00:00

docs: remove tooling as a catalogue entity #48

bart was assigned by patrick

2026-09-11 10:35:13 +00:00

patrick commented

2026-09-11 10:35:23 +00:00

Owner

Starting on this. Branch feature/db-schema-master-data, branched from origin/develop (not main — main does not yet contain backend/; #4/#51 API-skeleton only merged into develop so far). Owning backend/alembic/versions/** and backend/src/polaris/db/models/** only, per CLAUDE.md.

Note: backend/tests/conftest.py and backend/tests/integration/db/test_migrations.py currently pin postgres:16-alpine for the testcontainers fixture; per conftest.py's own header comment ("Geography-typed tables ... will need to switch this fixture's image"), this is the first ticket to introduce geography columns, so I'll switch those two fixtures to a PostGIS-enabled image as part of this PR rather than opening a separate seam request, since the header explicitly anticipates this. Will call it out clearly in the PR.

Starting on this. Branch `feature/db-schema-master-data`, branched from `origin/develop` (not `main` — `main` does not yet contain `backend/`; #4/#51 API-skeleton only merged into `develop` so far). Owning `backend/alembic/versions/**` and `backend/src/polaris/db/models/**` only, per CLAUDE.md. Note: `backend/tests/conftest.py` and `backend/tests/integration/db/test_migrations.py` currently pin `postgres:16-alpine` for the testcontainers fixture; per `conftest.py`'s own header comment ("Geography-typed tables ... will need to switch this fixture's image"), this is the first ticket to introduce `geography` columns, so I'll switch those two fixtures to a PostGIS-enabled image as part of this PR rather than opening a separate seam request, since the header explicitly anticipates this. Will call it out clearly in the PR.

patrick referenced this issue

2026-09-11 10:51:46 +00:00

seam: Auditable.audit_entity_id / before_flush hook broken for server-generated (identity) PKs #57

patrick referenced this issue from a commit

2026-09-11 10:52:36 +00:00

fix(db): make Auditable audit hook work with identity PKs

patrick referenced this issue from a commit

2026-09-11 10:52:36 +00:00

feat(db): schema 0002 master data tables

patrick referenced this issue from a commit

2026-09-11 10:53:45 +00:00

fix(db): make Auditable audit hook work with identity PKs

patrick referenced this issue from a commit

2026-09-11 10:53:45 +00:00

feat(db): schema 0002 master data tables

patrick referenced this issue from a pull request that will close it,

2026-09-11 10:53:56 +00:00

feat(db): schema 0002 master data tables #58

patrick commented

2026-09-11 10:54:08 +00:00

Owner

Done — PR #58 (feature/db-schema-master-data → main): #58

All four acceptance criteria ticked above. mypy --strict, ruff, and the full backend test suite (35 tests, incl. alembic upgrade head / downgrade -1 / upgrade +1 against real Postgres+PostGIS via testcontainers) are green.

Two notes for other sessions:

Branched from origin/develop originally (main had no backend/ yet at the time); rebased onto main once develop was retired mid-session — PR now targets main.
Discovered and fixed a real bug in polaris/db/audit.py (outside this ticket's owned paths) blocking every future Auditable class with a server-generated identity PK — see seam-request issue #57 for the full writeup. db/audit.py on main after this merges has that fix; rebase onto it if you're touching Auditable/audit_entity_id.

Done — PR #58 (`feature/db-schema-master-data` → `main`): https://git.xor.nu/patrick/Polaris/pulls/58 All four acceptance criteria ticked above. mypy --strict, ruff, and the full backend test suite (35 tests, incl. `alembic upgrade head` / `downgrade -1` / `upgrade +1` against real Postgres+PostGIS via testcontainers) are green. Two notes for other sessions: - Branched from `origin/develop` originally (main had no `backend/` yet at the time); rebased onto `main` once `develop` was retired mid-session — PR now targets `main`. - Discovered and fixed a real bug in `polaris/db/audit.py` (outside this ticket's owned paths) blocking every future `Auditable` class with a server-generated identity PK — see seam-request issue #57 for the full writeup. `db/audit.py` on `main` after this merges has that fix; rebase onto it if you're touching `Auditable`/`audit_entity_id`.

patrick closed this issue

2026-09-11 11:12:52 +00:00

bart referenced this issue

2026-09-11 11:21:57 +00:00

fix: worker never actually ran end-to-end against compose #64

bart referenced this issue from a commit

2026-09-11 11:21:59 +00:00

fix(db): use task_status enum values, not member names

bart was unassigned by patrick

2026-09-11 11:44:44 +00:00

patrick self-assigned this

2026-09-11 11:44:44 +00:00