Module Spec — shared (module #3)

1. Purpose

Global, non-tenant reference data used across every future module: currency, country, administrative subdivisions, language, locale, unit of measure, multi-system climate/hardiness zones, and a thin botanical plant reference. Vertical-neutral — none of these tables are nursery-specific except plant/plant_common_name/plant_climate_zone.

2. Ownership

Owns all reference vocabularies listed above. Depends on nothing (it's foundation data, seeded before any tenant exists). No other module owns or duplicates this data — the standing cross-module pattern is "reference, don't copy" (CROSS_MODULE_CONTRACTS.md).

3. Layer & Dependencies

Foundation layer, alongside platform and identity. shared now depends on identity (new as of 2026-07-06, via created_by_actor_id FKs on plant, plant_common_name, and plant_climate_zone — see PROJECT_DECISIONS #19), in addition to its prior zero-dependency status. Every future tenant-scoped module (inventory, crm, pricing, etc.) is expected to FK into shared tables rather than duplicate reference vocabularies. As of Remediation Phase 4 (2026-07-08, PROJECT_DECISIONS #40), crm and purchasing also depend on shared for the new payment_terms_catalog FK seam (see §8).

4. Tables

12 tables, 136 columns (locked 2026-07-05 at 101 columns; +7 from the 2026-07-06 autonomy-first backfill; +12 from the same-day 2026-07-06 review-seam fix, PROJECT_DECISIONS #21 — bringing the true pre-Phase-4 baseline to 120, not the 108 this doc previously (and incorrectly) stated; +16 from Remediation Phase 4, 2026-07-08, PROJECT_DECISIONS #40. See §10a below for the full accounting of the 108→120 correction, and §10b for Phase 4's own additions). See docs/database/schema_docs/shared.md for full column-level detail. Summary:

Table	PK style	Rows seeded
currency	natural (ISO 4217 code)	152
language	natural (ISO 639-1 code)	94
country	natural (ISO 3166-1 alpha-2)	193
locale	natural (BCP 47 code)	36
administrative_region	natural (ISO 3166-2 code)	186
unit_of_measure	natural (short code)	31
climate_zone	natural (composite string code)	60
plant	UUID (the one exception)	114
plant_common_name	UUID (join/fact table)	70
plant_climate_zone	UUID (join/fact table)	68
exchange_rate	UUID (surrogate — see §9b)	0
payment_terms_catalog	UUID	10

Natural-key PK convention: 7 of 12 tables use their natural code as the primary key rather than a UUID — a deliberate deviation from the platform/identity convention, scoped to this module. SCHEMA_CONVENTIONS.md §5 does not explicitly authorize this (it lists "UUID or smallint" for lookup tables); the deviation is recorded, not silently applied — see PROJECT_DECISIONS #17. The 2 tables added in Remediation Phase 4 both use UUID PKs rather than extending this natural-key exception list — see §9b for why exchange_rate in particular deliberately does NOT get a natural composite PK despite superficially fitting the pattern.

Honest seed-scope note: administrative_region and plant were both scaled down from larger originally-discussed targets (~3-4K global subdivisions; "a few hundred" plants) to a smaller, high-confidence set, rather than generating volume that couldn't be verified accurately from memory without a live authoritative source. See PROJECT_DECISIONS #17 and the migration file's own comments for exact scope per table.

Autonomy-first backfill (2026-07-06): plant_common_name and plant_climate_zone each gained data_source, is_verified, and created_by_actor_id, matching plant's existing shape. All 70 existing plant_common_name rows and 68 existing plant_climate_zone rows were backfilled to data_source='seed', is_verified=true in the same migration, consistent with plant's own seed rows. See PROJECT_DECISIONS #19 for the full cross-module rationale.

Review-seam fix (2026-07-06, same day): plant, plant_common_name, and plant_climate_zone each gained review_status, review_reason, reviewed_by_actor_id, reviewed_at (+4 columns × 3 tables = +12 columns) — see PROJECT_DECISIONS #21 ("FIX 1"). This addition was never folded into this doc's stated running column total at the time — the "108 columns" figure this doc carried until this update only ever reflected the earlier +7 autonomy-backfill (101 + 7 = 108), silently missing this later +12. See §10a for the full correction.

5. Capabilities

Pure reference-data lookup. No business logic, no state machine, no lifecycle beyond is_active toggling. The only non-trivial "capability" is the AI enrichment pipeline's write path into plant/plant_common_name (see Cross-Module Seams).

6. Service Contract — SharedService

Not built this pass. No HTTP controllers, no service class exists yet for shared — this module ships as schema + seed data only. Downstream modules query shared.* tables directly via Drizzle for now; a SharedService abstraction (lookups, the plant_climate_zone.system consistency validation noted below) is deferred to whenever the first consuming module (inventory, pricing, crm) is built. See OPEN_ITEMS.

Known write-time validation gap, not DB-enforceable: plant_climate_zone.system must match the system embedded in its own min_zone_code/max_zone_code (e.g. a row with system='usda' must have both zone codes prefixed 'usda:'). Nothing in the DB enforces this today (would require a trigger) — whichever service eventually writes to this table must validate it at write time. Verified consistent in the current seed data (0 mismatches, covered by a regression test) but not structurally guaranteed going forward.

7. Data-Flow / Population Model

• Seed migration (packages/db/migrations/20260705010000_shared_seed.sql) populates all 10 tables at build time — this is the only population path today.
• AI enrichment (future): AIService writes new plant rows (data_source='ai_generated', is_verified=false) and plant_common_name aliases via service_role, per the already-locked CROSS_MODULE_CONTRACTS.md seam. Not built this pass — schema is ready for it.
• No tenant or application-code writes — every table in this module is seed/service_role-only.

8. Cross-Module Seams

Already locked in CROSS_MODULE_CONTRACTS.md (pre-dating this module's own build):

• AI → Shared: AIService writes shared.plant (data_source='ai_generated', is_verified=false) + shared.plant_common_name aliases via service_role.
• Consumer → Shared: consumer_address.country_code → shared.country (enforced FK); consumer_interest.interest_ref → shared.plant.slug (loose text ref, app-validated, not FK-enforced).
• Shared → Identity (new, 2026-07-06): plant.created_by_actor_id, plant_common_name.created_by_actor_id, plant_climate_zone.created_by_actor_id → identity.actor, part of the autonomy-first backfill's agent-as-actor attribution pattern. See PROJECT_DECISIONS #19.
• Shared → CRM/Purchasing (new, 2026-07-08, Remediation Phase 4 Item 17b): crm.customer.payment_terms_id, purchasing.vendor.payment_terms_id, purchasing.purchase_order.payment_terms_id → shared.payment_terms_catalog.id (all nullable FKs). An additive interim step alongside the pre-existing crm.customer.credit_terms CHECK-enum, not yet a replacement for it — see §10b. See PROJECT_DECISIONS #40.
• Billing → Shared (new, 2026-07-08, Remediation Phase 4 Item 16, indirect): shared.exchange_rate has no direct FK consumer, but enables billing.ar_payment_application's new currency-agreement trigger (billing.validate_ar_payment_application_currency()), which validates ar_payment/ar_charge/ar_account currency-code agreement. See PROJECT_DECISIONS #40 and docs/modules/module_spec/billing.md.

Expected future seams (not yet built, no module to seam against yet): inventory.item → shared.unit_of_measure / shared.plant; crm.customer → shared.country / shared.administrative_region.

9. AI Capability Discovery (Part D — 2026-07-06)

This module previously only received the narrower schema-translation autonomy pass (2026-07-06: automation_source-equivalent columns via data_source/is_verified, created_by_actor_id agent-as-actor FKs on plant/plant_common_name/plant_climate_zone). This section runs the full Part D module-walk (AI_CAPABILITY_PLANE.md) against shared for the first time. As expected for a non-tenant-scoped, seed/service_role-only global reference schema with no business logic, most tenant-operational Part D questions (routing, error-prevention on transactions, offline, channel sync) are genuine "none" answers here — this module's real AI surface is narrow and centers on the plant/plant_common_name/plant_climate_zone AI-enrichment seam already locked in CROSS_MODULE_CONTRACTS.md.

D1–D15 table

Question	Applies/Ruled-out	Specific answer	Triggered items
D1. Capture targets	Ruled out	No capture bar / command canvas creates records in shared. All 10 tables are seed/service_role-only (see docs/database/schema_docs/shared.md "Global rules"); the only non-seed write path is the AI→Shared enrichment seam (B1d-adjacent but not a user-facing capture surface), and even that is app/service-triggered, not a user typing/speaking a shared transaction into existence.	B1
D2. Routing rules	Ruled out	No smart table/status placement — there is no status/lifecycle machinery in this module (is_active boolean toggle only, no state machine per the module spec's "Capabilities" section). Nothing routes here; downstream modules route into their own tables and merely FK-reference shared rows.	B1c
D3. Maintenance	Applies	Rot points: (a) plant.botanical_name/slug uniqueness collisions or near-duplicate botanical names entered by different sources (seed vs. AI-generated) creating synonym drift; (b) plant_common_name duplicate common-name variants across locales (already partially guarded by the name_normalized unique index + fuzzy-match support); (c) stale is_verified=false AI-generated rows that never get reviewed and pile up; (d) plant_climate_zone.system vs. min_zone_code/max_zone_code prefix drift — a known write-time-only validation gap (see module spec §6). Hygiene agent behavior: dedup near-duplicate plant/plant_common_name rows (embedding or normalized-name similarity) as draft-only proposals (never auto-merge — merging plant taxonomy affects downstream inventory.item and consumer_interest.interest_ref FKs); gap-fill missing plant_common_name locale coverage from AI enrichment (already the locked seam); flag plant/plant_common_name/plant_climate_zone rows stuck at is_verified=false past some age as a review-nudge (draft/surface-only, not auto-repair). Safe auto-repair candidates: none identified that are both safe and reversible enough for L5 — even filling a blank sun_exposure/water_needs on a manual-sourced row touches taxonomy-adjacent trust, so this stays draft-only rather than auto-applied.	B3
D4. Error-prevention	Ruled out	This module has no transactional actions to block — no sale, no PO, no payment. The one candidate ("don't let plant_climate_zone.system diverge from its zone-code prefixes") is a deterministic write-time validation, already documented as a SharedService responsibility (module spec §6), not an AI-plane error-prevention capability — it's plain code, not B4's warn/block-at-point-of-action pattern over a transaction.	B4
D5. Negative-space	Applies	Two real gap patterns: (a) a plant row with zero plant_common_name rows in any locale — a plant with only a Latin name and no consumer-facing name is a usable-but-incomplete record (detecting query: plant LEFT JOIN plant_common_name WHERE plant_common_name.id IS NULL); (b) a plant row with zero plant_climate_zone rows — a plant with no hardiness range recorded at all, which silently breaks any future "will this survive in zone X" feature (detecting query: plant LEFT JOIN plant_climate_zone WHERE plant_climate_zone.id IS NULL). Both are real today: only 68 of 114 seeded plant rows have a plant_climate_zone row (per the schema doc's seed-scope note), and only 70 rows' worth of common names exist against 114 plants — so gap (b) alone currently affects at least 46 plants, and gap (a) is very likely nonzero too (not independently re-verified in this pass, but the seed counts alone establish incompleteness is real, not hypothetical).	B5
D6. Decision-support	Ruled out	None. There is no transactional history in shared to forecast — no sales, no demand signal, nothing seasonal. Any "coverage trending" metric (e.g. "% of plants missing climate-zone data") is a static completeness gap (D5's territory), not a forecast/anomaly-over-time (B6's territory).	B6
D7. Autonomy boundary	Applies	See the full per-action table below.	A4, A13
D8. Evidence sources	Applies (narrow)	The only evidence-like input is the AI enrichment pipeline itself: an external AI service call (not a user photo/PDF/voice capture) produces plant/plant_common_name field values from some external source (e.g. web/knowledge lookup) and writes them via service_role with data_source='ai_generated', is_verified=false. Abuse risk: a poisoned or hallucinating enrichment source could write a bogus/adversarial botanical_name, plant_type, or care-fact that later feeds tenant-facing content or CRM consumer_interest.interest_ref matching. High-risk fields: botanical_name/slug (identity-defining, FK target for consumer_interest.interest_ref and future inventory.item), plant_climate_zone.min_zone_code/max_zone_code (a wrong hardiness range is actionable bad advice that could cause a tenant customer to buy a plant that dies in their climate). None of these fields currently have a "must match trusted master data" deterministic check the way A11's vendor-bank-vs-master pattern works elsewhere — is_verified=false is the only signal gating trust, and nothing currently forces human review before an unverified row is readable by tenants (see gap below).	B1d, A11
D9. Reconciliation pairs	Applies (narrow)	(a) plant_climate_zone.system should always match the system prefix embedded in its own min_zone_code/max_zone_code (e.g. system='usda' rows must have both zone codes prefixed 'usda:') — already flagged in the module spec and schema doc as a known write-time-only gap, verified consistent in seed data (0 mismatches) but not structurally guaranteed. This is the clearest reconciliation pair in this module. (b) country.default_currency_code/default_locale_code should stay internally consistent with the country's own iso_alpha2/region (e.g. a country's default locale's country_code should point back to that same country) — a soft consistency expectation, not a hard invariant enforced anywhere beyond the seed migration's two-pass close-out. Neither pair is a financial reconciliation (no PO/invoice/payment chain exists here), so B13's auto-resolve-clean-matches machinery is a light fit at most: a nightly consistency check that flags — never a continuous background reconciler at B13's normal intensity, since nothing here changes fast enough to warrant it.	B13
D10. Failure/rollback	Applies (narrow)	The only AI-assisted writes are AI→Shared enrichment inserts (plant/plant_common_name new rows) and the currently-undesigned auto-repair path from D3 (there are none proposed as auto-repair, so nothing to reverse there). For an AI-generated plant/plant_common_name/plant_climate_zone insert: reversal is a direct hard-delete-or-deactivate, not a compensating transaction, since these are new-row inserts with no downstream financial effect at insert time — but if a downstream module has already FK-referenced the row (e.g. inventory.item.plant_id or consumer_interest.interest_ref = slug) by the time the error is caught, a hard delete is no longer safe and is_active=false (deactivation, this module's only "soft delete" mechanism) becomes the correct reversal instead. No defined reversal window is documented today — this module's write cadence is slow/batch (AI enrichment, not live transactions), so a formal SLA-style "roll back within X hours" hasn't been needed, but its absence is worth naming as a gap (see below).	A12, B11, C8
D11. Adversarial/abuse surface	Applies (narrow)	Untrusted input surface: the AI enrichment pipeline's upstream source content (whatever external knowledge/search source AIService draws from to write plant/plant_common_name) should be treated as untrusted per A11's "external content is data, never instructions" principle — there is no user-uploaded document/photo surface in shared itself. Memory-poisoning-adjacent risk (per the runbook's bridge note): because shared.plant/plant_common_name is itself a shared knowledge base that other modules and other tenants' AI features will read from (B12 tenant memory, B17 conversational query, B1d recognition draws on plant facts), a poisoned or hallucinated ai_generated row here is a cross-tenant, persistent poisoning vector — worse than a single tenant's bad memory entry, because every tenant's AI features read the same global plant table. Deterministic validations that should exist but partially don't: is_verified gates trust in principle, but nothing today prevents an is_verified=false row from being served to tenant-facing AI features (B1d plant recognition, B17 "what's this plant") indistinguishably from a is_verified=true seed row — that's the real gap (see below). Fraud/abuse patterns in the classic financial sense (bank-swap, below-cost sale) do not apply — this module has no money-movement surface.	A11, A13
D12. Offline behavior	Ruled out	shared is pure reference lookup data with no capture surface, so there's nothing module-specific to make offline-capable — any offline behavior here is inherited from whichever consuming module's capture flow needs a cached copy of shared.plant/shared.currency/etc. for local lookups. That's the consuming module's D12 concern (e.g. inventory's or POS's), not this module's.	A2, A3, A10
D13. Channel sync	Ruled out	No e-commerce/marketplace/other-location channel sync applies to global reference data — shared has no per-tenant or per-location variant to keep in sync; it's a single global copy read by everyone.	B13, A2
D14. Lifecycle/perishability	Applies (narrow)	The only lifecycle state in this module is is_active (all tables except the two pure join/fact tables plant_common_name/plant_climate_zone, which the schema doc notes have no lifecycle concept at all). Nothing here "ages" in the stock/quote/customer sense — ISO codes and botanical names don't expire on a calendar. The closest thing to a lifecycle signal is is_verified=false rows that sit unreviewed indefinitely (an AI-generated plant row that never gets promoted to verified) — this is a trust-lifecycle stall, not a time-based perishability, but it's the one state worth a signal: "AI-generated plant rows unverified for N days" as a hygiene-agent surface item (ties back to D3). Taxonomic reclassification (a botanical name changing under revision) is explicitly why plant uses a UUID PK rather than natural key (PROJECT_DECISIONS #17) but there is no detection mechanism for when a reclassification has occurred — that's an external-fact-change, not a modeled lifecycle state, and out of scope for this module to detect on its own.	B5, B6, B13
D15. Capture modality	Ruled out	No user-facing capture modality exists for this module's own tables — no one scans, photographs, speaks, or form-fills a currency or plant row into existence in the tenant-facing product. The nearest adjacent modality is B1d vision-based plant recognition in other modules (e.g. photographing a plant tag at receiving), which reads shared.plant/plant_common_name to resolve a match, but does not capture into shared itself.	B1, B1d, A2, A11

Capabilities Recorded

Applies (with real content):

• B3 (self-maintaining master data) — dedup/gap-fill on plant/plant_common_name/plant_climate_zone, draft-only.
• B5 (negative-space detection) — missing-common-name and missing-climate-zone gaps on plant.
• A4/A13 (autonomy boundary / SoD) — see D7 table below.
• B1d/A11 (evidence sources / adversarial input) — narrow, via the AI-enrichment pipeline's upstream content, not a user capture surface.
• B13 (reconciliation) — narrow, plant_climate_zone.system-vs-zone-code-prefix consistency only; no financial reconciliation pairs exist.
• A12/B11/C8 (rollback) — narrow, applies to AI-generated inserts only; no compensating-transaction pattern needed since these are additive, not financial.
• B5/B6/B13 (D14 lifecycle) — narrow, only the is_verified trust-lifecycle stall qualifies; no time-based perishability.

Ruled out (with reason):

• B1/B1c (capture & routing) — no user-facing capture surface into shared; all writes are seed/service_role/AI-enrichment only.
• B4 (guided error-prevention) — no transactional actions exist to warn/block; the one candidate check is deterministic write-time validation owned by a future SharedService, not a B4-shaped point-of-action guard over a transaction.
• B6 (decision support / forecasting) — no transactional history exists to forecast over; nothing seasonal or trend-shaped in reference data.
• A2/A3/A10 (D12 offline) — no module-specific capture flow to make offline-capable; inherited by consuming modules.
• B13/A2 (D13 channel sync) — no per-tenant/per-location variant of global reference data to sync.
• B1/B1d/A2/A11 (D15 capture modality) — no capture modality for this module's own tables; adjacent modules' vision recognition reads shared, doesn't capture into it.
• B2, B7, B8, B9, B12, B14, B15, B16, B17, B18, B19, B20 — not triggered by any D-question for this module: no ambient-analyst surface (B2, nothing operationally urgent enough), no progressive-disclosure config (B7, this module has no tenant-facing config at all — it's global), no outcome planning (B8, deferred anyway and no multi-step goal touches pure reference data), no approval-queue-native item beyond what D7 already covers (B9 — the AI-enrichment review nudge could feed B9 but that's the consuming/reviewing surface's concern, not a new capability this module needs), no tenant operating memory (B12 — this module is not tenant-scoped, has no "how this tenant operates" preferences), no simulation (B14 — nothing to model), no bulk import specific to this module (B15 — the seed migration already is the bulk-load path; no tenant ever bulk-imports into shared), no outbound drafting (B16 — no comms originate from this module), no conversational query/report/assistant/marketing capability is module-specific here (B17–B20 — these would be invoked by other modules querying shared as reference data, not shared itself owning the capability).

D7 — Per-action autonomy boundary table

Action	Authority level	Threshold / condition	Notes
AI-enrichment: insert new plant row (data_source='ai_generated')	draft-only (L3, is_verified=false)	None — every AI-generated row lands unverified regardless of confidence	Matches the already-locked AI→Shared seam; is_verified is the human-review gate, but nothing yet forces a human touchpoint before the row is readable (see gap below) — the write itself is correctly draft-shaped (unverified), but there's no explicit approval-queue item generated for it today
AI-enrichment: insert new plant_common_name alias	draft-only (L3, is_verified=false)	None	Same as above
AI-enrichment: insert new plant_climate_zone row	draft-only (L3, is_verified=false)	None	Same as above; also carries the unenforced system-vs-zone-code-prefix invariant, so this is actually a slightly higher-risk draft than the other two — a bad write here is closer to "wrong advice" than "missing name"
Hygiene agent: merge/dedup near-duplicate plant/plant_common_name rows (D3)	draft-only (L3)	Never auto-merge, at any confidence	Merging taxonomy affects downstream FKs (inventory.item, consumer_interest.interest_ref); a bad auto-merge is much harder to reverse than a bad insert
Hygiene agent: gap-fill a blank enrichable field (sun_exposure, water_needs, mature_height_cm, etc.) on an existing row	draft-only (L3)	Never auto-apply under this walk's findings	Even though B3's plane text allows "safe auto-repair within bounds" generically, no field in plant was judged safe enough for L5 here — a wrong care-fact is closer to "bad advice with downstream consumer/tenant impact" than a typo-fix, and there is no dollar-value or quantity threshold in this module to bound the blast radius the way A4 examples do elsewhere.
Toggle is_active=false on any row (deactivation)	needs-approval (L4, human-initiated only)	N/A — this walk found no case where AI should decide to retire a reference-data row on its own	Deactivating a currency/country/plant is a structural decision (e.g. a currency ceasing to exist) that should always be a deliberate human/service_role action, never AI-initiated
Write to plant_climate_zone.system/min_zone_code/max_zone_code in a way that could diverge from each other	never for AI to write inconsistently	N/A	This is a deterministic validation the future SharedService must enforce at write time (module spec §6) — not an authority-ladder question but a hard validation gate that applies equally to AI and human writers
Any write to currency, language, country, locale, administrative_region, unit_of_measure (the 6 pure ISO/code tables)	never for AI, uninitiated	N/A	These are stable ISO/BCP-47 standards-backed tables with no AI-enrichment seam at all (only plant/plant_common_name/plant_climate_zone have one); no capability in Part B proposes AI writing to them, and this walk found no reason to open one

Real gaps found

1. CLOSED 2026-07-06. Missing: an explicit review/approval-queue surface for is_verified=false AI-generated shared.plant/plant_common_name/plant_climate_zone rows. All 3 tables now have review_status (CHECK IN not_required/pending/approved/rejected, DEFAULT not_required), review_reason, reviewed_by_actor_id (FK → identity.actor), reviewed_at — mirrors platform.contract's seam exactly. A new CHECK (chk_<table>_verified_review_consistency: is_verified = false OR review_status IN ('not_required','approved')) makes the trust flag and the review workflow mutually consistent at the DB level — something platform.contract's own seam does not yet have. A partial index on review_status = 'pending' per table supports the future review queue. Nullable/defaulted; all 252 existing rows (114 plant + 70 plant_common_name + 68 plant_climate_zone, all data_source='seed') satisfy the new CHECK via review_status='not_required' — zero backfill required. See PROJECT_DECISIONS.md #21, schema_docs/shared.md.
2. Missing: no reconciliation/consistency check job (even a scheduled one) actually implemented for the two known-but-unenforced invariants — plant_climate_zone.system vs. its own zone-code prefixes, and the country⇄locale circular-reference consistency. Both are currently "verified consistent in seed data, not structurally guaranteed going forward" (per the schema doc's own language) with no scheduled check named anywhere to catch future drift once SharedService or the AI-enrichment pipeline starts writing here for real. Urgency: deferrable — low write volume today (seed-only), no service layer exists yet to even perform writes outside seed/migration, and the module spec already names this as a known write-time validation responsibility for the future SharedService; it should be picked up when that service is built, not before.
3. Missing: no defined reversal window for AI-generated inserts (D10). Unlike transactional modules where A12 examples give a concrete "roll back within X" guarantee, shared has no stated SLA for how long an AI-generated plant/plant_common_name/plant_climate_zone row can be cleanly hard-deleted before downstream FKs (from inventory.item, consumer_interest.interest_ref) make deletion unsafe and force a deactivate-instead-of-delete correction. Urgency: deferrable — no downstream module has been built yet to actually create such an FK, so the window is currently infinite in practice; worth naming a concrete policy once inventory (the first real consumer) exists.

Item 1 was closed with a schema migration on 2026-07-06 (see above). Items 2 and 3 remain documented gaps for a future migration/decision, not applied here.

10. Remediation Phase 4 (2026-07-08)

See PROJECT_DECISIONS.md #40 for the full cross-module entry (Phase 4 of the 4-phase remediation plan, items 14–20 plus closing the Phase 3 anonymous-return open decision). This module's own involvement is 2 new tables (Items 16 and 17b), both additive — no existing table, column, or constraint was altered or dropped.

10a. The pre-existing 108 → 120 baseline correction (NOT a Phase 4 change)

§4 above previously stated the pre-Phase-4 baseline as 10 tables, 108 columns. That figure was stale, and the staleness predates and is unrelated to Phase 4's own work:

• 2026-07-05 lock: 10 tables / 101 columns.
• 2026-07-06 autonomy-first backfill (PROJECT_DECISIONS #19): plant_common_name and plant_climate_zone each gained data_source, is_verified, created_by_actor_id — +7 columns (101 + 7 = 108). This is the arithmetic this doc's "108" figure has reflected since.
• 2026-07-06 review-seam fix, same day (PROJECT_DECISIONS #21, "FIX 1"): plant, plant_common_name, AND plant_climate_zone each gained review_status, review_reason, reviewed_by_actor_id, reviewed_at — +4 columns × 3 tables = +12 columns. This fix is fully described in §9's "Real gaps found" item 1 (closed) above, and is fully reflected in the underlying schema and in schema_docs/shared.md's per-table column lists — but the module-spec-level running total in §4 was never updated to include it.
• True pre-Phase-4 baseline: 108 + 12 = 120 columns, still 10 tables. Disclosed transparently in PROJECT_DECISIONS.md #40's own Docs section as a drive-by fix — the same treatment Phase 3 gave an unrelated stale count it found in passing. This correction is not part of Phase 4's scope of work; it is fixed here only because this doc-update pass is touching this file anyway.

10b. Phase 4's own genuine additions: +2 tables, +16 columns (120 → 136)

1. shared.exchange_rate (Item 16) — 7 columns: id (UUID surrogate PK), from_currency_code, to_currency_code, effective_date, rate, source, created_at. UNIQUE(from_currency_code, to_currency_code, effective_date) enforces the natural key as an index rather than the PK — a deliberate departure from this module's own natural-key-PK convention (§4), justified by a codebase-wide grep confirming zero composite-PK precedent across ~180 tables — this table matches that universal convention instead of inventing a shared-local exception. Pure global reference data (no tenant_id, no RLS), matching every other table in this module. No direct FK consumer yet; instead enables billing.ar_payment_application's new currency-agreement trigger (see module_spec/billing.md).
2. shared.payment_terms_catalog (Item 17b) — 9 columns: id, code, name, net_days, discount_percent, discount_days, is_active, created_at, updated_at. 10 seeded rows including 2_10_net_30 (net_days=30, discount_percent=2.00, discount_days=10 — genuinely representing "2/10 net 30," a shape a bare CHECK-enum cannot express). Deliberately simplified to pure global (no tenant_id) during drafting — an earlier draft considered an optional tenant-scoped variant mirroring identity.role's mixed-scope pattern, dropped as unrequested scope creep. Consumed by crm.customer.payment_terms_id, purchasing.vendor.payment_terms_id, purchasing.purchase_order.payment_terms_id (§8).

7 + 9 = 16 new columns. 120 + 16 = 136 columns, 10 + 2 = 12 tables total — matches §4's header count.

Migrations: packages/db/migrations/20260709020000_phase4_item16_exchange_rates.sql (Item 16); packages/db/migrations/20260709030000_phase4_item17_enum_to_catalog.sql (Item 17, part b of a 4-part cross-module migration — parts a/c/d touch pos, tax, admin respectively, not shared).

Known disclosed gap carried by Item 17b: crm.customer.credit_terms (Phase 3's own 6-value CHECK-enum) is not a clean subset of payment_terms_catalog's 10 codes — 4 catalog codes (cod, prepaid, net_7, 2_10_net_30) have no credit_terms equivalent. The new FK column is independently nullable and not yet kept in sync with the legacy enum; the eventual cutover needs a real mapping decision for those 4 codes, not a trivial rename. Logged to OPEN_ITEMS.md.

Tests: shared-schema.spec.ts gained 14 new tests (sections F–G) covering exchange_rate's 3 CHECKs (positive rate, distinct currencies, unique pair+date) and payment_terms_catalog's seed data, both confirming no-RLS/no-tenant_id. 34/34 total for the file.

Independent verification: 2 separate adversarial lenses, both CLEAN on this module's changes — see PROJECT_DECISIONS.md #40 for full findings, including live-reproduced CHECK behavior and confirmation that shared.exchange_rate/payment_terms_catalog genuinely match this schema's 100%-global convention with zero quiet exception.