multi_loc — Phase 4 (module #4)

Transfer companion reopen — 2026-07-16: table/column counts remain 1 table / 36 columns. multi_loc.site now has an Inventory-owned BEFORE UPDATE OF deleted_at guard that blocks soft deletion while the site is the source or destination of an inventory.transfer in draft, approved, in_transit, or partially_received. received and cancelled rows do not block later deletion. Status changes remain allowed after shipment so an in-flight Transfer can still be received. The guard locks referencing Transfer rows in canonical order and does not add Transfer behavior to a Multi-Location service.

1 table, 36 columns (down from 37) — LOCKED 2026-07-05. Multi-location: the physical/operational locations (retail stores, yards, greenhouses, warehouses, farms, offices, pop-ups) a tenant operates. Tenant-scoped. Depends on platform (tenant ownership), shared (global address reference data — climate/hardiness-zone reference data relocated to nursery_ref 2026-07-18, no longer a multi_loc dependency, see below), and, new as of the 2026-07-06 autonomy-first backfill, identity (actor-attribution FKs on site — see PROJECT_DECISIONS #19). REOPENED 2026-07-10 (1st reopen since lock) — pure constraint-shape change, no column/table count impact: site gained UNIQUE(id, tenant_id) (site_id_tenant_id_unique), the prerequisite for 3 identity-side composite FKs (identity.user_site_assignment.site_id, identity.tenant_user.default_site_id, identity.invitation_site_assignment.site_id) to be wired for real, closing a gap this doc's own Cross-Phase FK table had tracked as deferred since lock. See PROJECT_DECISIONS.md #54. REOPENED again 2026-07-18 (2nd reopen), Phase 2 (Nursery Vertical Extraction, PROJECT_DECISIONS #70) — climate_zone_code column DROPPED (replaced by the new nursery.site_profile.hardiness_zone_id, see docs/database/schema_docs/nursery.md); site_type CHECK narrowed from an 8-value list (which included 3 nursery-only values: yard,greenhouse,farm) to ('retail','warehouse','office','temporary','other') — the 3 nursery values now live on nursery.site_profile.nursery_site_type instead. measurement_system (metric/imperial) was confirmed genuinely orthogonal to nursery concerns during this phase's research and deliberately left untouched.

Global rules for this schema:

• Tenant-scoped — multi_loc.site carries tenant_id NOT NULL FK → platform.tenant, with RLS enabled and a tenant-isolation policy (USING/WITH CHECK on current_setting('app.current_tenant_id')).
• Soft delete — deleted_at timestamptz, nullable. All partial-unique indexes are scoped WHERE deleted_at IS NULL.
• updated_at is trigger-maintained via platform.set_updated_at() (same shared trigger function used elsewhere in the platform).
• Global from day 1, country-code-driven — a site's address, currency/locale, and units are not assumed to be US-shaped. Address is flat lines + natural-key FKs into shared (module #3, locked 2026-07-05); currency/locale are derived via country_code rather than duplicated; units are a plain CHECK (no shared lookup exists to FK against). This mirrors the same country-code-driven pattern shared itself established for country/currency/locale. Climate/hardiness zone is no longer represented on this table at all — site.climate_zone_code was dropped 2026-07-18 (Phase 2, PROJECT_DECISIONS #70); a nursery tenant's site now carries nursery.site_profile.hardiness_zone_id → nursery_ref.climate_zone instead, since climate zone is a nursery-vertical concept, not a generic-core one.

Superseded v1 note: multi_loc previously existed in v1 (superseded) as a 1-table design (site, 21 columns) — see docs/old/schema/schema_modules/schema_multi_loc.md. v1 stored address as a single address JSONB column ({street, city, state, zip, country}) — implicitly US-shaped (state, zip). This v2 design replaces that JSONB column with flat address lines plus natural-key FKs into shared, so a site works in any country. Net column delta: +8 (21 → 29) — removed 1 JSONB column, added 7 replacement address columns (address_line1, address_line2, address_line3, city, region_code, postal_code, country_code, net +6), plus 2 new global-derivation columns (climate_zone_code, measurement_system, +2 more).

Cross-Phase Foreign Keys (multi_loc)

Column	Target	Notes
site.tenant_id	platform.tenant	Intra-tenancy, enforced.
site.region_code	shared.administrative_region.iso_3166_2	Nullable, ON DELETE SET NULL. Cross-schema, enforced.
site.country_code	shared.country.iso_alpha2	Nullable, ON DELETE SET NULL. Cross-schema, enforced.
site.created_by_actor_id	identity.actor	Nullable. Cross-schema, enforced. Added 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19).
site.updated_by_actor_id	identity.actor	Nullable. Cross-schema, enforced. Added 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19).
site.reviewed_by_actor_id	identity.actor	Nullable. Cross-schema, enforced. Added 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19).
platform.tenant.primary_site_id	multi_loc.site	Deferred, now unblocked, deliberately NOT wired this pass. site now exists as a valid target, but wiring it means reopening the already-locked platform module — a separate deliberate pass.
identity.tenant_user.default_site_id	multi_loc.site(id, tenant_id)	Real composite FK as of 2026-07-10 (tenant_user_default_site_tenant_fkey). See PROJECT_DECISIONS #54.
identity.user_site_assignment.site_id	multi_loc.site(id, tenant_id)	Real composite FK as of 2026-07-10 (user_site_assignment_site_tenant_fkey). See PROJECT_DECISIONS #54.
identity.invitation_site_assignment.site_id	multi_loc.site(id, tenant_id)	Real composite FK as of 2026-07-10 (invitation_site_assignment_site_tenant_fkey). See PROJECT_DECISIONS #54.
identity.user_permission_override.scope_id	multi_loc.site (when scope_type='site')	Deferred, now unblocked, deliberately NOT wired this pass. Would reopen the already-locked identity module.
identity.access_request.requested_scope_id	multi_loc.site (when requested_scope_type='site')	Deferred, still unwired. Surfaced 2026-07-10 as a previously-untracked sibling of the 3 columns wired above — not part of that bundle. See OPEN_ITEMS.

Not stored on site: currency and locale. Both are resolved by joining country_code → shared.country.default_currency_code / shared.country.default_locale_code, avoiding duplication of data shared already owns.

---

multi_loc.site

A physical or operational location belonging to a tenant — retail store, yard, greenhouse, warehouse, farm, office, or pop-up.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy site_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid.

Soft delete: deleted_at timestamptz, nullable.

updated_at: trigger-maintained via platform.set_updated_at().

Transfer soft-delete companion guard: trg_site_block_nonterminal_transfer_soft_delete calls inventory.block_nonterminal_transfer_soft_delete() before deleted_at changes. It blocks only nonterminal Transfer references and does not block ordinary status changes.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
name	text	NOT NULL	—
slug	text	NOT NULL	—	UNIQUE per tenant (partial-unique, see indexes)
code	text	nullable	—	UNIQUE per tenant (partial-unique, see indexes)
site_type	text	NOT NULL	'retail'	CHECK IN (retail,warehouse,office,temporary,other) — narrowed 2026-07-18, Phase 2 (PROJECT_DECISIONS #70; was 8 values including 3 nursery-only: yard,greenhouse,farm, now on nursery.site_profile.nursery_site_type)
status	text	NOT NULL	'active'	CHECK IN (active,inactive,closed)
is_primary	boolean	NOT NULL	false	At most one true per tenant (partial-unique, see indexes). Named is_primary (not is_default) to match identity.user_site_assignment.is_primary_site.
address_line1	text	nullable	—	Flexible free-form line — no street-number+street assumption
address_line2	text	nullable	—
address_line3	text	nullable	—
city	text	nullable	—
region_code	text	nullable	—	FK → shared.administrative_region.iso_3166_2, ON DELETE SET NULL
postal_code	text	nullable	—	Free text — not zip-shaped
country_code	char(2)	nullable	—	FK → shared.country.iso_alpha2, ON DELETE SET NULL
measurement_system	text	nullable	—	CHECK IN (metric,imperial) OR NULL. Not an FK — shared.country has no per-country unit-system lookup. Service-layer defaults this from country_code at site-creation time (in-code map, e.g. US/LR/MM → imperial, else metric).
timezone	text	nullable	—	IANA tz name, e.g. 'Europe/Berlin'. Unchanged from v1; no shared lookup table.
phone	text	nullable	—
email	text	nullable	—
operating_hours	jsonb	nullable	—	Shape: {"mon":{"open":"08:00","close":"18:00"},...} — unchanged from v1
latitude	numeric(9,6)	nullable	—	CHECK between -90 and 90
longitude	numeric(9,6)	nullable	—	CHECK between -180 and 180
sort_order	integer	NOT NULL	0
opened_at	date	nullable	—
closed_at	date	nullable	—	CHECK >= opened_at when both set
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()
deleted_at	timestamptz	nullable	—	Soft delete
created_by_actor_id	UUID	nullable	—	FK → identity.actor. Added 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19).
updated_by_actor_id	UUID	nullable	—	FK → identity.actor. Added 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19).
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed). Added 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19).
decision_provenance	jsonb	nullable	—	Field-level provenance, originally for climate_zone_code/measurement_system since a single row-level flag can't express mixed provenance within one row; scoped to measurement_system alone since climate_zone_code was dropped 2026-07-18 (Phase 2, PROJECT_DECISIONS #70). Added 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19).
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected). Added 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19).
review_reason	text	nullable	—	Added 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19).
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor. Added 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19).
reviewed_at	timestamptz	nullable	—	Added 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19).

CHECK constraints (10, all named):

Name	Condition
chk_site_site_type	site_type IN ('retail','warehouse','office','temporary','other') — narrowed 2026-07-18, Phase 2 (PROJECT_DECISIONS #70; was ('retail','yard','greenhouse','warehouse','farm','office','popup','other'))
chk_site_status	status IN ('active','inactive','closed')
chk_site_measurement_system	measurement_system IS NULL OR measurement_system IN ('metric','imperial')
chk_site_is_primary_active	is_primary = false OR status = 'active'
chk_site_latitude	latitude IS NULL OR latitude BETWEEN -90 AND 90
chk_site_longitude	longitude IS NULL OR longitude BETWEEN -180 AND 180
chk_site_closed_after_opened	closed_at IS NULL OR opened_at IS NULL OR closed_at >= opened_at
chk_site_region_country_match	region_code IS NULL OR (country_code IS NOT NULL AND left(region_code, 2) = country_code)
chk_site_automation_source	automation_source IN ('human','agent','system','seed'). Added 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19).
chk_site_review_status	review_status IN ('not_required','pending','approved','rejected'). Added 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19).

Indexes:

• PK on id
• Plain index on tenant_id
• Plain index on country_code
• Partial index on region_code WHERE NOT NULL
• Partial UNIQUE on (tenant_id, code) WHERE deleted_at IS NULL
• Partial UNIQUE on (tenant_id, slug) WHERE deleted_at IS NULL
• Partial UNIQUE on (tenant_id) WHERE is_primary = true AND deleted_at IS NULL
• UNIQUE on (id, tenant_id) (site_id_tenant_id_unique) — added 2026-07-10, prerequisite for 3 identity-side composite FKs; zero risk on live data since id was already the sole PK. See PROJECT_DECISIONS #54.

---

Multi-Location — Design Patterns Summary

Global address model: flat lines + natural-key FKs, replacing v1's JSONB

v1 stored address as a single address jsonb column shaped {street, city, state, zip, country} — implicitly US-only (state and zip don't generalize). v2 replaces it with address_line1/address_line2/address_line3 (flexible free-form lines, no street-number+street assumption), city, postal_code (free text, not zip-shaped), region_code (FK → shared.administrative_region), and country_code (FK → shared.country). This lets a site's address be validated and queried consistently regardless of country, using the same reference data shared already built.

Currency/locale: derived, not duplicated

site does not store a currency or locale column. Both are resolved at read time by joining country_code → shared.country.default_currency_code / shared.country.default_locale_code. This avoids multi_loc re-owning data that shared already owns and keeps the two in sync by construction.

measurement_system: plain CHECK, not an FK — rationale

Unlike region_code, country_code, and climate_zone_code, measurement_system has no shared table to FK against — shared.country (already locked as of module #3) has no per-country unit-system lookup. Rather than reopening the locked shared.country table to add one column, measurement_system is a plain CHECK (metric|imperial) column on site, defaulted by the service layer from country_code at site-creation time via an in-code map (e.g. US/LR/MM → imperial, else metric). Adding a measurement_system-equivalent column to shared.country was considered and rejected for this pass — logged to OPEN_ITEMS as a future option.

climate_zone_code: DROPPED 2026-07-18 (Phase 2, PROJECT_DECISIONS #70)

site.climate_zone_code — formerly one nullable FK into shared.climate_zone's multi-system dictionary (USDA, RHS, AHS Heat, Australian, EU) — no longer exists on this table. Climate/hardiness zone is a nursery-vertical concept, not a generic multi-location one, so it now lives on the tenant-scoped extension table nursery.site_profile.hardiness_zone_id → nursery_ref.climate_zone (formerly shared.climate_zone, relocated in the same Phase 2 migration). The system-appropriateness gap this section used to describe (no country-to-preferred-system mapping exists to pick USDA vs. RHS vs. AHS Heat vs. Australian vs. EU automatically) is unchanged in substance, just relocated — see docs/database/schema_docs/nursery.md/nursery_ref.md.

chk_site_region_country_match: a DB CHECK that actually closes

Unlike the analogous shared.plant_climate_zone.system consistency gap (which needs a trigger to enforce and was left app-enforced), region_code ⇄ country_code consistency is DB-enforceable here, because shared.administrative_region.iso_3166_2 always embeds its country prefix directly in the value (e.g. 'DE-BW' for a German region). chk_site_region_country_match checks region_code IS NULL OR (country_code IS NOT NULL AND left(region_code, 2) = country_code) — a plain CHECK, no trigger required.

is_primary, not is_default

The boolean marking a tenant's primary site is named is_primary rather than is_default, to match the existing naming already locked on identity.user_site_assignment.is_primary_site. Enforced to at most one true row per tenant via a partial-unique index, and constrained by chk_site_is_primary_active so a non-active site can never be marked primary.

Deferred FKs, now unblocked but not wired this pass

UPDATE 2026-07-10: of the (now known to be 5, not 4) FKs elsewhere in the schema targeting multi_loc.site conceptually, 3 are now real composite FKs — identity.tenant_user.default_site_id, identity.user_site_assignment.site_id, identity.invitation_site_assignment.site_id (see PROJECT_DECISIONS #54, and this doc's own Cross-Phase FK table above). platform.tenant.primary_site_id remains deferred (would reopen the locked platform module), and identity.user_permission_override.scope_id plus a previously-untracked sibling, identity.access_request.requested_scope_id, remain deferred within identity itself — see OPEN_ITEMS.

Deferred features (v1.5, unchanged from v1)

The original Multi-Location pass deferred cross-site movement. That deferral is now closed at the schema layer by Inventory-owned transfer, transfer_line, and transfer_reconciliation_event. Still deferred: Transfer application/runtime wiring, site-level pricing/permission overrides, and a PostGIS geo-proximity index on latitude/longitude.

JSONB columns

One: operating_hours. (v1's address JSONB column is removed in this design — see above.)

Service layer

No MultiLocService exists yet — this pass is schema + migration + tests only, matching the precedent set by the shared module (schema-first, service-layer-later).

Autonomy-first backfill (2026-07-06)

site gained 8 columns applying the canonical autonomy-first pattern retrofitted across all four locked modules (platform, identity, shared, multi_loc) in a single coherent cross-cutting pass — see PROJECT_DECISIONS #19 for the full pattern and per-module rationale. Applied here:

• Agent-as-actor attribution: created_by_actor_id, updated_by_actor_id, reviewed_by_actor_id, all nullable FKs → identity.actor (the polymorphic root — actor_type IN ('user','service_account','agent')), not identity.identity_user. This is what introduces multi_loc's new dependency on identity (previously multi_loc depended only on platform and shared).
• automation_source: text NOT NULL DEFAULT 'human', CHECK IN ('human','agent','system','seed'). No separate created_autonomously boolean — derivable from this column.
• Human-in-the-loop review seam: review_status (text NOT NULL DEFAULT 'not_required', CHECK IN ('not_required','pending','approved','rejected')), review_reason, reviewed_by_actor_id, reviewed_at — models the same shape as identity.access_request.
• decision_provenance (jsonb, nullable): originally captured field-level provenance for both climate_zone_code and measurement_system, since either could be independently auto-derived from country_code and a single row-level flag can't express mixed provenance within one row. climate_zone_code was dropped 2026-07-18 (Phase 2, PROJECT_DECISIONS #70) — this column's field-level provenance use is now scoped to measurement_system alone; the JSONB shape itself is unchanged (still permits arbitrary keys), so no migration was needed for existing rows.

automation_source != 'human' implying decision_provenance should be populated is an app-enforced service-layer invariant, not a hard DB CHECK — same deliberate deferral applied to every other table in this backfill.

Zero backfill risk: no MultiLocService exists yet (see Service Layer note above), so site had 0 rows at backfill time — no UPDATE statements were needed to retrofit existing data, unlike shared.plant_common_name/shared.plant_climate_zone in the same pass.

Net column delta: +8 (29 → 37). Table count unchanged (still 1 table).