platform — Phase 1

Locked at 16 tables per PROJECT_DECISIONS "Platform Module (Locked 2026-06-09)". Table names and one-line descriptions below are authoritative. Column-level definitions are pending design — to be added in a focused design pass.

Cross-Phase Foreign Keys

Eight platform columns reference tables that are built in later migration phases. To avoid forward-reference failures at migration time, these columns are created in the Phase 1 platform migration as plain UUID columns WITHOUT the FK constraint. The FK constraint is added in a later migration once the target schema exists.

#	Column	Target	FK added in
1	tenant.primary_site_id	multi_loc.site	Phase 4 (multi_loc migration)
2	tenant_contact.identity_user_id	identity.identity_user	Phase 3 (identity migration)
3	tenant_entitlement.granted_by_user_id	identity.identity_user	Phase 3 (identity migration)
4	agreement_acceptance.accepted_by_user_id	identity.identity_user	Phase 3 (identity migration)
5	tenant_setup_task.completed_by_user_id	identity.identity_user	Phase 3 (identity migration)
6	tenant_data_lifecycle.requested_by_user_id	identity.identity_user	Phase 3 (identity migration)
7	tenant_lifecycle_event.actor_user_id	identity.identity_user	Phase 3 (identity migration)
8	tenant_internal_activity.performed_by_user_id	identity.identity_user	Phase 3 (identity migration)

Each of these eight columns is marked (FK constraint deferred — see Cross-Phase FKs) in its column-definition row below.

platform.tenant

Root record for every tenant in Vrida — establishes the nursery's identity, subscription tier, default site, module preferences, and lifecycle state. One row per nursery; no tenant_id (this IS the root). Tenants never soft-delete; they transition through status states (trial → active → past_due / suspended / cancelled).

NOT tenant-scoped — this IS the root. No tenant_id, no deleted_at. Tenants go through lifecycle (status column), never soft-deleted.

RLS: not applied — this is the root record.

Access control: the tenant table has no RLS. The app_user role may read only a single tenant row by id or slug resolved from the authenticated request's tenant context (via identity.tenant_user). Cross-tenant scans of this table require service_role and occur only in trusted server-side code (provisioning, admin, reporting).

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
name	text	NOT NULL	—	Display name
slug	text	NOT NULL	—	UNIQUE — URL-safe identifier
status	text	NOT NULL	'trial'	CHECK (status IN ('trial','active','past_due','suspended','cancelled'))
tier	text	NOT NULL	'pro'	CHECK (tier IN ('starter','pro','enterprise'))
timezone	text	NOT NULL	'America/New_York'	IANA timezone
primary_site_id	UUID	nullable	—	FK → multi_loc.site (FK constraint deferred — see Cross-Phase FKs). Nullable at creation, set at provisioning step 2.
feature_flags	JSONB	NOT NULL	'{}'	System-controlled boolean toggles, key = flag name. Example: {"beta_ai_chat": true, "new_dashboard": false}
module_prefs	JSONB	NOT NULL	'{}'	Tenant on/off per module, key = module name. Example: {"inventory": true, "production": false, "purchasing": true}
activated_at	timestamptz	nullable	—
suspended_at	timestamptz	nullable	—
cancelled_at	timestamptz	nullable	—
retention_expires_at	timestamptz	nullable	—	90-day post-cancel
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on slug
• on (status) — billing/lifecycle jobs scanning tenants by lifecycle state

platform.tenant_profile

Extended business profile for a tenant — legal name, firmographics, USDA hardiness zone, scale indicators, service offerings, ecommerce flag, and acquisition attribution (UTM / referral / partner). 1:1 with tenant; populated during onboarding and kept current by the business profile setup task.

Tenant-scoped, 1:1 with tenant.

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 (uniqueness enforced as partial unique index — see Indexes)
legal_name	text	nullable	—
trading_name	text	nullable	—
brand_name	text	nullable	—
website_url	text	nullable	—
phone	text	nullable	—
support_email	text	nullable	—
tax_id	text	nullable	—	EIN
logo_url	text	nullable	—	Basic branding
country_code	char(2)	nullable	—	ISO 3166-1 alpha-2; future i18n
language_code	char(5)	nullable	—	e.g. en-US; future localization
billing_address	JSONB	nullable	—	{street,city,state,zip,country}
primary_store_address	JSONB	nullable	—
state_region	text	nullable	—	Normalized US state/region
usda_zone_primary	text	nullable	—	Climate zone for AI defaults
climate_notes	text	nullable	—
revenue_band	text	nullable	—	CHECK (revenue_band IN ('<100k','100-400k','400k-2M','2-5M','5-10M','10M+'))
employee_count_band	text	nullable	—	CHECK (employee_count_band IN ('<5','5-10','10-25','25-50','50+'))
locations_count	integer	nullable	—
business_type	text	nullable	—	CHECK (business_type IN ('independent_garden_center','nursery_with_landscaping','landscape_supply','farm_stand','chain_garden_center'))
services_offered	JSONB	nullable	'[]'	Array of service codes. Example: ["landscape_design","installation","maintenance","wholesale_to_contractors"]
has_ecommerce	boolean	nullable	false
ecommerce_platform	text	nullable	—	CHECK (ecommerce_platform IN ('none','shopify','woo','wix','other'))
online_sales_share_band	text	nullable	—	CHECK IN ('<5%','5-15%','15-30%','30%+')
approx_sku_count_band	text	nullable	—	CHECK IN ('<1k','1-3k','3-7k','7k+')
focus_plants	boolean	nullable	true
focus_hardgoods	boolean	nullable	false
peak_season_fraction	decimal(3,2)	nullable	—	0.00–1.00
current_system_before_vrida	text	nullable	—	CHECK IN ('square','lightspeed','shopify','quickbooks_pos','rapid_garden','picas','excel','paper','other')
onboarding_step	text	nullable	'created'	CHECK IN ('created','email_verified','business_profile_done','payment_added','activated')
onboarding_source	text	nullable	'self_serve'	CHECK IN ('self_serve','sales_led','partner_referral')
utm_source	text	nullable	—
utm_campaign	text	nullable	—
referral_code	text	nullable	—
partner_id	text	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• UNIQUE on (tenant_id) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)

platform.tenant_contact

Named business contacts (owner, billing, legal, admin, technical, internal CS owner). Also holds current-state CS fields (NPS, churn_risk) and per-contact marketing consent columns. Not all contacts are app users.

Tenant-scoped, many per tenant.

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
contact_type	text	NOT NULL	—	CHECK IN ('owner','billing','legal','admin','technical','cs_owner')
name	text	NOT NULL	—
email	text	nullable	—
phone	text	nullable	—
title	text	nullable	—	Job title at the nursery
is_primary	boolean	NOT NULL	false
identity_user_id	UUID	nullable	—	FK → identity.identity_user (FK constraint deferred — see Cross-Phase FKs). Not all contacts are app users.
nps_score	integer	nullable	—	0–10. Only for cs_owner type.
nps_last_response_at	timestamptz	nullable	—
churn_risk	text	nullable	—	CHECK IN ('low','medium','high')
receive_product_emails	boolean	NOT NULL	false
receive_feature_updates	boolean	NOT NULL	false
receive_webinar_invites	boolean	NOT NULL	false
marketing_consent_updated_at	timestamptz	nullable	—
marketing_consent_source	text	nullable	—	CHECK IN ('app','email_link','support')
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id, contact_type)

platform.billing_account

Vrida's billing relationship with the tenant — holds the Stripe Customer ID, billing address, tax-exempt status, tax ID, default payment method, invoice delivery preference, and payment terms. Kept separate from the subscription plan row so billing details can update without touching the plan.

Tenant-scoped, 1:1 with tenant.

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 (uniqueness enforced as partial unique index — see Indexes)
billing_name	text	nullable	—
billing_email	text	nullable	—
billing_address	JSONB	nullable	—	{street,city,state,zip,country}
tax_exempt_status	text	NOT NULL	'taxable'	CHECK IN ('taxable','exempt','pending')
tax_id	text	nullable	—
stripe_customer_id	text	nullable	—	Stripe Customer object ID
stripe_default_payment_method_id	text	nullable	—	Stripe PM ID
payment_method_brand	text	nullable	—	visa / mastercard / amex
payment_method_last4	text	nullable	—
payment_method_exp_month	integer	nullable	—	1–12
payment_method_exp_year	integer	nullable	—	2026, 2027, …
invoice_delivery_method	text	NOT NULL	'email'	CHECK IN ('email','mail','portal_only')
payment_terms_days	integer	NOT NULL	0	0 = immediate; 30/60/90 for Enterprise
currency_code	char(3)	NOT NULL	'USD'
last_payment_failure_at	timestamptz	nullable	—
last_payment_failure_reason	text	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• UNIQUE on (tenant_id) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)
• on stripe_customer_id

platform.subscription

Tracks the tenant's active Vrida SaaS plan — tier, billing cycle, trial and current-period dates, Stripe Subscription ID, seasonal pause flag, and source contract for sales-led deals. At most one active subscription per tenant (partial unique enforced). Stripe Customer ID lives in billing_account.

Tenant-scoped, 1 active per tenant (enforced by partial unique index).

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
tier_code	text	NOT NULL	—	starter / pro / enterprise. References tier_definition.tier_code.
billing_cycle	text	NOT NULL	'monthly'	CHECK IN ('monthly','annual')
status	text	NOT NULL	'trial'	CHECK IN ('trial','active','past_due','cancelled','paused','ended','incomplete','incomplete_expired','unpaid'). Notes: 'ended' = subscription fully terminated (paired with ended_at). 'incomplete' / 'incomplete_expired' / 'unpaid' = Stripe webhook lifecycle states ingested via PaymentsService.
trial_start_at	timestamptz	nullable	—
trial_end_at	timestamptz	nullable	—	30 days from start
current_period_start	timestamptz	nullable	—
current_period_end	timestamptz	nullable	—
stripe_subscription_id	text	nullable	—
is_seasonal_pause_allowed	boolean	NOT NULL	false
paused_at	timestamptz	nullable	—
resume_at	timestamptz	nullable	—
cancel_at_period_end	boolean	NOT NULL	false	Customer cancelled but access continues until period end
cancelled_at	timestamptz	nullable	—	When cancellation was requested
cancellation_reason	text	nullable	—
ended_at	timestamptz	nullable	—	When subscription actually ended
source_contract_id	UUID	nullable	—	For sales-led Enterprise deals
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• UNIQUE on (tenant_id) WHERE status NOT IN ('cancelled','ended') AND deleted_at IS NULL — partial unique, enforces one active non-deleted subscription
• on (trial_end_at) WHERE status = 'trial' — trial-end-approaching sweep
• on stripe_subscription_id

platform.subscription_invoice

One Vrida billing-cycle invoice issued to the nursery — tracks amount, payment status, line-item breakdown, and the Stripe invoice reference. Platform-managed; tenants view but cannot edit these rows.

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
subscription_id	UUID	NOT NULL	—	FK → platform.subscription
invoice_number	text	NOT NULL	—	Uniqueness enforced as partial unique index — see Indexes
period_start	timestamptz	NOT NULL	—
period_end	timestamptz	NOT NULL	—
subtotal_cents	bigint	NOT NULL	—	Before tax / discount
discount_cents	bigint	NOT NULL	0
tax_cents	bigint	NOT NULL	0
total_cents	bigint	NOT NULL	—	subtotal - discount + tax
credits_cents	bigint	NOT NULL	0	Account credits applied to this invoice
amount_due_cents	bigint	NOT NULL	—	total - credits
amount_paid_cents	bigint	NOT NULL	0
amount_remaining_cents	bigint	NOT NULL	—	amount_due - amount_paid
currency_code	char(3)	NOT NULL	'USD'
status	text	NOT NULL	'draft'	CHECK IN ('draft','open','paid','void','failed','written_off')
due_date	date	NOT NULL	—
issued_at	timestamptz	nullable	—
paid_at	timestamptz	nullable	—
line_items	JSONB	NOT NULL	'[]'	Array of line item objects with description, amount_cents, type (plan / addon / credit / adjustment). Example: [{"description":"Pro plan monthly","amount_cents":9999,"type":"plan"},{"description":"Extra users x5","amount_cents":2500,"type":"addon"}]
stripe_invoice_id	text	nullable	—
hosted_invoice_url	text	nullable	—	Stripe hosted invoice page
invoice_pdf_url	text	nullable	—	Stripe PDF download
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• UNIQUE on (invoice_number) WHERE deleted_at IS NULL — partial unique (soft-delete pattern)
• on (tenant_id, status)
• on (status, due_date) — system-wide overdue-invoice sweep

platform.payment

Records each payment the nursery makes to Vrida against a subscription invoice — amount, status (pending / succeeded / failed / refunded), and Stripe payment reference. Distinct from billing.ar_payment (merchant-side A/R); this is Vrida's own revenue collection.

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
invoice_id	UUID	NOT NULL	—	FK → platform.subscription_invoice
amount_cents	bigint	NOT NULL	—
currency_code	char(3)	NOT NULL	'USD'
status	text	NOT NULL	'pending'	CHECK IN ('pending','succeeded','failed','refunded')
payment_method_type	text	nullable	—	card / ach / check / manual
provider	text	NOT NULL	'stripe'	CHECK IN ('stripe','manual','check','ach')
provider_payment_id	text	nullable	—	Stripe PaymentIntent ID
failure_code	text	nullable	—	Structured: card_declined, insufficient_funds, expired_card
failed_reason	text	nullable	—	Human-readable detail
provider_fee_cents	bigint	nullable	—	Stripe processing fee
net_amount_cents	bigint	nullable	—	amount - provider_fee
received_at	timestamptz	nullable	—
refunded_at	timestamptz	nullable	—
refund_amount_cents	bigint	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id, invoice_id)
• on provider_payment_id

platform.tier_definition

Reference data defining each subscription tier — Starter / Pro / Enterprise feature caps, entitled modules, and permitted add-ons. Read by entitlement resolution at feature-gate check time; not tenant-scoped.

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

RLS: not applied — reference data.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tier_code	text	NOT NULL	—	UNIQUE — starter / pro / enterprise
name	text	NOT NULL	—	Display name
description	text	nullable	—	Short description for UI / marketing
display_order	integer	NOT NULL	—
price_monthly_cents	bigint	NOT NULL	—	4999 / 9999 / 19999
price_annual_cents	bigint	NOT NULL	—	49900 / 99900 / 299900
trial_days	integer	NOT NULL	30
is_public	boolean	NOT NULL	true	Visible to new signups
is_active	boolean	NOT NULL	true	Can new tenants select
max_users	integer	NOT NULL	—	-1 = unlimited
max_sites	integer	NOT NULL	—
max_zones	integer	NOT NULL	—
max_skus	integer	NOT NULL	—
entitled_modules	JSONB	NOT NULL	—	{"inventory":true,"production":false}
permitted_addons	JSONB	NOT NULL	'[]'	Array of addon codes. Example: ["extra_users","extra_sites","ai_pack","white_label","priority_support"]
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on tier_code

platform.tenant_entitlement

Source of truth for feature access. Absorbs subscription_addon: add-ons are entitlements with source_type=addon, plus price / quantity. Other source types: tier, override, beta, contract. Answers "why does this tenant have access to X?"

Tenant-scoped, many per tenant.

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
entitlement_code	text	NOT NULL	—	production_module / extra_users / ai_pack / white_label / advanced_reporting / custom_roles / etc.
source_type	text	NOT NULL	—	CHECK IN ('tier','addon','override','beta','contract')
source_id	text	nullable	—	tier_code, contract_id, etc.
quantity	integer	nullable	—	For add-ons: 5 extra users. Null for boolean entitlements.
limit_value	integer	nullable	—	Cap: 10000 AI calls. Null for boolean entitlements.
used_value	integer	nullable	—	Current usage against limit. Null if no limit.
reset_period	text	nullable	—	CHECK IN ('monthly','annual','never'). When used_value resets. Null if no limit.
renews_at	timestamptz	nullable	—	Next reset date. Null if no limit.
price_cents	bigint	nullable	—	For add-ons
currency_code	char(3)	nullable	—
billing_cycle	text	nullable	—	CHECK IN ('monthly','annual'). For add-ons only.
status	text	NOT NULL	'active'	CHECK IN ('active','expired','revoked')
is_enabled	boolean	NOT NULL	true
starts_at	timestamptz	NOT NULL	—
ends_at	timestamptz	nullable	—	Null = no expiry
granted_by_user_id	UUID	nullable	—	FK → identity.identity_user (FK constraint deferred — see Cross-Phase FKs)
reason	text	nullable	—
metadata	JSONB	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete

Indexes:

• PK on id
• on (tenant_id, entitlement_code)

platform.agreement_version

Version registry for Vrida's published legal documents (TOS, MSA, DPA) — one row per published version, with document type, content hash, effective date, and the R2 document URL. Platform-managed reference data; not tenant-scoped.

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

RLS: not applied — reference data.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
agreement_type	text	NOT NULL	—	CHECK IN ('tos','msa','privacy_policy','dpa')
version	text	NOT NULL	—	e.g. "2026-06-01"
effective_date	date	NOT NULL	—
document_url	text	NOT NULL	—	R2 or public URL
content_hash	text	NOT NULL	—	SHA-256
is_active	boolean	NOT NULL	true
requires_acceptance	boolean	NOT NULL	true	Some docs are informational only
supersedes_agreement_version_id	UUID	nullable	—	FK → self. Which prior version this replaces.
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (agreement_type, version)

platform.agreement_acceptance

Immutable record of a tenant user accepting a specific agreement version — timestamp, IP address, user-agent string, and HelloSign envelope ref for legally-binding documents. FKs to agreement_version and Identity's user table. Append-only; a new row is written each time a user accepts an updated version.

Tenant-scoped, immutable. No updated_at, no deleted_at. Once accepted, never changes.

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
agreement_version_id	UUID	NOT NULL	—	FK → platform.agreement_version
accepted_by_user_id	UUID	NOT NULL	—	FK → identity.identity_user (FK constraint deferred — see Cross-Phase FKs)
accepted_at	timestamptz	NOT NULL	—
acceptance_method	text	NOT NULL	—	CHECK IN ('clickwrap','wet_sign','hellosign','docusign')
ip_address	inet	nullable	—
user_agent	text	nullable	—
signature_ref	text	nullable	—	HelloSign envelope ID
evidence_json	JSONB	nullable	—	{"checkbox_text":"I agree to..."}
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id, agreement_version_id)

platform.tenant_usage_summary

Monthly usage snapshot per tenant — active user count, site count, SKU count, transaction count, and AI call count for a billing period. Used for billing cap enforcement and upgrade prompts. Append-only, never overwritten.

Tenant-scoped, append-only. No updated_at (append-only, one row per period). No deleted_at.

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
period_start	date	NOT NULL	—
period_end	date	NOT NULL	—
active_users_count	integer	NOT NULL	0
sites_count	integer	NOT NULL	0
skus_count	integer	NOT NULL	0
pos_sales_count	integer	NOT NULL	0
orders_count	integer	NOT NULL	0
ai_calls_count	integer	NOT NULL	0
storage_bytes	bigint	NOT NULL	0
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (tenant_id, period_start)

platform.tenant_setup_task

Combines technical provisioning (schema created, storage initialized, Stripe setup) and business onboarding (email verified, business profile, inventory imported via AI, POS configured, first sale, go-live). Each row: task_category, task_code, status, is_required, is_customer_visible, display_order, dependencies, failure tracking, metadata.

Tenant-scoped, many per tenant. No deleted_at — setup tasks are permanent records.

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
task_category	text	NOT NULL	—	CHECK IN ('provisioning','onboarding')
task_code	text	NOT NULL	—	CHECK IN ('schema_created', 'default_site_created', 'storage_initialized', 'identity_initialized', 'stripe_setup', 'seed_data_loaded', 'email_verified', 'business_profile_completed', 'payment_method_added', 'legal_agreements_accepted', 'first_user_invited', 'site_configured', 'inventory_imported', 'customers_imported', 'vendors_imported', 'pos_configured', 'branding_configured', 'first_sale_completed', 'go_live')
status	text	NOT NULL	'pending'	CHECK IN ('pending','in_progress','completed','failed','skipped','blocked')
is_required	boolean	NOT NULL	true
is_customer_visible	boolean	NOT NULL	false
display_order	integer	NOT NULL	0
depends_on	text	nullable	—	task_code that must complete first
started_at	timestamptz	nullable	—
completed_at	timestamptz	nullable	—
completed_by_user_id	UUID	nullable	—	FK → identity.identity_user (FK constraint deferred — see Cross-Phase FKs)
failed_at	timestamptz	nullable	—
failure_reason	text	nullable	—
blocked_reason	text	nullable	—	Why task is blocked
retry_count	integer	NOT NULL	0
next_retry_at	timestamptz	nullable	—	Scheduled retry
metadata	JSONB	nullable	—	{"imported_sku_count":243}
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• UNIQUE on (tenant_id, task_code)

platform.tenant_data_lifecycle

Governs post-cancellation data handling for a tenant — retention period, deletion workflow state (scheduled / in_progress / completed / failed), legal-hold flag, and export request tracking. One row per tenant; written by the data-lifecycle job and Vrida ops.

Tenant-scoped. No deleted_at — compliance / audit records.

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
lifecycle_type	text	NOT NULL	—	CHECK IN ('retention','export','deletion','legal_hold')
status	text	NOT NULL	'scheduled'	CHECK IN ('scheduled','requested','in_progress','completed','failed','blocked')
requested_by_user_id	UUID	nullable	—	FK → identity.identity_user (FK constraint deferred — see Cross-Phase FKs)
started_at	timestamptz	nullable	—
completed_at	timestamptz	nullable	—
expires_at	timestamptz	nullable	—
deletion_scheduled_at	timestamptz	nullable	—	When deletion will run
export_type	text	nullable	—	full / inventory / customers / transactions
download_url	text	nullable	—	R2 signed URL
download_expires_at	timestamptz	nullable	—
export_file_hash	text	nullable	—	SHA-256 of export for integrity
file_size_bytes	bigint	nullable	—
legal_hold_flag	boolean	NOT NULL	false
legal_hold_reason	text	nullable	—
failure_reason	text	nullable	—
metadata	JSONB	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id, lifecycle_type)

platform.tenant_lifecycle_event

Every status transition: what changed, why (reason code), who triggered it, when. Single source of truth for churn / suspension reasons.

Tenant-scoped, immutable. No updated_at, no deleted_at. Append-only.

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
event_type	text	NOT NULL	—	CHECK IN ('created','activated','suspended','resumed','cancelled','paused','unpaused','retention_expired','deleted')
from_status	text	nullable	—	Previous tenant.status value
to_status	text	nullable	—	New tenant.status value
reason_code	text	nullable	—	CHECK IN ('non_payment','requested_by_customer','abuse','internal_testing','seasonal_pause','trial_expired','other')
actor_type	text	NOT NULL	'system'	CHECK IN ('system','admin','customer','webhook')
actor_user_id	UUID	nullable	—	FK → identity.identity_user (FK constraint deferred — see Cross-Phase FKs)
correlation_id	text	nullable	—	Stripe webhook event ID, job ID
notes	text	nullable	—
metadata	JSONB	nullable	—
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id, created_at)

platform.tenant_internal_activity

Vrida's append-only internal activity log for CS and admin operations — support notes, tier changes, trial extensions, feature grants, credits, NPS updates, and churn-risk flags. activity_type discriminates record kinds; before / after JSONB captures state for admin changes. Never visible to tenants.

Tenant-scoped, immutable. No updated_at, no deleted_at. Append-only.

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
activity_type	text	NOT NULL	—	CHECK IN ('note','admin_action','nps_update','churn_update','credit_applied','trial_extended','tier_changed','feature_granted','feature_revoked')
performed_by_user_id	UUID	NOT NULL	—	FK → identity.identity_user (FK constraint deferred — see Cross-Phase FKs)
subject	text	nullable	—	Short summary
notes	text	nullable	—
before_value	JSONB	nullable	—	{"tier":"starter"}
after_value	JSONB	nullable	—	{"tier":"pro"}
reason_code	text	nullable	—
related_entity_type	text	nullable	—	subscription_invoice, tenant_entitlement, subscription, etc.
related_entity_id	UUID	nullable	—	FK to the related record
correlation_id	text	nullable	—	Ties to source event
created_at	timestamptz	NOT NULL	now()

Indexes:

• PK on id
• on (tenant_id, created_at)

---

Platform — Design Patterns Summary

Patterns applied across the 16 Platform tables. Use this as a cheat-sheet when reviewing or extending the schema.

Tables WITHOUT tenant_id (3 tables)

These are the root / reference tables that do not participate in tenant-level RLS.

• tenant — IS the root; everything else's tenant_id FKs here
• tier_definition — reference data; shared across all tenants
• agreement_version — reference data; shared across all tenants

Immutable tables — no updated_at AND no deleted_at (4 tables)

Append-only. Once written, never modified or removed.

• agreement_acceptance
• tenant_usage_summary
• tenant_lifecycle_event
• tenant_internal_activity

Tables WITHOUT deleted_at — permanent records (7 tables total)

Includes the 4 immutable tables above plus 3 more that are mutable but never soft-deleted:

• tenant (status-driven lifecycle, not soft delete)
• tier_definition (reference data; deactivate via is_active)
• agreement_version (reference data; deactivate via is_active)
• agreement_acceptance (immutable)
• tenant_usage_summary (immutable)
• tenant_lifecycle_event (immutable)
• tenant_internal_activity (immutable)
• tenant_setup_task — mutable but permanent (setup history is preserved)
• tenant_data_lifecycle — mutable but permanent (compliance/audit records)

JSONB columns by purpose

Table	Column	Purpose
tenant	feature_flags	{"beta_ai_chat": true, "new_dashboard": false} — system boolean toggles, key = flag name
tenant	module_prefs	{"inventory": true, "production": false, "purchasing": true} — tenant on/off per module, key = module name
tenant_profile	billing_address	{street,city,state,zip,country}
tenant_profile	primary_store_address	{street,city,state,zip,country}
tenant_profile	services_offered	["landscape_design","installation","maintenance","wholesale_to_contractors"] — array of service codes
billing_account	billing_address	{street,city,state,zip,country}
subscription_invoice	line_items	[{"description":"Pro plan monthly","amount_cents":9999,"type":"plan"},…] — array; type ∈ plan/addon/credit/adjustment
tier_definition	entitled_modules	{"inventory":true,"production":false}
tier_definition	permitted_addons	["extra_users","extra_sites","ai_pack","white_label","priority_support"] — array of addon codes
tenant_entitlement	metadata	Flexible per-entitlement bag
agreement_acceptance	evidence_json	{"checkbox_text":"I agree to..."}
tenant_setup_task	metadata	Task-specific data, e.g. {"imported_sku_count":243}
tenant_data_lifecycle	metadata	Lifecycle-specific bag
tenant_lifecycle_event	metadata	Event-specific context
tenant_internal_activity	before_value	State before admin change
tenant_internal_activity	after_value	State after admin change

Money columns (always _cents bigint + currency_code char(3))

Table	Column
subscription_invoice	subtotal_cents, discount_cents, tax_cents, total_cents, credits_cents, amount_due_cents, amount_paid_cents, amount_remaining_cents
payment	amount_cents, provider_fee_cents, net_amount_cents, refund_amount_cents
tier_definition	price_monthly_cents, price_annual_cents
tenant_entitlement	price_cents

currency_code is char(3) ISO 4217, default 'USD', present on every table holding money.

Other patterns

• All PKs: UUID with default uuid_generate_v4()
• All enums: stored as text with CHECK constraints (per SCHEMA_CONVENTIONS — no Postgres enum types)
• All timestamps: timestamptz, stored UTC
• Partial unique indexes (soft-delete pattern per SCHEMA_CONVENTIONS):
  • tenant_profile: UNIQUE (tenant_id) WHERE deleted_at IS NULL
  • billing_account: UNIQUE (tenant_id) WHERE deleted_at IS NULL
  • subscription_invoice: UNIQUE (invoice_number) WHERE deleted_at IS NULL
  • subscription: UNIQUE (tenant_id) WHERE status NOT IN ('cancelled','ended') AND deleted_at IS NULL — enforces one active non-deleted subscription per tenant
  • tenant_usage_summary, tenant_setup_task use composite UNIQUE (no deleted_at — tables have no soft delete)
  • agreement_version uses composite UNIQUE (agreement_type, version) (reference data — no soft delete)

---