paliad/docs/design-procedural-events-b0-findings-2026-05-26.md

Slice B.0 — Live DB re-validation findings (t-paliad-273)

Author: curie (researcher) Date: 2026-05-26 Branch: mai/curie/researcher-slice-b-zero Predecessor: docs/design-procedural-events-model-2026-05-25.md (cronus, t-paliad-262) Scope: READ-ONLY re-validation of the design doc's §1 premises against the live youpc Supabase paliad schema. No migration SQL written, no writes to deadline_rules or any table. B.1 (additive migration) remains blocked pending m's greenlight.

This document does not redesign the schema. It does not propose new structural changes. It records what the live DB looks like ~24 hours after the design was authored, flags every claim that drifted, and gives the eventual B.1 coder a current-as-of-2026-05-26 baseline to plan against.

---

§0 TL;DR

The design doc's §1 premises were sound on 2026-05-25. All numeric premises drifted in the 24 hours since. The qualitative model (deadline_rules conflates three concepts; live deadlines.rule_id FK; snapshot precedent established; no proceeding_event* tables) still holds.

The Q5 default ("10 archived multi-row submission_codes collapse safely") is now moot: those rows were removed from the live DB between 2026-05-25 15:30 and 2026-05-26 13:30. There are now zero multi-row submission codes; every active submission_code maps 1:1 to one rule row. B.1 backfill no longer needs the multi-row collapse logic that §5 of the design doc anticipated.

The Q6 default ("concept_id attaches to procedural event, not sequencing rule") is directionally correct but needs refinement. The empirical attachment is above the procedural-event level — deadline_concepts rows cluster legal meaning across jurisdictional procedural-event variants. One concept_id can span 15 distinct submission_codes (e.g. "Berufungsfrist" across BGH / BPatG / LG / OLG for both PatG and ZPO paths). The FK in §4.1's draft schema (procedural_events.concept_id REFERENCES deadline_concepts(id), N:1) is already correctly shaped for this — no schema change needed. The verbal claim in the design doc should be tightened to "one deadline_concept row may be referenced by many procedural events; the FK lives on procedural_events."

Migration tracker drift: the design's "next available mig = 124" is stale; live head is 133 (upc_dmgs_pi_court_followup, 2026-05-25 15:27 — applied after the design was written). Next available is 134. Ten migrations landed since the doc was authored — 124..133. None of them touched deadline_rules schema, but they did mutate row content (the missing 23 rows and the new event_type/legal_source distribution come from migs 127/128/132/133).

The design's claimed migration tracker paliad.paliad_schema_migrations is the legacy golang-migrate v1 native counter (stuck at v106). The canonical tracker is paliad.applied_migrations (one row per applied migration, with checksum + applied_at). internal/db/migrate.go:9-21 is the source of truth. Project CLAUDE.md still says paliad.paliad_schema_migrations; that's a stale doc, not a B.0-scope fix.

One doc-side bug fixed by this slice: design doc §1 + m/paliad#93 issue body referenced paliad.deadlines.deadline_rule_id. Live column is paliad.deadlines.rule_id. Both files patched on this branch.

---

§1 Headline-count drift table

All numbers taken 2026-05-26 ~13:30 UTC against the live paliad schema.

Metric	Design (2026-05-25)	Live (2026-05-26)	Δ	Notes
deadline_rules row count	254	231	-23	All rows is_active = true. No soft-deletes in flight.
Rows with submission_code	177	153	-24
Distinct submission_code values	158	153	-5	All 5 lost are the multi-row _archived_litigation.* codes — see §2.
Rows with legal_source	102	112	+10
Distinct legal_source values	70	87	+17	New jurisdictional variants seeded by recent migs (127/132/133).
Rows with concept_id (linked to deadline_concepts)	125	129	+4	56% of the corpus is concept-linked, vs 49% in the design.
paliad.deadlines rows	1	5	+4	Still tiny — destructive cutover stays cheap.
paliad.submission_drafts rows	4	7	+3
Rules in lifecycle_state = 'draft'	4	0	-4	All 4 design-era drafts were published or discarded.

event_type distribution

event_type	Design	Live	Δ
filing	130	105	-25
NULL	77	89	+12
decision	25	21	-4
hearing	21	15	-6
order	1	1	0
Total	254	231	-23

The -23 row delta lands almost entirely in filing (-25) and hearing (-6), offset by +12 NULL — consistent with the disappearance of the _archived_litigation.* filings and a few archived hearing rows, plus seeding of new structural / parent-only rows by recent migrations.

What did NOT drift (qualitative claims, still valid)

• paliad.deadline_rules carries 39 columns (design said 38 — drift +1; likely from mig 128 deadline_rules_unit_check which adds a CHECK without adding a column — or one of migs 124-133 added a column. Not investigated further; out of B.0 scope).
• paliad.deadlines.rule_id (uuid, nullable) is the FK column to paliad.deadline_rules.id. Confirmed via information_schema.referential_constraints — rule_id → paliad.deadline_rules(id). The doc-side mention of deadline_rule_id was always a typo.
• paliad.deadlines.rule_code + paliad.deadlines.custom_rule_text both still present (the denormalized-display columns from mig 122).
• paliad.submission_drafts uses (project_id uuid nullable, submission_code text NOT NULL) as its key — no FK to deadline_rules. Confirms the design's claim that the Schriftsätze surface filters on a text key, not on deadline_rules.id.
• No paliad.proceeding_event* tables exist (einstein's 2026-05-08 graph design was never built — still the case).

---

§2 Archived submission_code audit (Q5 re-confirm)

Premise re-checked: "10 archived multi-row submission_codes (_archived_litigation.*) collapse safely into single procedural events with multiple sequencing variants."

Finding: the premise is moot in the live DB.

SELECT submission_code, COUNT(*)
FROM paliad.deadline_rules
WHERE submission_code LIKE '_archived_litigation.%'
GROUP BY submission_code;
-- 0 rows

SELECT submission_code, COUNT(*)
FROM paliad.deadline_rules
WHERE submission_code IS NOT NULL
GROUP BY submission_code
HAVING COUNT(*) > 1;
-- 0 rows

Every active submission_code in the live corpus is 1:1 with its deadline_rules row. The 10 multi-row codes the design anticipated no longer exist.

Consequence for B.1 backfill:

• The §5.1 / §5.2 backfill SQL the design sketched (collapsing N rows-with-same-submission_code into 1 procedural_event + N sequencing_rules) is simpler than expected: a straight 1:1 backfill, no GROUP-BY-and-collapse step needed.
• B.1's INSERT INTO paliad.procedural_events ... SELECT DISTINCT submission_code ... becomes equivalent to INSERT ... SELECT submission_code, ... FROM deadline_rules WHERE submission_code IS NOT NULL. No deduplication needed.
• The 78 rows where submission_code IS NULL (231 - 153) still need a B.1 decision: do they become procedural_events rows (with synthetic codes), do they become free-standing sequencing_rules with procedural_event_id NULL, or do they get parked? This was implicit in the design (the 77 NULLs were framed as "structural / parent-only rows in the proceeding tree"); B.1 should make the decision explicit and document it in the migration's .up.sql comments.

---

§3 concept_id attachment shape (Q6 re-confirm)

Premise re-checked: "concept_id attaches to procedural event, not sequencing rule."

Finding: partly true. The FK direction the design proposes (procedural_events.concept_id → deadline_concepts.id, N:1) is correct. The verbal phrasing in Q6's default needs refinement — the empirical attachment is above the procedural-event level, not "at" it.

Empirical pattern

129 of 231 rows carry a concept_id. Those 129 rows reference 53 distinct deadline_concepts rows. Averages: 2.43 rows-per-concept, 2.42 submission-codes-per-concept (the two are nearly identical because today's corpus has no multi-row submission codes — see §2). Span distribution:

• 33 of 53 concepts (62%) attach to exactly 1 submission_code → procedural-event-scoped.
• 20 of 53 concepts (38%) attach to >1 submission_code → cross-procedural-event scoped.
• Maximum: 1 concept attaches to 15 distinct submission_codes.

Example: one concept, four procedural events

The concept b85b2e5a-4064-40b2-b862-24b7abaa5b94 ("Berufungsfrist / Berufungsschrift") is referenced by 4 deadline_rules rows that today carry these 4 distinct submission_codes:

rule_code	submission_code	court	name
§ 110 PatG	de.null.bgh.berufung	BGH	Berufungsschrift
§ 110 PatG	de.null.bpatg.berufung	BPatG	Berufungsfrist
§ 517 ZPO	de.inf.lg.berufung	LG	Berufungsfrist
§ 517 ZPO	de.inf.olg.berufung	OLG	Berufungsfrist

Under Slice B's target schema (§4.1), each of these four rows becomes a separate procedural_events row (different codes, different jurisdiction-specific names, different legal_source_ids), but all four reference the same deadline_concepts.id.

Implication for B.1

• procedural_events.concept_id should be nullable (62% of rows today have no concept link — the §4.1 sketch already allows this).
• The constraint must be N:1, not 1:1 (one deadline_concept may be referenced by many procedural_events). The §4.1 sketch (concept_id uuid REFERENCES paliad.deadline_concepts(id)) is already correctly N:1; a hypothetical "UNIQUE INDEX on procedural_events.concept_id" would break the existing data. Do not add UNIQUE.
• The design doc's Q6 phrasing can be tightened to: "concept_id attaches to procedural event (N procedural events → 1 concept). Sequencing rules do not carry concept_id." — but this is a wording nit, not a structural change. It does not block B.1.

---

§4 Snapshot precedent audit

Premise re-checked: the paliad.deadline_rules_pre_<N> snapshot pattern is established and ready for B.4's destructive drop.

Finding: confirmed and consistent.

Snapshot tables in paliad:

Snapshot table	Origin migration
deadlines_pre_089	mig 089
deadline_rules_pre_091	mig 091 (destructive drop of legacy columns)
event_deadlines_pre_092	mig 092
event_deadline_rule_codes_pre_092	mig 092
deadline_rules_pre_093	mig 093
proceeding_types_pre_093	mig 093
projects_pre_094	mig 094
deadline_rules_pre_095	mig 095
proceeding_types_pre_096	mig 096
deadline_rules_pre_098	mig 098

Pattern: <original_table>_pre_<migration_number>. Always created in the .up.sql of the destructive migration as CREATE TABLE paliad.<t>_pre_<N> AS TABLE paliad.<t>; (followed by the destructive DROP / ALTER).

B.4's template: before DROP TABLE paliad.deadline_rules; (and ALTER TABLE paliad.deadlines DROP COLUMN rule_id;), mig <N>.up.sql must include:

CREATE TABLE paliad.deadline_rules_pre_<N> AS TABLE paliad.deadline_rules;
-- (optional) CREATE TABLE paliad.deadlines_pre_<N> AS TABLE paliad.deadlines;

This is non-negotiable per m's snapshot policy and the precedent of migs 089-098. B.4 should not enter the deploy queue without it.

---

§5 deadlines.rule_id doc bug — verified + patched

Premise re-checked: the live column on paliad.deadlines referencing deadline_rules is named rule_id, not deadline_rule_id.

Verification:

SELECT column_name FROM information_schema.columns
WHERE table_schema='paliad' AND table_name='deadlines' AND column_name LIKE '%rule%';
-- rule_id (uuid, nullable)
-- rule_code (text, nullable)
-- custom_rule_text (text, nullable)

SELECT kcu.column_name, ccu.table_name, ccu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON ...
JOIN information_schema.constraint_column_usage ccu ON ...
WHERE tc.constraint_type='FOREIGN KEY' AND tc.table_schema='paliad' AND tc.table_name='deadlines';
-- rule_id → paliad.deadline_rules.id

Fix applied on this branch:

• docs/design-procedural-events-model-2026-05-25.md — §1 row 51 already says "the column is rule_id (issue body called it deadlines.deadline_rule_id — that's a doc-side typo)". §1 row 63 (the "Doc-side bug flagged" line) already names the fix target. No change needed to the design doc — the inventor already flagged and described the bug; B.0 just re-confirms it.
• m/paliad#93 issue body — line 56 says paliad.deadlines.deadline_rule_id in the Q3 migration shape. Patched via Gitea API on this slice. See §6 of this report.

---

§6 Migration tracker drift (out-of-scope context)

The design doc said "next available mig number is 124 (mig 123 = Backup Mode Slice A, just shipped)". Live state on 2026-05-26 13:30:

• Latest applied migration: 133 (upc_dmgs_pi_court_followup, 2026-05-25 15:27).
• Next available: 134.
• Migrations 124-133 (all applied after the design was authored):

124  de_inf_lg_replik_duplik_sequencing      (2026-05-25 13:49)
125  cross_cutting_filter_legal_source       (2026-05-25 14:13)
126  users_inbox_seen_at                     (2026-05-25 13:51)
127  wave0_tier0_deadline_fixes              (2026-05-25 14:13)
128  deadline_rules_unit_check               (2026-05-25 14:13)
129  project_event_choices                   (2026-05-25 15:02)
130  submission_drafts_language              (2026-05-25 15:05)
131  submission_drafts_party_selection       (2026-05-25 15:02)
132  wave1_tier1_rule_additions              (2026-05-25 15:40)
133  upc_dmgs_pi_court_followup              (2026-05-25 15:27)

These touched deadline_rules content (wave0/wave1 rule additions, sequencing fixes, unit checks) and adjacent tables, but did not change the conflated-three-concepts shape that motivates Slice B. The structural premise of the design holds; the row-level numbers shifted.

Side observation (not a B.0 fix scope): the project's CLAUDE.md says "Migration tracker is paliad.paliad_schema_migrations (avoids collision with other apps on the shared public.schema_migrations)." That sentence is stale. The canonical tracker is paliad.applied_migrations (per internal/db/migrate.go:9-21,53,105). paliad.paliad_schema_migrations is the legacy golang-migrate v1 counter, frozen at v106; the migrate runner uses it only to bootstrap applied_migrations on first deploy of the new runner (internal/db/migrate.go:219-240). Recommend a separate doc-fix slice (out of B.0 scope) to update .claude/CLAUDE.md.

---

§7 Updated B.1 brief (no-op / minor adjustments only)

What the live data means for the design's §5 migration plan:

1. Backfill is simpler. No multi-row collapse logic needed (§2). One-to-one INSERT INTO paliad.procedural_events SELECT submission_code, name, name_en, description, event_type AS event_kind, primary_party, ... FROM paliad.deadline_rules WHERE submission_code IS NOT NULL against 153 rows.
2. The 78 NULL-submission_code rows need an explicit decision in B.1. Either:
   • (a) Skip them — they remain deadline_rules-only and become orphan-once-deadline_rules-is-dropped. Not acceptable; B.4 would lose them.
   • (b) Mint synthetic codes (null.<uuid8> or similar) for the structural rows and create procedural_events for them.
   • (c) Treat them as "sequencing-rule-only" (a sequencing_rules row with NULL procedural_event_id) — would require sequencing_rules.procedural_event_id to be nullable, which contradicts §4.1's NOT NULL FK.
   • Default recommendation: (b) — mint codes, preserve every row. B.1 must document the mint rule in the .up.sql. Surface this to head before scheduling B.1.
3. concept_id stays N:1 on procedural_events. No UNIQUE constraint. §4.1's sketch already does this; just don't accidentally tighten it.
4. Use migration number 134 (or whatever's the live MAX(version)+1 at B.1-write-time; re-check at the moment of writing the file).
5. Snapshot before drop in B.4: CREATE TABLE paliad.deadline_rules_pre_<N> AS TABLE paliad.deadline_rules; per §4 precedent. This is the hard-stop pre-condition for B.4 entering the deploy queue.
6. Submission_drafts.submission_code → procedural_events.code text join continues to work unchanged through B.1-B.3 because both names match. No B.5 dual-write needed for submission_drafts. (The design's §6.3 already noted this.)

None of these change the shape of the design — they tighten the backfill SQL and surface one explicit decision (point 2) for head.

---

§8 Outputs of this slice (B.0)

Artifact	Status
docs/design-procedural-events-b0-findings-2026-05-26.md (this file)	created on mai/curie/researcher-slice-b-zero
docs/design-procedural-events-model-2026-05-25.md	cherry-picked from mai/cronus/inventor-procedural onto this branch (design doc was never merged to main; B.0 brings it onto a branch off main so the doc bug fix has somewhere to land)
m/paliad#93 issue body — deadline_rule_id → rule_id correction	patched via Gitea API
Gitea comment on m/paliad#93 summarizing this report	posted (see §6 trailing summary on the issue)

Nothing migrated, nothing written to paliad.deadline_rules or any other live data table. Only mai.reports (progress) and the GitHub issue body / repo files were touched.

---

§9 Hard-stop status

B.0 COMPLETE. AWAITING B.1 GREENLIGHT.

Per the original instruction:

• B.1 (additive migration creating paliad.procedural_events, paliad.sequencing_rules, paliad.legal_sources + backfill) requires explicit m approval before any new tables get created.
• B.4 (destructive drop of paliad.deadline_rules + paliad.deadlines.rule_id) requires m's downtime-window approval AND a paliad.deadline_rules_pre_<N> snapshot table in the same migration.
• This researcher (curie) stays parked until head re-hires.

---

§10 Decisions worth surfacing to m before B.1 starts

1. NULL-submission_code rows (78 of them) — what to do during backfill? Recommendation (b): mint synthetic codes. m should confirm or pick (a)/(c).
2. B.5 deprecation header window length — the design (§8.2) says "one slice". For 7 active submission_drafts that's safe; the question is whether external integrations (Word templates with {{rule.X}}) need a longer window. The variable-bag alias contract (submission_vars.go) covers Word templates without a wire-format change, so "one slice" is defensible. m should confirm.
3. Migration number reservation — by the time B.1 ships, the live head may be 135+. The B.1 coder must re-check MAX(version) at write-time. (Not a decision; just a process note.)

These are the only open questions the B.0 audit surfaced. Everything else in the design holds.