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

4 tables, 61 columns — created 2026-07-18, Phase 2 (Nursery Vertical Extraction), by moving these 4 tables verbatim out of shared via ALTER TABLE ... SET SCHEMA (data/indexes/constraints/triggers preserved exactly — see PROJECT_DECISIONS.md #70). Global, non-tenant-scoped, Vrida/AI-curated botanical reference dictionary — the nursery vertical's own <vertical>_ref global dictionary (mechanism (a) in SCHEMA_CONVENTIONS.md §21's governing principle). Depends on shared for one cross-schema foundation-to-foundation FK (plant_common_name.locale_code → shared.locale.code) and identity for actor-attribution FKs (continuing the pattern established when these tables lived in shared).

Global rules for this schema (unchanged from when these tables lived in shared):

• NOT tenant-scoped — no tenant_id on any table; no RLS applied.
• Readable by all authenticated users — app_user and service_role both have SELECT.
• Write access: see the "Write access" note at the end of this file.
• No deleted_at anywhere — deactivation is via is_active = false, never soft delete, except plant/plant_common_name/plant_climate_zone, which have no lifecycle toggle at all.
• Natural-key PKs, with one deliberate exception (plant, UUID) — see PROJECT_DECISIONS #17 for the full rationale and the SCHEMA_CONVENTIONS.md conflict this creates (flagged, not silently resolved).

---

nursery_ref.climate_zone

Multi-system climate/hardiness zone dictionary — USDA, RHS, AHS Heat, Australian, EU. Seeded at 60 zones across all 5 systems.

NOT tenant-scoped — reference data. No tenant_id, no deleted_at.

RLS: not applied — reference data.

Confidence varies by system: USDA (26 zones, 1a–13b) and RHS (9 zones, H1a–H7) are high-confidence, well-documented standards. AHS Heat (12 zones) is high-confidence for its day-count ranges. Australian (7 zones) and EU (6 zones) are lower-confidence — there is no single official "EU hardiness zone" standard the way USDA is for the US; these are seeded as simplified, commonly-cited approximations and should be reviewed against an authoritative source before being treated as definitive.

AHS Heat measures a different metric: average annual days above 30°C/86°F (a day-count), not a temperature range. min_temp_c/max_temp_c are NULL for system='ahs_heat' rows; the day-count range is documented in description only.

Column	Type	Nullable	Default	Constraints / Notes
code	text	NOT NULL	—	PK — format '<system>:<zone_code>', e.g. 'usda:9a'
system	text	NOT NULL	—	CHECK IN (usda,rhs,ahs_heat,australian,eu)
zone_code	text	NOT NULL	—	e.g. '9a', 'H4'
description	text	nullable	—
min_temp_c / max_temp_c	numeric	nullable	—	NULL for ahs_heat (see above)
sort_order	integer	NOT NULL	0
is_active	boolean	NOT NULL	true
created_at / updated_at	timestamptz	NOT NULL	now()

Indexes: PK on code; UNIQUE on (system, zone_code).

---

nursery_ref.plant

Thin global botanical reference — Option C from v1 PROJECT_DECISIONS (carried forward): facts and names only, rich care content deliberately deferred to AI enrichment at query time. Seeded at 114 common nursery plants (data_source='seed', is_verified=true).

NOT tenant-scoped — global botanical reference. No tenant_id, no deleted_at.

RLS: not applied — reference data.

Access control: readable by all authenticated users. Writable via service_role, seed migrations, and the AI enrichment pipeline (data_source='ai_generated', is_verified=false — see the AI→nursery_ref seam in CROSS_MODULE_CONTRACTS.md). Tenant application code never writes here — as of 2026-07-18 (Phase 2, PROJECT_DECISIONS.md #70), authenticated has zero INSERT/UPDATE/DELETE grant on this table, live-reproduced.

UUID PK — the ONE deliberate exception to this schema's natural-key convention. Botanical/taxonomic names are not permanently stable (real taxonomic revisions happen); a natural-key PK would force every downstream FK to cascade on a reclassification. See PROJECT_DECISIONS #17.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK — UUID, the natural-key exception
botanical_name	text	NOT NULL	—	UNIQUE — e.g. 'Acer palmatum'
slug	text	NOT NULL	—	UNIQUE — URL-safe, e.g. 'acer-palmatum'. Also the target of the locked consumer_interest.interest_ref seam.
genus / species / cultivar / family	text	nullable	—
plant_type	text	nullable	—	CHECK IN (tree,shrub,perennial,annual,succulent,grass,vine,bulb,fern,aquatic,other)
sun_exposure	text	nullable	—	CHECK IN (full_sun,partial_shade,full_shade)
water_needs	text	nullable	—	CHECK IN (low,moderate,high)
mature_height_cm / mature_width_cm	integer	nullable	—
data_source	text	nullable	—	CHECK IN (seed,ai_generated,manual)
is_verified	boolean	NOT NULL	false	trust flag, decoupled from data_source (provenance vs. trust)
created_by_actor_id	UUID	nullable	—	FK → identity.actor
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor
reviewed_at	timestamptz	nullable	—
is_active	boolean	NOT NULL	true
created_at / updated_at	timestamptz	NOT NULL	now()

Indexes: PK on id; UNIQUE on botanical_name; UNIQUE on slug; partial index on review_status WHERE review_status = 'pending' (review-queue support).

CHECK chk_plant_verified_review_consistency: is_verified = false OR review_status IN ('not_required','approved') — a row cannot be marked verified while still pending or rejected review.

---

nursery_ref.plant_common_name

Locale-scoped common names per plant. Seeded at 70 rows for the most common plants in the starter set.

NOT tenant-scoped — reference data. No tenant_id, no deleted_at.

RLS: not applied — reference data.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
plant_id	UUID	NOT NULL	—	FK → nursery_ref.plant
locale_code	text	NOT NULL	—	FK → shared.locale — cross-schema foundation-to-foundation FK, permitted under SCHEMA_CONVENTIONS.md §1's one-way rule since both nursery_ref and shared are non-tenant-scoped foundation-layer schemas
common_name	text	NOT NULL	—
name_normalized	text	NOT NULL	—	Service-layer normalized (lowercase/trim); powers AI-import fuzzy matching
is_primary	boolean	NOT NULL	false
data_source	text	nullable	—	CHECK IN (seed,ai_generated,manual)
is_verified	boolean	NOT NULL	false
created_by_actor_id	UUID	nullable	—	FK → identity.actor
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor
reviewed_at	timestamptz	nullable	—
created_at / updated_at	timestamptz	NOT NULL	now()

Indexes: PK on id; UNIQUE on (plant_id, name_normalized, locale_code); UNIQUE on (plant_id, locale_code) WHERE is_primary = true; index on name_normalized; partial index on review_status WHERE review_status = 'pending'.

CHECK chk_plant_common_name_verified_review_consistency: same shape as plant's — see above.

---

nursery_ref.plant_climate_zone

Per-system hardiness range for a plant — a plant may be hardy across multiple systems simultaneously. Seeded at 68 rows (USDA ranges only).

NOT tenant-scoped — reference data. No tenant_id, no deleted_at.

RLS: not applied — reference data.

system is denormalized from min_zone_code/max_zone_code's embedded system prefix, for direct filtering without a join. Not DB-enforced that they match (would need a trigger) — app-enforced at write time. Verified consistent in the current seed (0 mismatches) and covered by a regression test (nursery_ref-schema.spec.ts section E).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
plant_id	UUID	NOT NULL	—	FK → nursery_ref.plant
system	text	NOT NULL	—	CHECK IN (usda,rhs,ahs_heat,australian,eu)
min_zone_code / max_zone_code	text	NOT NULL	—	FK → nursery_ref.climate_zone
data_source	text	nullable	—	CHECK IN (seed,ai_generated,manual)
is_verified	boolean	NOT NULL	false
created_by_actor_id	UUID	nullable	—	FK → identity.actor
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
review_reason	text	nullable	—
reviewed_by_actor_id	UUID	nullable	—	FK → identity.actor
reviewed_at	timestamptz	nullable	—
created_at / updated_at	timestamptz	NOT NULL	now()

Indexes: PK on id; UNIQUE on (plant_id, system); partial index on review_status WHERE review_status = 'pending'.

CHECK chk_plant_climate_zone_verified_review_consistency: same shape as plant's — see above.

---

Write access: As of 2026-07-18 (Phase 2, PROJECT_DECISIONS.md #70), authenticated has SELECT-only access on all 4 tables in this schema (INSERT/UPDATE/DELETE REVOKEd), with ALTER DEFAULT PRIVILEGES set so any future table added to this schema inherits the same read-only-by-default posture automatically. Live-reproduced via genuine A/B (a scoped, rolled-back GRANT INSERT proved the pre-lockdown posture would have allowed the write; post-lockdown, the same INSERT correctly rejects with 42501).