nursery — Phase 2 (Nursery Vertical Extraction, new schema)

2 tables, 18 columns — created 2026-07-18, Phase 2 (Nursery Vertical Extraction), PROJECT_DECISIONS.md #70. Tenant-scoped vertical extension schema for the nursery industry — mechanism (b) in SCHEMA_CONVENTIONS.md §21's governing principle: a <vertical>.<entity>_profile tenant extension table keyed to a core entity, used instead of adding vertical-only columns/FKs to the core tables themselves. Standard RLS (tenant-isolation policy on tenant_id) and composite-FK conventions throughout, same as any other tenant-scoped module. Depends on platform (tenant ownership), inventory (item_profile → item composite FK), multi_loc (site_profile → site composite FK), and nursery_ref (plant/climate_zone lookups). No NurseryService exists yet — schema-only.

Global rules for this schema:

• Tenant-scoped — tenant_id on every table; standard RLS tenant-isolation policy applies.
• Soft delete via deleted_at — both tables carry it, paired with a partial unique index (WHERE deleted_at IS NULL), not a plain UNIQUE, so a soft-deleted profile never permanently blocks re-creating an active one for the same item/site (this codebase's own documented Recurring Bug Class #3 — caught by this module's own Section 4 self-audit before lock).
• FK direction is always vertical-extension-points-into-core — nursery.item_profile/site_profile point INTO inventory.item/multi_loc.site; the core never points back (SCHEMA_CONVENTIONS.md §21).

---

nursery.item_profile

The vertical-extension replacement for the dropped inventory.item.plant_id column. Keyed to inventory.item via a composite (item_id, tenant_id) FK.

Tenant-scoped — standard RLS tenant-isolation policy on tenant_id.

item_id carries no direct FK of its own — the real constraint is the composite FK below (item_profile_item_tenant_fkey), which is what actually enforces both same-item and same-tenant agreement.

profile_type is deliberately single-value for now (CHECK IN ('plant')) — this phase RELOCATES the pre-existing plant-linkage concept out of inventory.item.plant_id, it does not add new nursery item-subtype capability. Widening this CHECK to cover other nursery item subtypes is a future business-capability decision, not part of this build.

care_attributes is a generic nursery-specific attribute bag, mirroring inventory.item.attributes's own pattern (mechanism (d) in SCHEMA_CONVENTIONS.md §21). Example shape: {"watering_notes": "keep soil moist, not soggy", "pot_size_override_in": 8}.

Partial unique index, not a plain UNIQUE — item_profile_tenant_id_item_id_unique is scoped WHERE deleted_at IS NULL, deliberately, since this table has deleted_at and a plain UNIQUE(tenant_id, item_id) would permanently block re-creating a profile for the same item after a soft delete (Recurring Bug Class #3, caught by this module's own Section 4 self-audit before lock).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
item_id	UUID	NOT NULL	—	No direct FK — see composite FK below
plant_id	UUID	nullable	—	FK → nursery_ref.plant
profile_type	text	NOT NULL	'plant'	CHECK IN (plant) — deliberately single-value for now, see note above
care_attributes	jsonb	nullable	'{}'	Generic nursery-specific attribute bag — example: {"watering_notes": "keep soil moist, not soggy", "pot_size_override_in": 8}
created_at / updated_at	timestamptz	NOT NULL	now()	updated_at maintained by a set_updated_at trigger
deleted_at	timestamptz	nullable	—	Standard soft-delete timestamp

Constraints:

• Composite FK item_profile_item_tenant_fkey (item_id, tenant_id) → inventory.item(id, tenant_id).
• Partial unique index item_profile_tenant_id_item_id_unique ON (tenant_id, item_id) WHERE deleted_at IS NULL — deliberately partial, not a plain UNIQUE (see note above).

Indexes: PK on id; the partial unique index above; index on tenant_id; partial index on plant_id WHERE plant_id IS NOT NULL.

RLS: standard tenant-isolation policy on tenant_id.

---

nursery.site_profile

The vertical-extension replacement for the dropped multi_loc.site.climate_zone_code column plus 3 nursery-only site_type values (yard/greenhouse/farm, also removed from multi_loc.site's own CHECK this phase). Keyed to multi_loc.site via a composite (site_id, tenant_id) FK.

Tenant-scoped — standard RLS tenant-isolation policy on tenant_id.

site_id carries no direct FK of its own — the real constraint is the composite FK below (site_profile_site_tenant_fkey).

hardiness_zone_id replaces multi_loc.site.climate_zone_code, now pointing at nursery_ref.climate_zone(code) instead of the old (now-moved) shared.climate_zone.

growing_environment_attributes is a generic attribute bag, same pattern as item_profile.care_attributes. Example shape: {"shade_cover_percent": 30, "irrigation_type": "drip"}.

Partial unique index, not a plain UNIQUE — same soft-delete-vs-unique reasoning as item_profile above (Recurring Bug Class #3).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
site_id	UUID	NOT NULL	—	No direct FK — see composite FK below
hardiness_zone_id	text	nullable	—	FK → nursery_ref.climate_zone(code)
nursery_site_type	text	NOT NULL	—	CHECK IN (yard,greenhouse,farm)
growing_environment_attributes	jsonb	nullable	'{}'	Generic attribute bag — example: {"shade_cover_percent": 30, "irrigation_type": "drip"}
created_at / updated_at	timestamptz	NOT NULL	now()	updated_at maintained by a set_updated_at trigger
deleted_at	timestamptz	nullable	—	Standard soft-delete timestamp

Constraints:

• Composite FK site_profile_site_tenant_fkey (site_id, tenant_id) → multi_loc.site(id, tenant_id).
• Partial unique index site_profile_tenant_id_site_id_unique ON (tenant_id, site_id) WHERE deleted_at IS NULL — deliberately partial, same reasoning as item_profile above.

Indexes: PK on id; the partial unique index above; index on tenant_id; partial index on hardiness_zone_id WHERE hardiness_zone_id IS NOT NULL.

RLS: standard tenant-isolation policy on tenant_id.