shared — Phase 2

Locked at 7 tables. Global reference data — vocabularies and small dictionaries shared by every tenant. Seeded at migration time; updated only by service_role and seed migrations.

Global rules for this schema:

• 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.
• Writable only via service_role and seed migrations — application code never INSERTs / UPDATEs shared.* rows at runtime.
• No deleted_at anywhere — deactivation is via is_active = false, never soft delete. Reference rows have permanent identity (an ISO code is the same code forever, even when retired).

Cross-Phase Foreign Keys (shared)

Column	Target	Notes
us_state.country_id	shared.country	Intra-schema; enforced at Phase 2 migration time.

Inbound (not built here): many later modules will reference shared tables — inventory.item → unit_of_measure and plant; crm.customer → country / us_state; etc. Those FK constraints are added when those modules' migrations run. No outbound forward-references from shared.

shared.country

ISO 3166-1 country dictionary. v1.0 ships with US active; other countries seeded as is_active = false for forward compatibility.

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	uuid_generate_v4()	PK
iso_code_2	char(2)	NOT NULL	—	UNIQUE — ISO 3166-1 alpha-2, e.g. 'US'
iso_code_3	char(3)	NOT NULL	—	UNIQUE — ISO 3166-1 alpha-3, e.g. 'USA'
name	text	NOT NULL	—	Display name
dial_code	text	nullable	—	E.164 dialing code, e.g. '+1'
is_active	boolean	NOT NULL	true	v1.0: only US active
sort_order	integer	NOT NULL	0	UI ordering

Indexes:

• PK on id
• UNIQUE on iso_code_2
• UNIQUE on iso_code_3

shared.us_state

US state / territory dictionary. Subordinate to country. v1.5+ may add provinces of other countries.

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	uuid_generate_v4()	PK
country_id	UUID	NOT NULL	—	FK → shared.country
code	char(2)	NOT NULL	—	State / territory code, e.g. 'CA'
name	text	NOT NULL	—	Display name
is_active	boolean	NOT NULL	true
sort_order	integer	NOT NULL	0

Indexes:

• PK on id
• UNIQUE on (country_id, code)

shared.currency

ISO 4217 currency dictionary. v1.0 ships with USD active only; multi-currency is schema-ready but not feature-enabled in v1.0.

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	uuid_generate_v4()	PK
iso_code	char(3)	NOT NULL	—	UNIQUE — ISO 4217, e.g. 'USD'
name	text	NOT NULL	—	Display name
symbol	text	NOT NULL	—	Currency symbol, e.g. '$'
decimal_places	integer	NOT NULL	2	For display / formatting
is_active	boolean	NOT NULL	true	v1.0: only USD active
sort_order	integer	NOT NULL	0

Indexes:

• PK on id
• UNIQUE on iso_code

shared.unit_of_measure

Global UoM vocabulary. Inventory-side conversions (cases → eaches, etc.) are NOT here — those live on inventory.item / inventory.package definitions. This table is the vocabulary only: the set of UoMs that exist.

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	uuid_generate_v4()	PK
code	text	NOT NULL	—	UNIQUE — e.g. 'each', 'lb', 'gal', 'cu_yd'
name	text	NOT NULL	—	Display name
symbol	text	nullable	—	Display symbol, e.g. 'ea', 'lb', 'gal', 'yd³'
uom_type	text	NOT NULL	—	CHECK IN ('count','weight','volume','length','area')
is_active	boolean	NOT NULL	true
sort_order	integer	NOT NULL	0

Indexes:

• PK on id
• UNIQUE on code

Scope note: unit conversion logic lives in inventory.item / inventory.package (case-of, pack-of, conversion factor). This table holds only the global UoM vocabulary.

shared.usda_hardiness_zone

USDA Plant Hardiness Zone dictionary — climate-zone reference for plant recommendations and tenant tenant_profile.usda_zone_primary defaults.

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	uuid_generate_v4()	PK
zone_code	text	NOT NULL	—	UNIQUE — e.g. '7a', '9b', '10a'
min_temp_f	integer	NOT NULL	—	Lower bound, °F
max_temp_f	integer	NOT NULL	—	Upper bound, °F
description	text	nullable	—	e.g. '0 to 5°F'
sort_order	integer	NOT NULL	0

Indexes:

• PK on id
• UNIQUE on zone_code

shared.plant

Thin global botanical reference (the Option C plant database from PROJECT_DECISIONS). Holds the facts that don't change per tenant: botanical name, slug, taxonomy, basic care attributes, provenance. Rich care content (full care guides, images, pests, companions) is NOT in v1.0 — the consumer plant-care AI generates / enriches care data at query time via Bedrock.

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

RLS: not applied — reference data.

Access control: readable by all authenticated users (consumer app + tenant app both consume it). Writable via service_role, seed migrations, and the AI enrichment pipeline (which writes through a service role). Tenant application code never writes here.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
botanical_name	text	NOT NULL	—	UNIQUE — e.g. 'Acer palmatum'. Scientific source of truth.
slug	text	NOT NULL	—	UNIQUE — URL-safe identifier for consumer-app plant URLs, e.g. vrida.app/plants/acer-palmatum. Normalized lowercase in service layer (not DB CHECK).
genus	text	nullable	—	e.g. 'Acer'
species	text	nullable	—	e.g. 'palmatum'
family	text	nullable	—	e.g. 'Sapindaceae'
plant_type	text	nullable	—	CHECK IN ('tree','shrub','perennial','annual','succulent','grass','vine','bulb','fern','aquatic','other')
usda_zone_min	text	nullable	—	Lower hardiness bound, e.g. '5a'. CHECK `(usda_zone_min IS NULL OR usda_zone_min ~ '^(1[0-3]
usda_zone_max	text	nullable	—	Upper hardiness bound, e.g. '9b'. CHECK `(usda_zone_max IS NULL OR usda_zone_max ~ '^(1[0-3]
sun_exposure	text	nullable	—	CHECK IN ('full_sun','partial_shade','full_shade')
water_needs	text	nullable	—	CHECK IN ('low','moderate','high')
mature_height_cm	integer	nullable	—	Mature height in centimeters
mature_width_cm	integer	nullable	—	Mature width / spread in centimeters
data_source	text	nullable	—	CHECK IN ('seed','ai_generated','manual'). Provenance — where the record came from
is_verified	boolean	NOT NULL	false	Trust flag — false for AI-generated rows that haven't been reviewed. UI must NOT render unverified data with the same authority as verified data.
is_active	boolean	NOT NULL	true
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on botanical_name
• UNIQUE on slug

Provenance vs trust: data_source tracks where the data came from (seed file, AI enrichment, manual curation). is_verified tracks whether to trust it for authoritative display. An AI-generated row may eventually be reviewed and flipped to is_verified = true without changing data_source. The two columns are deliberately decoupled.

shared.plant_common_name

Many common names per botanical plant — supports both consumer-app search and the AI import pipeline matching messy tenant input (e.g. "jap maple", "JAPANESE MAPLE", "Japanese Maple " all resolve to the same plant_id).

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	uuid_generate_v4()	PK
plant_id	UUID	NOT NULL	—	FK → shared.plant
common_name	text	NOT NULL	—	Display form, e.g. 'Japanese Maple'
name_normalized	text	NOT NULL	—	Lowercased / trimmed / collapsed whitespace, e.g. 'japanese maple'. Powers AI import matching. Normalized in service layer.
is_primary	boolean	NOT NULL	false	The main common name for the plant. One per (plant_id, language_code) — enforced by partial unique index.
language_code	char(5)	NOT NULL	'en-US'	Same shape as tenant_profile.language_code (e.g. 'en-US', 'es-MX')

Indexes:

• PK on id
• UNIQUE on (plant_id, name_normalized, language_code)
• UNIQUE on (plant_id, language_code) WHERE is_primary = true — at most one primary common name per plant per language (same pattern as multi_loc.site (tenant_id) WHERE is_primary = true)
• on (name_normalized) — cross-plant matching lookups for AI import ("does this fuzzy name resolve to any plant?")

---

shared — Design Patterns Summary

All tables are NOT tenant-scoped

Table	Active in v1.0
country	US only
us_state	all 50 + DC + territories
currency	USD only (schema is multi-currency-ready)
unit_of_measure	nursery-relevant set (each, lb, gal, cu_yd, etc.)
usda_hardiness_zone	full USDA zone list (1a–13b)
plant	seeded with the most common nursery plants; AI enriches over time
plant_common_name	seeded with primary common names; AI imports add aliases

All 7 tables share the same access model: readable by all authenticated users; writable only via service_role and seed migrations.

No deleted_at — deactivation via is_active

Reference rows have permanent identity (an ISO code is the same code forever, even when retired). Deactivation is a display / picker concern, not a soft delete:

• All 7 tables carry is_active boolean NOT NULL.
• 6 of 7 default is_active = true (active by default).
• plant.is_active defaults true; plant.is_verified defaults false (active for picking, but flagged as untrusted until reviewed).

No updated_at on the truly-immutable dictionaries

Five tables omit updated_at (and created_at) entirely — they're seeded once and only rarely edited via a migration:

• country, us_state, currency, unit_of_measure, usda_hardiness_zone

Two tables carry created_at + updated_at because the AI enrichment pipeline writes to them at runtime:

• plant (AI may add new rows or update facts)
• plant_common_name (AI may add aliases discovered during import)

Plant database — Option C (thin global)

• v1.0: facts and names. Botanical name (truth), slug (URL), taxonomy, basic care attributes, provenance, hardiness bounds.
• v1.5+: rich care content (care guides, images, pests, companions, propagation) deferred — generated at query time by the consumer plant-care AI via Bedrock and cached in module-specific tables (likely customer_app or ai schema) rather than bloating shared.

Normalized columns (service-layer responsibility)

Column	Normalization	Used for
plant.slug	lowercase, hyphenated	Consumer-app plant URLs (vrida.app/plants/{slug})
plant_common_name.name_normalized	lowercase, trimmed, whitespace collapsed	AI import fuzzy matching ("jap maple" → "japanese maple")

Normalization is applied in SharedService.createPlant / addCommonName, NOT via DB CHECK constraints — keeps the rules in code where they can evolve and be tested.

Deferred to v1.5: plant-browsing filter indexes

As plant grows to thousands of AI-enriched rows, filter queries from the consumer-app plant browser will need supporting indexes:

• (plant_type) WHERE is_active = true — "show me all trees"
• (usda_zone_min, usda_zone_max) — "plants that survive zone 7"
• (is_verified) — admin queue: "review unverified AI-generated rows"

Deferred to v1.5 with the consumer-app plant browser. v1.0 access paths (direct ID, botanical_name, slug, common-name fuzzy match) are all already indexed.

is_verified vs data_source (provenance vs trust)

• data_source ∈ ('seed', 'ai_generated', 'manual') — where the row came from.
• is_verified ∈ (true, false) — whether to trust the row for authoritative display.
• An AI-generated row can be reviewed and flipped to is_verified = true without changing data_source — the provenance remains accurate, the trust flag captures the curation step.
• UI must surface unverified data with a disclaimer (e.g. "AI-suggested, not yet reviewed"). Don't conflate the two.

---