Postgres schema and migrations #2

New Issue

grenade · 2026-08-07T12:38:28Z

grenade commented

2026-08-07 12:38:28 +00:00

Part of #1.

Goal

Create the database schema tireless persists to, as sqlx migrations, plus the
decision about how queries are checked at compile time. crates/tireless-data/
declares the migrate feature and store.rs refers to a migrations/
directory that does not exist, so this is the first missing piece of stage 1.

Three tables, matching the domain types in tireless-entities: tracked repos,
jobs, and agent runs. The claim protocol (design.md §4.2) needs jobs to carry
claimed_by and claim_expires_at and to be indexed for the claim query;
enqueue idempotency (§4.4) needs a unique key on (forge, owner, repo, number).

One decision must be made here and stated in the PR, because it is expensive
to reverse. sqlx is configured with the macros feature, so query! checks
SQL at compile time against either a live DATABASE_URL or committed offline
metadata. CI has neither today. Either commit .sqlx offline data and add
cargo sqlx prepare --check to the workflow, or use runtime query_as and drop
the macros feature. Both are defensible; pick one, say why in the PR, and be
consistent.

Files

crates/tireless-data/migrations/0001_init.sql — new; the three tables, their
indexes and constraints
crates/tireless-data/src/store.rs — embed the migrator, run migrations on
startup
crates/tireless-data/Cargo.toml — adjust the sqlx feature set if the runtime
query path is chosen
.gitea/workflows/deploy.yaml — add the offline-data check, if that path is
chosen

Steps

Write 0001_init.sql covering tracked_repo, job and agent_run. Mirror
the field names in tireless_entities so the mapping stays obvious.
Add the unique constraint on (forge, owner, repo, number) that makes
enqueue an upsert.
Add an index supporting the claim query: pending jobs ordered by creation,
filtered by kind.
Store enums as text with a check constraint rather than as Postgres enums —
adding a JobKind variant should be a migration, not a type alteration that
locks the table.
Embed the migrator in PgStore and run it on connect.
Make the compile-time-checking decision and implement it consistently.

Acceptance

cargo test --workspace
cargo clippy --all-targets --all-features -- -D warnings
Migrations apply cleanly against an empty database, and applying them twice is
a no-op.
The PR states which sqlx query path was chosen and why.

Out of scope

The JobStore trait implementation — that is its own child, and it depends on
this one.
Any forge client work.
Reconciliation or lease-expiry logic; the columns are created here, the
behaviour lands in stage 2.
Do not add a password field to PgConfig/Database. Auth is ident-mapped
mTLS (design.md §4.1); a password appearing here means the ident mapping is
wrong and should be fixed there instead.

Part of #1. ## Goal Create the database schema tireless persists to, as sqlx migrations, plus the decision about how queries are checked at compile time. `crates/tireless-data/` declares the `migrate` feature and `store.rs` refers to a `migrations/` directory that does not exist, so this is the first missing piece of stage 1. Three tables, matching the domain types in `tireless-entities`: tracked repos, jobs, and agent runs. The claim protocol (design.md §4.2) needs jobs to carry `claimed_by` and `claim_expires_at` and to be indexed for the claim query; enqueue idempotency (§4.4) needs a unique key on `(forge, owner, repo, number)`. **One decision must be made here and stated in the PR, because it is expensive to reverse.** `sqlx` is configured with the `macros` feature, so `query!` checks SQL at compile time against either a live `DATABASE_URL` or committed offline metadata. CI has neither today. Either commit `.sqlx` offline data and add `cargo sqlx prepare --check` to the workflow, or use runtime `query_as` and drop the `macros` feature. Both are defensible; pick one, say why in the PR, and be consistent. ## Files - `crates/tireless-data/migrations/0001_init.sql` — new; the three tables, their indexes and constraints - `crates/tireless-data/src/store.rs` — embed the migrator, run migrations on startup - `crates/tireless-data/Cargo.toml` — adjust the sqlx feature set if the runtime query path is chosen - `.gitea/workflows/deploy.yaml` — add the offline-data check, if that path is chosen ## Steps 1. Write `0001_init.sql` covering `tracked_repo`, `job` and `agent_run`. Mirror the field names in `tireless_entities` so the mapping stays obvious. 2. Add the unique constraint on `(forge, owner, repo, number)` that makes enqueue an upsert. 3. Add an index supporting the claim query: pending jobs ordered by creation, filtered by kind. 4. Store enums as text with a check constraint rather than as Postgres enums — adding a `JobKind` variant should be a migration, not a type alteration that locks the table. 5. Embed the migrator in `PgStore` and run it on connect. 6. Make the compile-time-checking decision and implement it consistently. ## Acceptance - `cargo test --workspace` - `cargo clippy --all-targets --all-features -- -D warnings` - Migrations apply cleanly against an empty database, and applying them twice is a no-op. - The PR states which sqlx query path was chosen and why. ## Out of scope - The `JobStore` trait implementation — that is its own child, and it depends on this one. - Any forge client work. - Reconciliation or lease-expiry logic; the columns are created here, the behaviour lands in stage 2. - Do not add a password field to `PgConfig`/`Database`. Auth is ident-mapped mTLS (design.md §4.1); a password appearing here means the ident mapping is wrong and should be fixed there instead.

grenade added the tireless/implement label 2026-08-07 12:40:46 +00:00

grenade referenced this issue

2026-08-07 12:41:14 +00:00

Stage 1 — forge ingestion (read-only) #1

grenade referenced this issue

2026-08-07 14:07:34 +00:00

Verify the deploy workflow end to end, before there is anything to lose #9

grenade referenced this issue from a commit

2026-08-07 15:47:21 +00:00

feat(data): add the initial schema and migrations

grenade closed this issue

2026-08-07 15:47:21 +00:00

grenade commented

2026-08-07 15:47:42 +00:00

Done in 6545d19.

The decision this issue asked for

Runtime queries, not query!. No .sqlx offline metadata, no DATABASE_URL
needed to build, no cargo sqlx prepare --check in CI.

lairball — the other house project on this cluster — already does exactly
this: 19 uses of sqlx::query(, zero macros, no .sqlx directory, no Postgres
in CI. But the deciding argument is specific to tireless. Compile-time checking
makes a database a build dependency: regenerating .sqlx after any query
change needs a live Postgres. This crate is meant to be modified by a 27B model
working unattended (design.md §2.4), and a build that fails without a database
it cannot provision is a build that model cannot fix — its documented failure
mode being to improvise.

The cost is real and named in store.rs: a malformed query is caught by a test
against a real database rather than by cargo build. That is why the schema
tests below exist.

What the schema does that isn't obvious

The live-issue index is partial, and getting it wrong is silent in both
directions. A plain unique (forge, owner, repo, number) — which is what
"enqueue is an upsert keyed on …" suggests — would forbid re-running a terminal
job, and would forbid the discovery lane outright, since discovery recurs
against one tracking issue on a cooldown (§2.6). Partial on the non-terminal
states gives at most one live job per issue plus unlimited history.

The claim index orders by created_at alone, leaving kind as a filter.
Leading with kind — which is what this issue's step 3 proposed — makes the
planner sort every pending row on every claim, because LIMIT 1 can otherwise
stop at the first match:

index	buffers	plan
`(kind, created_at)`	720	Bitmap scan → Sort 2503 rows
`(created_at)`	4	Index scan, stops at row 1

The gap grows with the backlog rather than staying fixed. INCLUDE (kind) was
measured too and dropped: FOR UPDATE visits the heap regardless.

A spelling that would have been permanent. rename_all = "snake_case" turns
Forge::GitHub into git_hub — in the database, the JSON API and the
generated TypeScript. Renamed to github while nothing is persisted and GitHub
support is still disabled. There is a test.

Check constraints are tested against the domain enums. Adding a JobKind
variant without a migration would otherwise fail as a constraint violation on
the first job of that kind — in production, unattended. Now it fails cargo test.

Verified, not assumed

Against Postgres 18 (same major as the house cluster):

migrations apply to an empty database, and a second run is a no-op — which
matters because all three units migrate on start and a deploy restarts them
together;
all eight constraints reject what they should and admit what they should,
including git_hub now being rejected;
two concurrent claimers of one pending job produce exactly one winner, the
loser skipping rather than blocking — the property §4.2 rests on.

The live tests are #[ignore]d rather than skipping on a missing variable, so a
green cargo test never implies the schema was exercised. CLAUDE.md says how
to run them, and that an applied migration must never be edited — sqlx keeps a
checksum per version, so editing one makes every deployed database refuse to
start.

Left alone

No JobStore impl (that is #3), no forge work, no reconciliation or lease-expiry
behaviour — the columns exist, including labels_synced_at for the poller
split in §6.4, but nothing reads them yet. No password field went near
Database.

Done in `6545d19`. ## The decision this issue asked for **Runtime queries, not `query!`.** No `.sqlx` offline metadata, no `DATABASE_URL` needed to build, no `cargo sqlx prepare --check` in CI. `lairball` — the other house project on this cluster — already does exactly this: 19 uses of `sqlx::query(`, zero macros, no `.sqlx` directory, no Postgres in CI. But the deciding argument is specific to tireless. Compile-time checking makes a database a *build dependency*: regenerating `.sqlx` after any query change needs a live Postgres. This crate is meant to be modified by a 27B model working unattended (design.md §2.4), and a build that fails without a database it cannot provision is a build that model cannot fix — its documented failure mode being to improvise. The cost is real and named in `store.rs`: a malformed query is caught by a test against a real database rather than by `cargo build`. That is why the schema tests below exist. ## What the schema does that isn't obvious **The live-issue index is partial**, and getting it wrong is silent in both directions. A plain `unique (forge, owner, repo, number)` — which is what "enqueue is an upsert keyed on …" suggests — would forbid re-running a terminal job, and would forbid the discovery lane outright, since discovery recurs against one tracking issue on a cooldown (§2.6). Partial on the non-terminal states gives at most one *live* job per issue plus unlimited history. **The claim index orders by `created_at` alone**, leaving `kind` as a filter. Leading with `kind` — which is what this issue's step 3 proposed — makes the planner sort every pending row on every claim, because `LIMIT 1` can otherwise stop at the first match: | index | buffers | plan | | --- | --- | --- | | `(kind, created_at)` | 720 | Bitmap scan → **Sort** 2503 rows | | `(created_at)` | **4** | Index scan, stops at row 1 | The gap grows with the backlog rather than staying fixed. `INCLUDE (kind)` was measured too and dropped: `FOR UPDATE` visits the heap regardless. **A spelling that would have been permanent.** `rename_all = "snake_case"` turns `Forge::GitHub` into **`git_hub`** — in the database, the JSON API and the generated TypeScript. Renamed to `github` while nothing is persisted and GitHub support is still disabled. There is a test. **Check constraints are tested against the domain enums.** Adding a `JobKind` variant without a migration would otherwise fail as a constraint violation on the first job of that kind — in production, unattended. Now it fails `cargo test`. ## Verified, not assumed Against Postgres 18 (same major as the house cluster): - migrations apply to an empty database, and a second run is a no-op — which matters because all three units migrate on start and a deploy restarts them together; - all eight constraints reject what they should and admit what they should, including `git_hub` now being rejected; - two concurrent claimers of one pending job produce exactly one winner, the loser skipping rather than blocking — the property §4.2 rests on. The live tests are `#[ignore]`d rather than skipping on a missing variable, so a green `cargo test` never implies the schema was exercised. `CLAUDE.md` says how to run them, and that an applied migration must never be edited — sqlx keeps a checksum per version, so editing one makes every deployed database refuse to start. ## Left alone No `JobStore` impl (that is #3), no forge work, no reconciliation or lease-expiry *behaviour* — the columns exist, including `labels_synced_at` for the poller split in §6.4, but nothing reads them yet. No password field went near `Database`.

grenade referenced this issue from a commit

2026-08-07 17:20:13 +00:00

feat(data): implement JobStore and the Gitea read client

grenade referenced this issue

2026-08-07 17:20:32 +00:00

PgStore: implement JobStore with FOR UPDATE SKIP LOCKED #3

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: lair/tireless#2