Skip to main content

ADR-0024 — Serial numbers as first-class entities

  • Status: Accepted
  • Date: 2026-05-11
  • Decision-makers: Tom Anderson

Context​

Three planned roadmap slices share an underlying need:

  • Slice 15 — E-bike serialization and battery lifecycle wants per-battery cycle counts, firmware versions, and warranty periods that are independent of the parent bike's
  • Slice 16 — Bike registration integration needs a stable identifier for the bike across registry pushes, ownership transfers, and theft reports
  • Slice 17 — Warranty claim workflow needs to assemble the full event history for a serialized item: when bought, when serviced, when failed, by whom

Today the schema treats serial numbers as a text column on inventory_skus (and a redundant copy on customer_bikes). That works for the simple case ("this bike's serial is X") but doesn't support the multi-event lifecycle the three new slices need:

purchase → registration → service history → warranty claim → trade-in → resale → next owner

The serial number is the through-line across all of those events. Modeling it as a text field means re-deriving its history through joins every query, and means events that should reference "this specific physical object" have no consistent foreign key to use.

Batteries make this worse: they work the same way as bikes but with their own attributes (cycle count, charge history, firmware version, warranty period independent of the bike's). A bike can outlive its first battery; the second battery is a different object with a different lifecycle.

Options considered​

Option 1 — Keep as text column, re-derive on each query. Cheapest near term. Cost compounds over time: every new slice that wants serialized-object history reinvents the join. Cross-event indexing is impossible (the text column isn't a real identity). Warranty workflow becomes prohibitively manual because the assembly of "everything we know about serial X" can't be cached.

Option 2 — Promote to a first-class entity with its own table. A serialized_objects table holds one row per physical tracked item; events reference it by id. Migration of existing inventory data is non-trivial but bounded; new events compose cleanly.

Option 3 — Promote selectively, only for items the shop chooses to track. A compromise where shops opt items in. Adds an opt-in dimension that quickly becomes its own complexity surface (which event types respect the opt-in? what happens when an opted-out item gets a warranty claim?). The semantics of "tracked vs untracked" pollute every downstream feature.

Decision​

Promote serial numbers to a first-class entity. Before any of slices 15–17 builds, land the primitive.

Schema shape (engineer to refine the exact column set when the slice 12+ prep migration is written):

CREATE TABLE serialized_objects (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  object_type TEXT NOT NULL,            -- 'bike' | 'battery' | 'motor' | 'other'
  serial_number TEXT NOT NULL,
  manufacturer_id INTEGER REFERENCES manufacturers(id),
  model_reference TEXT,                 -- the SKU or model identifier
  current_owner_customer_id INTEGER REFERENCES customers(id),
  current_status TEXT NOT NULL,         -- 'in_stock' | 'sold' | 'in_service' | 'warranty_claim' | 'returned' | 'retired'
  registration_id TEXT,                 -- e.g., Project529 / Bike Index id; NULL until slice 16 registers
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  last_event_at TEXT,
  UNIQUE (object_type, serial_number)
);

CREATE TABLE serialized_object_relationships (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  parent_id INTEGER NOT NULL REFERENCES serialized_objects(id),
  child_id INTEGER NOT NULL REFERENCES serialized_objects(id),
  relationship_type TEXT NOT NULL,      -- 'battery_of_bike' | 'motor_of_bike' | 'spare_for' | etc.
  attached_at TEXT NOT NULL DEFAULT (datetime('now')),
  detached_at TEXT
);

Lifecycle events (purchase, registration, service, warranty, trade-in, resale) reference serialized_objects.id, not the serial-number text. Batteries and motors are their own rows in serialized_objects, linked to a parent bike row via serialized_object_relationships.

Migration of existing data:

  • For every row in inventory_skus with a non-null serial number that's been sold (i.e., has a transaction line referencing it), generate a serialized_objects row of object_type='bike'
  • Same for customer_bikes (where the serial number is set)
  • The text columns on the existing tables remain as denormalized convenience columns for now; new event types use the serialized_objects.id foreign key
  • Eventual removal of the text columns is a separate cleanup; not blocking for slices 15–17

Consequences​

Positive:

  • Slices 15 / 16 / 17 build on a coherent primitive instead of three different join shapes
  • Cross-event queries become indexable: "show me everything we know about serial X" is one PK lookup + N event-table joins, all indexed
  • Future features compose on the same primitive: recalls, theft recovery, multi-owner ownership history, depreciation tracking, fleet-asset auditing
  • Battery as its own object correctly models the lifecycle (a bike outliving its first battery)
  • Stays consistent with audit-everything — every event still writes through recordMutation

Negative:

  • Existing inventory data with serial numbers in text columns needs migration. Bounded (~9,338 variants for Swicked, fewer with serials), but not trivial.
  • The denormalized text columns remaining after migration are a transitional smell. They must be removed eventually or they become a permanent inconsistency surface.
  • One more concept ("serialized object") for new readers of the schema to learn.

Mitigations:

  • The migration runs as Stage 3 prep, not as part of any single slice. Lives in a numbered migration file with full row-by-row provenance.
  • Existing rows get a serialized_objects record auto-generated by the migration script; manual intervention only for ambiguous cases (duplicates, missing serials).
  • The text columns stay temporarily for read-side convenience but new write code uses the foreign key; a tracked tech-debt item flags the removal task.
  • The "serialized object" concept gets its own entity page (analogous to existing entity pages) when the slice lands, so new readers have one place to learn it.

Notes​

This ADR captures the decision. The implementation (exact column set, migration script, audit-event vocabulary, UI surface) is part of the Stage 3 prep work — not this consolidation pass. The point of pinning the decision now is so the three Stage 3 slices can be specified consistently when their turn comes.

See also​