Declare FKs on the remaining plain-Integer references (handoffs.created_by, dispatches.received_handoff_id/created_handoff_id)

jared commented

2026-08-21 19:12:00 +00:00

Owner

Slice

Declare the three remaining plain-Integer references in plugins/os-sdlc/lib/os_sdlc/runner/db.rb as foreign keys and bump SCHEMA_VERSION from 18 to 19.

Acceptance criteria

Given the schema in db.rb, when the handoffs table is created, then created_by is declared with t.foreign_key :created_by, :agents (replacing t.Integer at line 260).
Given the schema in db.rb, when the dispatches table is created, then received_handoff_id and created_handoff_id are declared with t.foreign_key ..., :handoffs (replacing t.Integer at lines 295–296).
Given the change, when db.rb is loaded, then SCHEMA_VERSION is 19.
Given a fresh database created by recreate, when PRAGMA foreign_key_list is queried on handoffs and dispatches, then all three FKs are present.
Given the full test suite, when it runs, then it is green.

Non-goals

No incremental migration: ADR-0129 ships schema changes by wipe-and-recreate on version mismatch.
No change to handoff claiming, dispatch settlement, or any runtime behavior.
No FK audit beyond these three columns.

Notes for implementer

FK declaration pattern is on db.rb lines 293–294 (t.foreign_key :implementation_id, :implementations).
Governing ADRs: ADR-0135 (handoff contract; created_by FK is part of the decided contract), ADR-0129 (disposable DB, SCHEMA_VERSION bump), ADR-0155 (stale-schema wipe refuses while an implementation is in flight — the bump takes effect at the next idle load).
FK enforcement is globally on since #457; only declarations plus the version bump are needed.

Context

The #457 contract-auditor pass verified the D04 scope but noted three references still plain Integer while siblings gained FKs: handoffs.created_by → agents (joined in handoff.rb:17), dispatches.received_handoff_id / created_handoff_id → handoffs (db.rb:201-202; now 260 and 295–296 at current HEAD). Enforcement is globally on since #457, so this is a schema-only change plus SCHEMA_VERSION bump.

Skeptic verdict: CREATE — deliberate D04 scope boundary, real named gap with a stated fix path, root ticket itself.

Origin

Trigger: contract-auditor handoff during the #467 drive (2026-08-21).
Improvised this session: none.
Chain: unenforced references ← D04 scope boundary ← DESIGN (docs/os-sdlc-rebuild/poodr-map-decisions.md D04).
Root candidate: this ticket.

Related: #457 (FK enforcement), #473 / ADR-0155 (wipe guard).

## Slice Declare the three remaining plain-Integer references in `plugins/os-sdlc/lib/os_sdlc/runner/db.rb` as foreign keys and bump `SCHEMA_VERSION` from 18 to 19. ## Acceptance criteria - [ ] Given the schema in db.rb, when the `handoffs` table is created, then `created_by` is declared with `t.foreign_key :created_by, :agents` (replacing `t.Integer` at line 260). - [ ] Given the schema in db.rb, when the `dispatches` table is created, then `received_handoff_id` and `created_handoff_id` are declared with `t.foreign_key ..., :handoffs` (replacing `t.Integer` at lines 295–296). - [ ] Given the change, when db.rb is loaded, then `SCHEMA_VERSION` is 19. - [ ] Given a fresh database created by `recreate`, when `PRAGMA foreign_key_list` is queried on `handoffs` and `dispatches`, then all three FKs are present. - [ ] Given the full test suite, when it runs, then it is green. ## Non-goals - No incremental migration: ADR-0129 ships schema changes by wipe-and-recreate on version mismatch. - No change to handoff claiming, dispatch settlement, or any runtime behavior. - No FK audit beyond these three columns. ## Notes for implementer - FK declaration pattern is on db.rb lines 293–294 (`t.foreign_key :implementation_id, :implementations`). - Governing ADRs: ADR-0135 (handoff contract; `created_by` FK is part of the decided contract), ADR-0129 (disposable DB, SCHEMA_VERSION bump), ADR-0155 (stale-schema wipe refuses while an implementation is in flight — the bump takes effect at the next idle load). - FK enforcement is globally on since #457; only declarations plus the version bump are needed. ## Context The #457 contract-auditor pass verified the D04 scope but noted three references still plain Integer while siblings gained FKs: handoffs.created_by → agents (joined in handoff.rb:17), dispatches.received_handoff_id / created_handoff_id → handoffs (db.rb:201-202; now 260 and 295–296 at current HEAD). Enforcement is globally on since #457, so this is a schema-only change plus SCHEMA_VERSION bump. Skeptic verdict: CREATE — deliberate D04 scope boundary, real named gap with a stated fix path, root ticket itself. ## Origin - Trigger: contract-auditor handoff during the #467 drive (2026-08-21). - Improvised this session: none. - Chain: unenforced references ← D04 scope boundary ← DESIGN (docs/os-sdlc-rebuild/poodr-map-decisions.md D04). - Root candidate: this ticket. Related: #457 (FK enforcement), #473 / ADR-0155 (wipe guard).

jared added the

plugin/os-sdlc

label

2026-08-21 19:12:00 +00:00

jared referenced this issue

2026-08-21 19:12:00 +00:00

Add the missing FK constraint flagged in the #447 review #457

jared referenced this issue

2026-08-21 19:14:23 +00:00

POODR map T9: supervised end-to-end drive of the POODR map #467

jared referenced this issue

2026-08-21 19:18:46 +00:00

POODR implementation map — build wayfinder #458

jared referenced this issue

2026-08-21 20:15:16 +00:00

POODR implementation map — build wayfinder #458

jared commented

2026-08-23 12:37:03 +00:00

Author

Owner

Body corrected 2026-08-23 from code-probe verification (agent a7c06ed2d895621b1, claude-sonnet-5).

Change log:

Corrected db.rb line citation for dispatches.received_handoff_id / created_handoff_id from ~192-193 to 201-202, matching current HEAD.

Reason: pure line-drift from later edits to db.rb; the described gap (both columns remain plain Integer with no foreign_key declaration) is confirmed unchanged.

Body corrected 2026-08-23 from code-probe verification (agent a7c06ed2d895621b1, claude-sonnet-5). Change log: - Corrected db.rb line citation for dispatches.received_handoff_id / created_handoff_id from ~192-193 to 201-202, matching current HEAD. Reason: pure line-drift from later edits to db.rb; the described gap (both columns remain plain Integer with no foreign_key declaration) is confirmed unchanged.

jared commented

2026-08-27 01:16:34 +00:00

Author

Owner

This was generated by AI during triage.

Refined to ticket tier during triage: added Slice, Acceptance criteria, Non-goals, and implementer notes; verified the gap at current HEAD (db.rb lines 260, 295–296; SCHEMA_VERSION 18) and linked governing ADRs 0129, 0135, 0155. No open decisions, so no decision children.

> *This was generated by AI during triage.* Refined to ticket tier during triage: added Slice, Acceptance criteria, Non-goals, and implementer notes; verified the gap at current HEAD (db.rb lines 260, 295–296; SCHEMA_VERSION 18) and linked governing ADRs 0129, 0135, 0155. No open decisions, so no decision children.

jared added the

enhancement

ready-for-agent

labels

2026-08-27 01:16:44 +00:00

jared commented

2026-08-27 01:18:07 +00:00

Author

Owner

This was generated by AI during triage.

Work started on branch worktree-ticket-471 via the os-sdlc pipeline.

> *This was generated by AI during triage.* Work started on branch worktree-ticket-471 via the os-sdlc pipeline.

jared commented

2026-08-27 01:34:33 +00:00

Author

Owner

Resolution

Done: Declared FKs on handoffs.created_by -> agents and dispatches.received_handoff_id/created_handoff_id -> handoffs; bumped SCHEMA_VERSION to 19.

Evidence: Branch worktree-ticket-471 commit 728e8d2 via os-sdlc pipeline (implementation 1, all gates green). Suite: 1221 runs, 0 failures; 3 new orphan-rejection tests in db_foreign_key_test.rb. Conforms to ADR-0129/0135/0155.

Follow-ups: none

## Resolution **Done:** Declared FKs on handoffs.created_by -> agents and dispatches.received_handoff_id/created_handoff_id -> handoffs; bumped SCHEMA_VERSION to 19. **Evidence:** Branch worktree-ticket-471 commit 728e8d2 via os-sdlc pipeline (implementation 1, all gates green). Suite: 1221 runs, 0 failures; 3 new orphan-rejection tests in db_foreign_key_test.rb. Conforms to ADR-0129/0135/0155. **Follow-ups:** none

jared closed this issue

2026-08-27 01:34:33 +00:00

jared referenced this issue from a commit

2026-08-27 13:26:25 +00:00

#471: declare FKs on handoffs.created_by and dispatches handoff references

jared referenced this issue from a commit

2026-08-27 13:26:25 +00:00

docs(os-sdlc): status row for #471 FK declarations