ADR-0003: Repositories Table Materializes the Tenant/Repository Scope¶
Context¶
ADR-0001 introduced “repository” as the unit of ThingHub visibility — the account’s reference into a thing, materialized by thing_accesses. ADR-0002 re-keyed group and tag memberships off thing_accesses.id so that cross-tenant chip leakage became structurally impossible.
Both ADRs used “repository” as a vocabulary, but the schema only ever represented it as a free-floating device_groups.tenant_id string column. This shape produces three problems:
No place to hang per-repository attributes. ADR-0001 / ADR-0002 reference things like retention policies, sharing settings, and quotas as living “on the repository”, but there was no row to attach them to. Every such attribute would have had to be duplicated on each of
device_groups,device_tags,thing_accessesand friends — or stored in a siblingrepository_settingstable with a free-floating string FK that loses referential integrity at the DB layer.No referential integrity for the account scope. A typo in
device_groups.tenant_idproduced a silently orphaned row; the database could not catch it.String comparison instead of FK join. Group queries compared
g.tenant_id = :accountIdas a varchar predicate. With a real FK the scope is expressed as a JOIN that the planner can index and hash-join on.
Decision¶
Introduce a real repositories table:
CREATE TABLE repositories (
id VARCHAR(36) PRIMARY KEY,
account_id VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
device_groups.tenant_id (string) is replaced by device_groups.repository_id (FK to repositories.id with ON DELETE CASCADE). One repository per account; the unique constraint on account_id enforces 1:1.
The repository row is auto-bootstrapped lazily by RepositoryService.findOrCreateByAccountId on the very first access for that account (read or write). Concurrent first-time callers race on the unique-constraint insert; the service catches the integrity-violation error and falls back to a re-read.
The migration (V33__create_repositories_table.sql) is atomic: it backfills repositories from the UNION of device_groups.tenant_id and thing_accesses.account_id (covering accounts that have devices but no groups yet), backfills device_groups.repository_id, asserts no row was left orphaned, and drops the legacy tenant_id column — all in a single migration transaction. If backfill leaves any unmatched row, the migration aborts and the database stays on V32.
Consequences¶
Positive¶
Future per-repository attributes (retention, sharing, quotas) attach as columns on
repositorieswith no schema gymnastics.Cross-tenant isolation is now expressible as a FK join. The DB enforces that every
device_groupsrow is owned by some real repository; an attempted typo cannot produce a silently orphaned row.Lazy bootstrap means existing endpoints “just work” for accounts that have never created a group:
GET /thinghub/api/v1/device-groupsfor a brand-new account returns[]and quietly creates the repository row for next time.
Negative / Trade-offs¶
Wire-format compat is preserved by patch:
DeviceGroupDto.tenantIdremains in the JSON response for frontend compat, populated fromrepository.account_idrather than the storage column. The field’s name now lies about its provenance — but the frontend has not been redeployed to read a renamed field, and the cost of churning that path is not worth the cleanup. A future API revision should rename it toaccount_id.Tags / TagService still use the legacy
tenantIdstring. Out of scope for this plan; the same treatment will apply in a follow-up ADR/plan that migratestag_definitionsand theDeviceTagenrichment paths.The migration is irreversible in the sense that it drops
tenant_id. The orphan-count guard inside V33 ensures the database cannot end up half-migrated, so “rolling forward” is the only path; rolling back to V32 would require a separate downgrade migration that re-createstenant_idfrom the FK join.