Module Spec — multi_loc (module #4)

Transfer companion reopen — 2026-07-16: no new table or column. multi_loc.site now blocks soft deletion while referenced by a nonterminal Inventory Transfer. Post-shipment status changes remain allowed, and receipt continuity is preserved if the destination later becomes inactive or closed. Transfer tables and lifecycle logic remain Inventory-owned; no MultiLocService behavior was built.

1. Purpose

Owns the site concept — a physical location — and defines what site_id points to system-wide. Transfer business tables are now built in inventory; this module owns only the site row and its companion nonterminal soft-delete guard. Cross-site fulfillment services and per-site pricing/permission overrides remain deferred.

2. Ownership

Owns the site table and the site_id concept. No other module owns or duplicates site data. multi_loc.site is the FK target for 5 columns elsewhere; as of 2026-07-10, 3 are real composite FKs (identity.tenant_user.default_site_id, identity.user_site_assignment.site_id, identity.invitation_site_assignment.site_id), and 2 remain deferred (platform.tenant.primary_site_id, identity.user_permission_override.scope_id) — see Cross-Module Seams.

3. Layer & Dependencies

Tenant-scoped operational layer. Depends on platform (tenant isolation, tenant_id FK, set_updated_at() trigger), shared (module #3) via three natural-key FKs — region_code, country_code, climate_zone_code — so a site works in any country rather than assuming US-shaped address data, and now also on identity, via three new actor-attribution FKs (created_by_actor_id, updated_by_actor_id, reviewed_by_actor_id, all → identity.actor) added by the 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19).

4. Tables

1 table, 37 columns, locked 2026-07-05. See packages/db/migrations/20260705020000_multi_loc_module.sql for the full DDL.

2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19): site gained 8 columns — created_by_actor_id / updated_by_actor_id (nullable FK → identity.actor), automation_source (NOT NULL default 'human'), decision_provenance (nullable jsonb, field-level provenance for climate_zone_code/measurement_system since both can be auto-derived from country_code), and the review seam (review_status NOT NULL default 'not_required', review_reason, reviewed_by_actor_ididentity.actor, reviewed_at). Zero backfill risk: no MultiLocService exists yet, so site had 0 rows at migration time.

Table PK style Notes
site UUID Tenant-scoped, RLS enabled, soft-delete via deleted_at

Column-level shape:

Column Type Notes
id UUID PK, gen_random_uuid()
tenant_id UUID NOT NULL FK → platform.tenant
name text NOT NULL Display name
slug text NOT NULL URL-safe identifier, unique per tenant
code text Short code, unique per tenant when present
site_type text NOT NULL, default 'retail' CHECK 8 values: retail/yard/greenhouse/warehouse/farm/office/popup/other
status text NOT NULL, default 'active' CHECK 3 values: active/inactive/closed
is_primary boolean NOT NULL, default false Name kept consistent with identity.user_site_assignment.is_primary_site (not is_default)
address_line1 text Flexible address line — no street-number+street assumption
address_line2 text
address_line3 text
city text
region_code text FK → shared.administrative_region.iso_3166_2, nullable, ON DELETE SET NULL
postal_code text Free text, not zip-shaped
country_code char(2) FK → shared.country.iso_alpha2, nullable, ON DELETE SET NULL
climate_zone_code text FK → shared.climate_zone.code, nullable, ON DELETE SET NULL
measurement_system text CHECK metric/imperial or NULL — plain CHECK column, not an FK
timezone text IANA text, e.g. 'Europe/Berlin'
phone text
email text
operating_hours jsonb
latitude numeric(9,6) CHECK -90..90
longitude numeric(9,6) CHECK -180..180
sort_order integer NOT NULL, default 0
opened_at date
closed_at date CHECK >= opened_at when both set
created_at timestamptz NOT NULL, default now() Trigger-maintained via platform.set_updated_at()
updated_at timestamptz NOT NULL, default now() Trigger-maintained via platform.set_updated_at()
deleted_at timestamptz Soft delete

Global-FK design (replaces v1's single address JSONB {street,city,state,zip,country} column — US-shaped):

  • Address: address_line1/2/3 + city + postal_code (free text) + region_code → shared.administrative_region + country_code → shared.country.
  • Currency/locale: NOT stored on site — resolved via country_code join to shared.country.default_currency_code / default_locale_code. Avoids duplicating data shared already owns.
  • Units: measurement_system is a plain CHECK column (metric/imperial), not an FK — shared.country has no per-country unit-system lookup. Service layer defaults it from country_code at site-creation time (in-code map, e.g. US/LR/MM → imperial, else metric). Rejected alternative: adding a measurement_system column to the already-locked shared.country table — would reopen a locked module for one column; logged to OPEN_ITEMS as a future option instead.
  • Climate zone: climate_zone_code → shared.climate_zone.code (single nullable FK). Which SYSTEM is appropriate for the site's country (USDA for US, RHS for UK) is NOT DB-enforced — no country-to-preferred-system mapping exists in shared; UI/service-layer picks the right system based on country_code. Same category of gap as shared.plant_climate_zone.system from module #3's lock (PROJECT_DECISIONS #17).
  • Timezone: timezone IANA text, unchanged from v1, no shared lookup table (standard practice, app-library validated).

New DB-enforceable consistency CHECK (chk_site_region_country_match): region_code IS NULL OR (country_code IS NOT NULL AND left(region_code,2) = country_code). This closes cleanly (unlike the analogous shared.plant_climate_zone.system gap) because administrative_region.iso_3166_2 always embeds its country prefix in the value itself (e.g. 'DE-BW').

All 8 named CHECK constraints: chk_site_site_type, chk_site_status, chk_site_measurement_system, chk_site_is_primary_active (is_primary=false OR status='active'), chk_site_latitude (-90..90), chk_site_longitude (-180..180), chk_site_closed_after_opened (closed_at >= opened_at when both set), chk_site_region_country_match.

Indexes: PK id; tenant_id (plain); country_code (plain); region_code partial WHERE NOT NULL; climate_zone_code partial WHERE NOT NULL; 3 partial-unique WHERE deleted_at IS NULL(tenant_id, code), (tenant_id, slug), (tenant_id) WHERE is_primary=true.

Net column delta vs. v1 (docs/old/schema/schema_modules/schema_multi_loc.md, 1 table / 21 cols): +8 (21 → 29) — removed 1 JSONB address column, added 7 replacement address columns (address_line1/2/3, city, region_code, postal_code, country_code) = +6, plus 2 new global-derivation columns (climate_zone_code, measurement_system) = +2 more.

Built elsewhere, schema-only: inventory.transfer, inventory.transfer_line, and inventory.transfer_reconciliation_event now implement cross-site stock movement. Still deferred: Transfer application services/runtime wiring, site-level pricing/permission overrides, and the PostGIS geo-proximity index on lat/long.

5. Capabilities

Site record management: create/update/soft-delete a site, enforce exactly one primary site per tenant (partial-unique + CHECK that the primary must be active), enforce region/country consistency, and resolve currency/locale/climate/unit-system context from the site's current profile seams. Cross-site Transfer storage and database invariants are now Inventory-owned and built; cross-site application services and fulfillment orchestration remain deferred.

6. Service Contract — MultiLocService

Not built this pass — no MultiLocService; schema + migration + tests only, matching the shared module's own precedent. Downstream code queries multi_loc.site directly via Drizzle for now. A MultiLocService abstraction (site creation with measurement_system country-derivation, climate-zone-system selection by country, slug/code normalization) is deferred to whenever it's built.

Known write-time validation gaps, not DB-enforceable (both consistent with the current test data, not structurally guaranteed going forward):

  • climate_zone_code's system-appropriateness (USDA vs. RHS vs. others per country) — same category of gap as shared.plant_climate_zone.system from module #3.
  • measurement_system has no FK target in shared — whichever service eventually creates sites must derive it from country_code at write time (in-code map).

7. Data-Flow / Population Model

  • Provisioning: single-site tenants get an auto-created is_primary=true site at provisioning with minimal data — name + slug only. Full address/country/climate/timezone fields fill in during the site_configured onboarding task (platform.tenant_setup_task) — consistent with the v1 design's own provisioning note.
  • No seed migration for this modulesite rows are created per-tenant at provisioning time, not seeded globally like shared.
  • No AI write path for this module this pass.

8. Cross-Module Seams

Already locked in CROSS_MODULE_CONTRACTS.md:

  • Platform → multi_loc: platform.tenant.primary_site_id → multi_loc.site — deferred FK, now unblocked (multi_loc.site exists as of 2026-07-05) but deliberately NOT wired this pass; wiring it reopens the locked platform module.
  • identity → multi_loc: identity.tenant_user.default_site_id → multi_loc.site(id, tenant_id) — a tenant user's default working site for multi-site staff. Real composite FK as of 2026-07-10 (tenant_user_default_site_tenant_fkey, multi_loc's 1st reopen since lock). See PROJECT_DECISIONS #54.
  • identity → multi_loc: identity.user_site_assignment.site_id → multi_loc.site(id, tenant_id) — which sites a user is assigned to, for multi-site permission scoping. Real composite FK as of 2026-07-10 (user_site_assignment_site_tenant_fkey). See PROJECT_DECISIONS #54.
  • identity → multi_loc: identity.invitation_site_assignment.site_id → multi_loc.site(id, tenant_id) — pre-acceptance staged site access. Real composite FK as of 2026-07-10 (invitation_site_assignment_site_tenant_fkey), closing the gap this column started with at its own creation earlier the same day. See PROJECT_DECISIONS #52 → #54.
  • identity → multi_loc: identity.user_permission_override.scope_id → multi_loc.site (when scope_type='site') — site-scoped permission overrides. Deferred, unblocked, not wired this pass.
  • identity → multi_loc: identity.access_request.requested_scope_id → multi_loc.site (when requested_scope_type='site') — a 4th deferred column, surfaced 2026-07-10 as a previously-untracked sibling of the 3 above; not part of that bundle. Deferred, unblocked, not wired. See OPEN_ITEMS.

All four are logged to OPEN_ITEMS: wiring them means reopening 2 already-locked modules (platform, identity) — a separate deliberate pass, not this one.

9. AI Capability Discovery (Part D — 2026-07-06)

This module previously received only the schema-translation half of the AI Capability Plane pass (the 2026-07-06 autonomy-first backfill: agent-as-actor FKs, automation_source, the review seam, decision_provenance — see PROJECT_DECISIONS #19). This section runs the other half — AI_CAPABILITY_PLANE.md's Part D capability-discovery walk — against multi_loc as it stands today: 1 table (site), no MultiLocService, no capture-bar/command-canvas wiring, no AI write path. Given how little operational behavior exists around this table, most of Part D genuinely does not apply yet — this is recorded honestly below rather than force-fitting capabilities onto a module that is still schema + migration + tests only.

D1–D15 grid

Question Applies/Ruled-out Specific answer Triggered items
D1. Capture targets Ruled out (for now) No capture bar or command canvas is wired to multi_loc today — sites are created two ways, neither AI-mediated: (a) auto-provisioning at tenant signup (name + slug only, is_primary=true), and (b) manual admin CRUD once MultiLocService/site-management UI is built. If/when B1 is wired here, the natural capture sentence is "Open a new [site_type] at [address]" → resolves site_type, address lines, and defaults country_code-derived fields as a draft. Not built this pass. B1
D2. Routing rules Applies, trivially Only one table to route to: every site-creation input (provisioning or manual) writes to multi_loc.site with status='active' (or whatever the form specifies) and, if primary, sets is_primary=true (enforced by the partial-unique index + chk_site_is_primary_active). No multi-table placement decision exists because there is only one table in this schema. B1c
D3. Maintenance Applies site is small-cardinality (one row per physical location, typically single digits to low hundreds per tenant) but not immune to rot: (a) stale operating_hours — a JSONB blob nobody updates after seasonal hour changes; (b) address driftaddress_line1-3/city/postal_code going stale after a physical move without closed_at/new-site being recorded; (c) orphaned climate_zone_code/measurement_system — set once at creation, never revisited if country_code is corrected later (the CHECK constraints don't re-derive on update); (d) duplicate near-identical sites — e.g. a re-created site after a typo'd slug, now sitting alongside the original as deleted_at IS NULL. A hygiene agent here would: gap-fill measurement_system/climate_zone_code from country_code when null (safe auto-repair, deterministic in-code map, no judgment call) draft-only or auto per A4 threshold; flag (draft-only, never auto) sites whose country_code changed but climate_zone_code/measurement_system weren't updated to match (a mismatch is a B3 "wrong data" case, not negative-space); flag (draft-only) two active sites for the same tenant with high name/address similarity as possible duplicates. Never auto-merge or auto-delete a site — closing/merging locations has downstream reach (inventory, staff assignment) this module doesn't own. B3
D4. Error-prevention Applies Deterministic checks already DB-enforced, not needing new capability: primary-site uniqueness (site_tenant_id_primary_unique + chk_site_is_primary_active — a non-active site can never be marked primary), closed_at >= opened_at, lat/long bounds, region_codecountry_code consistency (chk_site_region_country_match). One gap worth a UI-level (not DB) warning: marking a site status='closed' or inactive' while it is still is_primary=true is currently only blocked for inactive/closed combined with is_primary=true via the CHECK — that IS blocked already. The one real UI-level warn-not-block case: deactivating/closing the only site a tenant has (leaves the tenant with zero active sites) — nothing in the schema prevents this; a service-layer check reading COUNT(*) WHERE tenant_id=X AND status='active' before allowing the write would warn "this is your last active site." B4
D5. Negative-space Applies Structural gap patterns that fit this table even without a service layer yet: (a) a tenant with zero sites where is_primary=true — should always have exactly one; detecting query: SELECT tenant_id FROM multi_loc.site WHERE deleted_at IS NULL GROUP BY tenant_id HAVING COUNT(*) FILTER (WHERE is_primary) = 0; (b) a platform.tenant row whose primary_site_id is still null even though multi_loc.site rows exist for that tenant (the deferred, unwired FK means this can't even be checked today — itself a negative-space finding, see gap below); (c) a site with opened_at set but still status='active' far past a typical "should have gone live" window — low value, deferred; (d) a site with country_code set but climate_zone_code/measurement_system still null (derivable but never derived) — overlaps with D3's gap-fill case. (a) is the one with real teeth today. B5
D6. Decision-support Ruled out No forecast or anomaly is genuinely worth surfacing from a single-table, low-cardinality, rarely-changing master-data table. Site counts/openings are not a time series with enough density per tenant to forecast against. None. B6
D7. Autonomy boundary Applies See the full per-action table below. A4, A13
D8. Evidence sources Ruled out (for now) No document/photo/voice evidence path creates or updates a site row today — site data is entered directly (provisioning defaults or manual admin form), not extracted from an uploaded artifact. If a future capability lets an admin photograph a lease/utility bill to auto-fill address fields, that would pull in B1d + A11 (the extracted address would need to match shared.country/shared.administrative_region before being trusted) — not built, not needed yet. B1d, A11
D9. Reconciliation pairs Applies, narrowly The one real reconciliation pair involving site today is cross-module and currently structurally impossible to check: platform.tenant.primary_site_id should reconcile with multi_loc.site WHERE is_primary=true for that tenant, but the FK is deferred/unwired, so there are two independent, potentially-divergent sources of "which site is primary" (the boolean on site, and — once wired — the FK on tenant). Clean match: both agree on the same site. Break: tenant.primary_site_id points to a site with is_primary=false, or is null while a site.is_primary=true row exists. Cannot be implemented as a real check until the FK is wired (logged as existing OPEN_ITEMS, not new). No intra-module reconciliation pair exists — one table can't reconcile against itself. B13
D10. Failure/rollback Applies No AI-assisted write path exists yet for site (see D1/D8), so there is nothing to roll back today. If/when D3's auto-repair (gap-filling measurement_system/climate_zone_code from country_code) is built as a genuine L5 auto-repair, its reversal is direct and trivial: UPDATE site SET measurement_system = NULL, climate_zone_code = NULL, decision_provenance = decision_provenance - 'measurement_system' - 'climate_zone_code' WHERE id = X — a clean undo (no downstream postings depend on these fields), reversal window unbounded (soft data, not a financial transaction). No compensating-transaction case applies — site carries no monetary or inventory state. A12, B11, C8
D11. Adversarial/abuse surface Applies, narrowly Untrusted-input surface is currently minimal because there's no evidence-capture or free-text-to-field AI path into site. If D1's capture-bar wiring is ever built ("Open a new location at 123 Main St, Austin"), the abuse surface would be: a malicious/malformed free-text address hiding an attempt to inject a bogus region_code/country_code pairing — already blocked deterministically by chk_site_region_country_match (DB-level, can't be bypassed by AI-drafted data) and the FK constraints on region_code/country_code/climate_zone_code (must resolve to real shared rows or the write fails). No fraud-pattern (financial abuse) surface exists — site has no monetary fields. Memory-poisoning read (per SCHEMA_DESIGN_RUNBOOK.md's bridge note under A11): no agent/tenant memory subsystem writes to or reads from site today, so there is no memory-poisoning vector here either — this module has neither an evidence-capture path (ruled out at D8) nor an agent-memory write path to poison. Revisit if a future "tenant operating memory" (B12) capability starts caching site facts. A11, A13
D12. Offline behavior Ruled out site is looked-up reference data (resolving tenant_id → site context, address, timezone), not a live transaction path — there's no interactive AI touchpoint here with a latency budget to fall back from. Reading/caching site records for offline POS operation is a pos/frontline-module concern (reads multi_loc.site, doesn't originate here). Nothing in this module queues for reconnect because nothing here is AI-mediated yet. None. A2, A3, A10
D13. Channel sync Ruled out site is not synced with any external channel. Inventory-owned Transfer schema now represents internal cross-site movement, but no channel-sync or Transfer runtime service is built. B13, A2
D14. Lifecycle/perishability Applies site has a genuine lifecycle, expressed today via status (active/inactive/closed) and the opened_at/closed_at date pair, but nothing currently detects or alerts on lifecycle transitions — it's a manually-set field, not a monitored state machine. Lifecycle states worth a signal: (a) a site sitting in status='inactive' for a long stretch without ever transitioning to closed (ambiguous "paused" state with no time-bound) — signal: updated_at/status age check; action: prompt admin to confirm closed vs. reopen; (b) a site with closed_at set in the past but status still 'active'/'inactive' (data contradiction, not DB-blocked since the CHECK only orders the two dates, doesn't require status to follow) — signal: closed_at < now() AND status != 'closed'; action: flag for correction; (c) opened_at in the future with status='active' (site marked live before its open date) — signal: simple date compare; action: warn. None of these are wired today (no service layer to run the check); this is what a future hygiene/lifecycle sweep would need. B5, B6, B13
D15. Capture modality Ruled out (for now) No capture modality is wired for site today — creation is either automatic (provisioning) or a plain admin form, not scan/photo/voice. If built, the natural primary modality is form (site setup is a one-time, detail-heavy, low-frequency task — name, address, hours, timezone — not a frontline hands-busy scenario), with photo as a plausible secondary for address capture (photograph a storefront sign or utility bill to extract address fields, per D8) rather than voice — voice doesn't fit a low-frequency back-office setup task the way it fits POS/receiving. Offline fallback: the plain admin form always works (no AI dependency to fall back from). B1, B1d, A2, A11

Capabilities Recorded

Applies (with real, if narrow, hooks in this module):

  • B3 (self-maintaining master data) — gap-fill measurement_system/climate_zone_code from country_code; duplicate-site detection.
  • B5 (negative-space detection) — missing-primary-site-per-tenant gap query; (blocked) tenant.primary_site_id vs. site.is_primary cross-check.
  • B13 (continuous reconciliation) — tenant.primary_site_idsite.is_primary pair, currently un-implementable until the deferred FK is wired.
  • A4 / A13 (authority ladder / segregation of duties) — see D7 table below.
  • A12 (rollback) — trivial direct-undo case for the one plausible auto-repair.
  • A11 (adversarial input) — already covered structurally by existing FK/CHECK constraints; no live attack surface yet.

Ruled out, with why:

  • B1 / B1c / B1d (capture & routing & evidence) — no capture bar, command canvas, or evidence-extraction path wired to multi_loc yet; only one table exists to route to, making B1c trivial-to-the-point-of-inapplicable.
  • B4 (error-prevention) — the real risky actions are already DB-enforced via CHECK constraints (primary/active coupling, date ordering, region/country match); the one soft gap (deactivating a tenant's last active site) is a service-layer concern for whenever MultiLocService is built, not a schema gap.
  • B6 (decision support) — no time-series-worthy signal exists on a low-cardinality, rarely-changing table.
  • A2 / A3 / A10 (tier routing / budget / offline) — nothing in this module is AI-mediated yet, so there is no latency/cost/offline behavior to define.
  • Channel sync (D13)site is internal-only data; the Transfer schema is built, but channel sync and runtime orchestration remain out of scope.

D7 — Full per-action autonomy-boundary table

Action Authority level Boundary Notes
Create a site (manual admin form) N/A — human-initiated, no AI drafting involved today may-act-alone (human, not AI) No AI touches this path yet; recorded for completeness.
Auto-create is_primary=true site at tenant provisioning (name+slug only) System-automated, deterministic (not AI) may-act-alone Already exists, automation_source='system', not an LLM decision — pure provisioning logic. No autonomy-ladder rung applies since it isn't an AI action.
Gap-fill measurement_system from country_code (in-code deterministic map) L5 (execute-within-limits) IF built as an automated backfill; today not built may-act-alone, bounded Deterministic derivation (not probabilistic AI), safe/reversible (D10), no financial/identity risk — fits B3's "safe auto-repair" carve-out. Must stamp automation_source='system' or 'agent' + decision_provenance.measurement_system={"source":"derived","rule":"country_code map"} per the existing schema seam.
Gap-fill/suggest climate_zone_code from country_code L3 (draft) at most draft-only Unlike measurement_system, the correct system (USDA vs. RHS vs. other) per country is not DB-enforced and has no canonical mapping table yet (module spec Section 6's known gap) — a wrong auto-pick has real downstream effect (feeds any future plant-care/zone-specific logic), so this must be draft + human confirm, never auto.
Suggest a merge of two near-duplicate sites (D3) L2 (suggest) draft-only Merging/closing a location has cross-module reach (inventory, staff site-assignment) this module doesn't own the consequences of — never auto, always human-confirmed, likely needs sign-off from whoever owns those downstream modules once they exist.
Flag a tenant with zero is_primary=true sites (D5) L1 (explain/surface) may-act-alone to surface; needs-approval to resolve Detecting and surfacing the gap is safe to run unattended; picking which site becomes primary is a business decision requiring a human (or at minimum a deterministic tie-break rule a human approved in advance).
Auto-repair a region_code/country_code mismatch Not applicable — DB CHECK (chk_site_region_country_match) blocks the write outright never (as an AI action) This is enforced before any row can be written; there is no "repair" step because the bad state can't persist.
Close/deactivate a site that is the tenant's last active site L1 (warn) today; L4 (submit-with-approval) if ever AI-drafted needs-approval Should warn per D4; if AI ever proposes closing a site, it must never auto-execute given it can leave a tenant operationally headless — always human-approved.
Any write touching site.is_primary combined with a financial/payment consequence N/A never (for AI alone) site has no monetary fields itself, so A13's SoD constraint doesn't have a live trigger here — recorded for completeness per D7's "be exhaustive" instruction, not because a real action exists.

Real gaps found

No schema gaps found. Every capability that genuinely applies to multi_loc in its current state (B3's safe gap-fill, B5's negative-space query, B13's reconciliation pair, D7's autonomy stamping) is already fully supported by the 2026-07-06 autonomy backfill's columns — automation_source, decision_provenance (specifically designed for the climate_zone_code/measurement_system field-level-provenance case this walk keeps surfacing), and the review_status/review_reason/reviewed_by_actor_id/reviewed_at review seam cover every draft/approval/auto-repair-attribution need identified above. The one real limitation found (D9: the tenant.primary_site_idsite.is_primary reconciliation pair can't be checked because the FK is deferred/unwired) is not a multi_loc schema gap — multi_loc.site already carries everything needed on its side; the gap is the unwired cross-module FK on platform.tenant, already tracked in OPEN_ITEMS as part of the known deferred-FK backlog, not a new finding from this pass. Similarly, D3/D7's flagged gap around no canonical country→climate-zone-system mapping is the same already-logged gap from module #4's original lock (module spec Section 6, PROJECT_DECISIONS #17's sibling issue) — not new. This module's Part D walk surfaces mostly ruled-out capabilities (D1, D6, D8, D12, D13, D15) precisely because it has no service layer and no AI write path yet, consistent with the task brief's expectation for the newest, smallest, least-built module.

Last modified: Jul 16, 2026, 9:05 AM PT
On this page
Esc