multi_loc — Phase 4

Locked at 1 table for v1.0 (site, 21 columns). Owns the site concept — a physical nursery property — and defines what site_id points to system-wide. Multi-site operational features (transfers, cross-site fulfillment, per-site pricing, site-level permission overrides) are deferred to v1.5.

Cross-Phase Foreign Keys (multi_loc)

Column	Target	Status
site.tenant_id	platform.tenant	Phase 1 exists — FK enforced at migration time.

This table is the deferred FK target for four columns elsewhere: platform.tenant.primary_site_id, identity.tenant_user.default_site_id, identity.user_site_assignment.site_id, identity.user_permission_override.scope_id (when scope_type = 'site'). Those FK constraints can now be added in the Phase 4 multi_loc migration once multi_loc.site exists — they were created as plain UUID columns in Phases 1 and 3.

multi_loc.site

A physical nursery property: retail location, yard, greenhouse, warehouse, farm, office, popup. Single-site tenants get an auto-created is_primary=true site at provisioning step 2 with minimal data; details fill in during the site_configured onboarding task.

Tenant-scoped.

RLS: enabled — tenant isolation policy on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
name	text	NOT NULL	—	Display name, e.g. "Temecula Main"
slug	text	NOT NULL	—	URL-safe public identifier, e.g. "temecula-main". Normalized lowercase in service layer (not DB CHECK). Used for consumer-app URLs: vrida.app/nursery/{tenant_slug}/{site_slug}.
code	text	nullable	—	Short code, e.g. TEM / MUR. Normalized uppercase in service layer (not DB CHECK). Optional: multiple coderless sites per tenant are permitted by design — the partial unique (tenant_id, code) only constrains sites that HAVE a code (Postgres NULL ≠ NULL).
site_type	text	NOT NULL	'retail'	CHECK IN ('retail','yard','greenhouse','warehouse','farm','office','popup','other')
status	text	NOT NULL	'active'	CHECK IN ('active','inactive','closed')
is_primary	boolean	NOT NULL	false	Tenant's default site. Exactly one per tenant (enforced by partial unique).
address	JSONB	nullable	—	{street,city,state,zip,country}
phone	text	nullable	—
email	text	nullable	—
timezone	text	nullable	—	IANA timezone. Overrides platform.tenant.timezone for site-local operations. NULL = inherit tenant timezone.
operating_hours	JSONB	nullable	—	{"mon":{"open":"08:00","close":"18:00"},"tue":{...},...}
latitude	decimal(9,6)	nullable	—	For consumer-app mapping
longitude	decimal(9,6)	nullable	—
sort_order	integer	NOT NULL	0	UI ordering
opened_at	date	nullable	—	When the site opened (seasonal / reporting)
closed_at	date	nullable	—	When the site closed (vs current status)
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
(table CHECK 1)	—	—	—	CHECK (is_primary = false OR status = 'active') — the primary site must be active
(table CHECK 2)	—	—	—	CHECK (latitude IS NULL OR latitude BETWEEN -90 AND 90)
(table CHECK 3)	—	—	—	CHECK (longitude IS NULL OR longitude BETWEEN -180 AND 180)
(table CHECK 4)	—	—	—	CHECK (closed_at IS NULL OR opened_at IS NULL OR closed_at >= opened_at)

Indexes:

• PK on id
• on (tenant_id) — RLS filters on tenant_id on every query; needs a leading index
• UNIQUE on (tenant_id, code) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)
• UNIQUE on (tenant_id, slug) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)
• UNIQUE on (tenant_id) WHERE is_primary = true AND deleted_at IS NULL — at most one primary site per tenant

Provisioning note: single-site tenants auto-receive a site row at provisioning step 2 with is_primary = true, status = 'active', and minimal data (just name and slug). Address, hours, geo, and code are populated during the site_configured onboarding task (see platform.tenant_setup_task).

String normalization: slug and code are normalized in the service layer (lowercase slug; uppercase code), not via DB CHECK. Service writes go through MultiLocService.createSite / updateSite which applies the normalization.

---

multi_loc — Design Notes

v1.0 scope (1 table) vs. v1.5 deferrals

v1.0 (built now)	v1.5 (deferred — do NOT build now)
site — the property record	transfer — cross-site stock movement header
Site provisioning at step 2	transfer_line — per-line item / quantity
site_id plumbed through operational schemas	Site-specific pricing overrides
is_primary site-of-record	Site-specific permission overrides (beyond identity.user_site_assignment)
	Fulfillment / sales routing toggles per site
	Cross-site inventory visibility rules
	Geo-proximity index on latitude / longitude (PostGIS + GiST) for the consumer-app "nurseries near me" feature. Needs the PostGIS extension, not a plain btree. Build with the v1.5 consumer-app geo features.

The deferred items arrive with the multi-site UI in v1.5. v1.0 ships single-site-effective even though the schema carries site_id everywhere transactional (per PROJECT_DECISIONS "Multi-Location Module" forward decision #1).

Patterns recap

• Tenant-scoped — tenant_id NOT NULL, RLS enabled, soft delete via deleted_at.
• Partial unique indexes on (tenant_id, code), (tenant_id, slug), and (tenant_id) WHERE is_primary = true — all with WHERE deleted_at IS NULL.
• JSONB: address ({street,city,state,zip,country}), operating_hours ({"mon":{"open":"08:00","close":"18:00"},...}).
• Geo precision: decimal(9,6) (≈11 cm at the equator, more than enough for consumer-app mapping).

Deliberate non-enforcement: status ⇄ closed_at

status and closed_at are deliberately decoupled — they are NOT CHECK-linked:

• A site can be status = 'inactive' (temporarily off — winter shutdown, renovation) without closed_at being set.
• A site can have closed_at set and later reopen via a status change back to 'active' — closed_at preserves the historical close date even after the site is operational again.

Do NOT add CHECK (status = 'closed' ⇒ closed_at IS NOT NULL) or CHECK (closed_at IS NOT NULL ⇒ status = 'closed'). Either constraint would break the reopen flow. The two columns capture different things: status is current state; closed_at is the most recent historical close timestamp.

---