Data & Schema

The shape of the data, documented. Every table, column, enum, index and row-level-security policy below is transcribed straight from this site’s production migrations, not a sketch of how it might look. Read it as the contract the application is held to.

The rules, stated once

Conventions every table follows

Consistency is a security property. These six rules hold across the whole schema, so any table you open behaves the way the last one did.

UUID primary keys, never sequential integers

Every table keys on a uuid_generate_v4() primary key. IDs are never guessable or enumerable, so a leaked URL reveals nothing about row counts or order.

Soft delete, never a hard delete

Records carry a nullable deleted_at TIMESTAMPTZ. Deletion sets the timestamp; every public read filters deleted_at IS NULL. Nothing is ever physically removed, history stays recoverable and auditable.

All timestamps are UTC (TIMESTAMPTZ)

Time is stored as TIMESTAMPTZ and defaulted with NOW() at the database, never as local strings. Africa/Johannesburg (UTC+2) is applied only at the display edge.

Row-level security on every table

RLS is ENABLEd on all ten core tables. Public-facing reads are gated by explicit policies (active products, approved testimonials, published articles); private data (subscribers, quote leads) has no public policy at all and is reachable only via the service role.

updated_at maintained by trigger, not application code

Mutable tables (products, case_studies, articles) run a BEFORE UPDATE trigger calling update_updated_at(), so the column is always correct regardless of which code path wrote the row.

Money as NUMERIC, currency explicit

Prices and amounts are NUMERIC(10,2), never floating point, with a separate currency column. ZAR is the base; price_usd is nullable for the products that publish one.

Controlled vocabularies

Enums

State lives in Postgres enums, not free text, so an order can only ever be one of four things, and an impossible status can’t be written at all.

product_status·

activebetacoming_soonarchived

products.status, drives the public RLS read (active/beta only) and the Coming-Soon UI state.

order_status·

pendingpaidfailedrefunded

orders.status, set to paid only by the verified PayFast ITN handler.

quote_status·

pendingreviewingquotedaccepteddeclined

quote_requests.status, the lead lifecycle in the admin queue.

product_tier·

accessprofessionalenterprise

products.tier, the three-tier pricing model.

billing_type·

monthlyannualonce_off

products.billing_type.

This site’s database

Core tables

Six of the ten core tables, in full. Each opens with its row-level-security posture in plain language, because on this platform, what a row is matters less than who is allowed to read it. (subscribers, quote_requests, products, orders and referrals are transcribed verbatim from migration 001.)

subscribers

Newsletter and nurture-sequence membership for The Mirembe Brief. No public read policy, service-role only.

RLSRLS on. No public policy whatsoever, all access is via the service role or SECURITY DEFINER functions, so emails can never be read from the client.

Column	Type	Notes
`id` PK	UUID	uuid_generate_v4()
`email` UNIQUENOT NULLINDEX	TEXT	One row per address
`source`	TEXT	Capture point: 'homepage', 'quote_form', 'build_shop'…
`sequence` NOT NULL	TEXT	Which nurture track; default 'mirembe_brief'
`sequence_step` INDEX	INTEGER	Position in the track; partial index where unsubscribed_at IS NULL
`language` NOT NULL	TEXT	Locale for sends; default 'en'
`confirmed_at`	TIMESTAMPTZ	Double opt-in confirmation
`unsubscribed_at`	TIMESTAMPTZ	Suppresses all further sends
`created_at` NOT NULL	TIMESTAMPTZ	DEFAULT NOW()

Indexes

idx_subscribers_email
idx_subscribers_sequence (sequence, sequence_step) WHERE unsubscribed_at IS NULL

quote_requests

Inbound project briefs from /quote, the top of the consulting funnel.

RLSRLS on. Public INSERT only (lead capture); no public read. The admin queue reads via the service role.

Column	Type	Notes
`id` PK	UUID	uuid_generate_v4()
`name` NOT NULL	TEXT	—
`email` NOT NULLINDEX	TEXT	Indexed for lookup
`service_wing` NOT NULL	TEXT	Which wing the brief is for
`project_type` NOT NULL	TEXT	—
`budget_range`	TEXT	Self-reported band
`timeline`	TEXT	Desired delivery window
`description` NOT NULL	TEXT	The brief itself
`status` NOT NULLINDEX	quote_status	Lifecycle enum; default 'pending'
`admin_notes`	TEXT	Internal triage notes
`deleted_at` soft-delete	TIMESTAMPTZ	Soft delete

Indexes

idx_quote_requests_status (status) WHERE deleted_at IS NULL
idx_quote_requests_email

products

The catalog, SaaS, digital products and courses across the ecosystem.

RLSRLS on. Public read is policy-gated to status IN ('active','beta') AND deleted_at IS NULL, so coming-soon and archived rows are invisible to the client.

Column	Type	Notes
`id` PK	UUID	uuid_generate_v4()
`slug` UNIQUENOT NULLINDEX	TEXT	URL key
`name` NOT NULL	TEXT	—
`tier` NOT NULL	product_tier	access \| professional \| enterprise
`price_zar` NOT NULL	NUMERIC(10,2)	Base price
`price_usd`	NUMERIC(10,2)	Optional published USD
`billing_type` NOT NULL	billing_type	Default 'monthly'
`status` NOT NULLINDEX	product_status	Drives the public read policy
`features` JSONBNOT NULL	JSONB	Feature list; default '[]'
`updated_at` NOT NULL	TIMESTAMPTZ	Maintained by trigger
`deleted_at` soft-delete	TIMESTAMPTZ	Soft delete

Indexes

idx_products_status (status, sort_order)
idx_products_slug
trg_products_updated_at (BEFORE UPDATE)

orders

Purchases and deposits. Written by the visitor, confirmed only by the payment webhook.

RLSRLS on. Users may read and insert only their own rows (auth.uid() = user_id). The paid status is set exclusively by the service-role PayFast ITN handler, never trusted from the client.

Column	Type	Notes
`id` PK	UUID	uuid_generate_v4()
`user_id` FKNOT NULLINDEX	UUID	→ auth.users(id); RLS boundary
`product_id` FKNOT NULL	UUID	→ products(id)
`amount_zar` NOT NULL	NUMERIC(10,2)	—
`currency` NOT NULL	TEXT	Default 'ZAR'
`payment_provider` NOT NULL	payment_provider	payfast (canonical)
`payment_reference`	TEXT	Gateway reference
`status` NOT NULLINDEX	order_status	Default 'pending'; paid only via verified ITN
`referral_code`	TEXT	Attribution to a referral
`discount_applied` NOT NULL	NUMERIC(10,2)	Referral discount; default 0
`deleted_at` soft-delete	TIMESTAMPTZ	Soft delete

Indexes

idx_orders_user_id WHERE deleted_at IS NULL
idx_orders_status WHERE deleted_at IS NULL

case_studies

Published client work. The /case-studies index and detail pages, and the homepage proof strip, read from here.

RLSRLS on. Public read is gated to published_at IS NOT NULL AND published_at <= NOW() AND deleted_at IS NULL, so a draft, a future-dated piece, or a deleted one is never visible to the client.

Column	Type	Notes
`id` PK	UUID	uuid_generate_v4()
`slug` UNIQUENOT NULLINDEX	TEXT	URL key
`title` NOT NULL	TEXT	—
`client_name` NOT NULL	TEXT	—
`excerpt` NOT NULL	TEXT	Card + meta summary
`body`	TEXT	Full write-up
`service_wing` NOT NULL	TEXT	Which wing delivered it
`results` JSONBNOT NULL	JSONB	Metric→value pairs; default '{}'
`featured` NOT NULL	BOOLEAN	Homepage pull; default false
`published_at` INDEX	TIMESTAMPTZ	Gates the public read policy
`updated_at` NOT NULL	TIMESTAMPTZ	Maintained by trigger
`deleted_at` soft-delete	TIMESTAMPTZ	Soft delete

Indexes

idx_case_studies_published (published_at DESC) WHERE deleted_at IS NULL AND published_at IS NOT NULL
idx_case_studies_slug
trg_case_studies_updated_at (BEFORE UPDATE)

referrals

The public referral programme, R2,000 credit to the referrer, 5% off for the referee.

RLSRLS on. A user can read only their own referrals (auth.uid() = referrer_user_id). Codes are globally unique.

Column	Type	Notes
`id` PK	UUID	uuid_generate_v4()
`referrer_user_id` FKNOT NULLINDEX	UUID	→ auth.users(id); RLS boundary
`referee_email` NOT NULL	TEXT	Invited address
`code` UNIQUENOT NULLINDEX	TEXT	Shareable code
`credit_zar` NOT NULL	NUMERIC(10,2)	Referrer credit; default 2000
`discount_percent` NOT NULL	NUMERIC(5,2)	Referee discount; default 5
`redeemed_at`	TIMESTAMPTZ	When the referee converted
`expires_at` NOT NULL	TIMESTAMPTZ	DEFAULT NOW() + 90 days

Indexes

idx_referrals_code
idx_referrals_referrer

Also in the schema: contact_messages, articles, service_bookings and the testimonials table (extended across later phases with status, video and source/consent governance columns), same conventions, same RLS discipline.

Across the ecosystem

Schema scale, per product

Each product carries its own database with the same discipline. The figures below are drawn from each product’s build journals; open a product spec for its full architecture diagram and decisions.

VarsityOS

149 tables · 391 RLS policies · 220 indexes · 88 migrations

profiles IS the user row, its id is the auth.users PK directly, every RLS policy is auth.uid() = id, and child tables cascade on delete
POPIA baked in: popia_consent NOT NULL + popia_consent_at, captured at signup via a Zod z.literal(true)
history-preserving deletes, group_messages.user_id / group_tasks.assigned_to use ON DELETE SET NULL so shared history survives a member leaving

AdminOS

86 tables · all under RLS · 147 policies · 43 migrations

Tenant claims (tenant_id, role, plan) live in service-role-only app_metadata, never user-owned metadata, so current_tenant_id() denies by default
conversations first-class (intent / sentiment / status at thread level); messages carry the full WhatsApp delivery lifecycle + from_cache + tokens_used
audit_log is append-only: UPDATE/DELETE revoked at the DB level, even for the service role

K53 Drill Master

1 Supabase table + ~15 localStorage stores

subscribers, id, user_id (FK auth.users), email, plan (CHECK monthly | bundle | lifetime | lifetime_pdp | group), status, payfast_payment_id, expires_at
RLS: read_own (auth.uid() = user_id) + service_insert (service-role only), "the anon key is public, and it doesn’t matter, because the DB says no"
two independent learning engines in localStorage: k53_sr_v1 (SM-2) and the Nervous System (k53_nerve_mastery_v1 / k53_xp_v1), plus freemium, streak, history and prefs keys

Sanyu Botanicals

25 tables · 53 migrations · 8 stored RPCs

products, stock_count (engine-synced) + reserved_count, is_combo + combo_items JSONB, variant_group, soft-delete via deleted_at
orders, status CHECK (pending|paid|packed|shipped|delivered|refunded|cancelled|expired), payment_method, a synced_at column with a partial index WHERE synced_at IS NULL for the backfill scan
angel_points_ledger, append-only: every movement stores delta + balance_after, RLS scoped to the owning Angel

The Selves of Us

25 tables · RLS + policies on all 25 · 8 enums · 32 migrations

profiles (role: founder | editor | studio) → stories → {editorial_messages, story_versions, comments, mirrors}
story_status enum: draft → pending → needs_edits → resubmitted → approved → published
separate comments vs story_comments, subscriptions vs supporter_subscriptions, modelled, not conflated

True Access

19 tables · PostGIS · 8 migrations · 13 RPCs

locations, PostGIS GIST index on geography(Point), compliance_score CHECK 0–100, mapbox_feature_id; partial indexes WHERE deleted_at IS NULL
accessibility_features, the SANS 10400-S:2011 checklist: 11 required ×10 + 6 optional ×5 (MAX 140), each row citing a clause like cl.5.1
consents, POPIA §26 special-personal-info: versioned, per-type (disability_data / marketing / analytics), revocable

StokvelOS

19 tables across 6 modular schema files · region-pinned jnb1

contributions / payouts / loans, each with a UNIQUE receipt_number and recorded_by
audit_log is append-only by design: its only RLS policy is SELECT-for-admins, so every write goes through the service role
every financial record dual-writes its notion_page_id; stokvels holds five notion_*_db_id columns

WatchSankofa

26 tables · 26 indexes · RLS on all 26 · 50 policies · trigger-driven

content (creators, works, series, albums, collections) + discovery (taste_profiles, history)
denormalized counters kept live by triggers: works.view/heart/comment/share_count + trending_score/is_trending
fan-out triggers write activity_feed + notifications on new work / follow / note; check_and_award_badges tiers badges automatically

Your system, documented like this

Every client build ships with this same data dictionary, you own the schema, the migrations and the docs.

Start a project Security & Trust →

← Handbook

Data & Schema

The rules, stated once

Conventions every table follows

Consistency is a security property. These six rules hold across the whole schema, so any table you open behaves the way the last one did.

UUID primary keys, never sequential integers

Every table keys on a uuid_generate_v4() primary key. IDs are never guessable or enumerable, so a leaked URL reveals nothing about row counts or order.

Soft delete, never a hard delete

All timestamps are UTC (TIMESTAMPTZ)

Time is stored as TIMESTAMPTZ and defaulted with NOW() at the database, never as local strings. Africa/Johannesburg (UTC+2) is applied only at the display edge.

Row-level security on every table

updated_at maintained by trigger, not application code

Mutable tables (products, case_studies, articles) run a BEFORE UPDATE trigger calling update_updated_at(), so the column is always correct regardless of which code path wrote the row.

Money as NUMERIC, currency explicit

Prices and amounts are NUMERIC(10,2), never floating point, with a separate currency column. ZAR is the base; price_usd is nullable for the products that publish one.

Controlled vocabularies

Enums

State lives in Postgres enums, not free text, so an order can only ever be one of four things, and an impossible status can’t be written at all.

product_status·

activebetacoming_soonarchived

products.status, drives the public RLS read (active/beta only) and the Coming-Soon UI state.

order_status·

pendingpaidfailedrefunded

orders.status, set to paid only by the verified PayFast ITN handler.

quote_status·

pendingreviewingquotedaccepteddeclined

quote_requests.status, the lead lifecycle in the admin queue.

product_tier·

accessprofessionalenterprise

products.tier, the three-tier pricing model.

billing_type·

monthlyannualonce_off

products.billing_type.

This site’s database

Core tables

subscribers

Newsletter and nurture-sequence membership for The Mirembe Brief. No public read policy, service-role only.

RLSRLS on. No public policy whatsoever, all access is via the service role or SECURITY DEFINER functions, so emails can never be read from the client.

Column	Type	Notes
`id` PK	UUID	uuid_generate_v4()
`email` UNIQUENOT NULLINDEX	TEXT	One row per address
`source`	TEXT	Capture point: 'homepage', 'quote_form', 'build_shop'…
`sequence` NOT NULL	TEXT	Which nurture track; default 'mirembe_brief'
`sequence_step` INDEX	INTEGER	Position in the track; partial index where unsubscribed_at IS NULL
`language` NOT NULL	TEXT	Locale for sends; default 'en'
`confirmed_at`	TIMESTAMPTZ	Double opt-in confirmation
`unsubscribed_at`	TIMESTAMPTZ	Suppresses all further sends
`created_at` NOT NULL	TIMESTAMPTZ	DEFAULT NOW()

Indexes

idx_subscribers_email
idx_subscribers_sequence (sequence, sequence_step) WHERE unsubscribed_at IS NULL

quote_requests

Inbound project briefs from /quote, the top of the consulting funnel.

RLSRLS on. Public INSERT only (lead capture); no public read. The admin queue reads via the service role.

Column	Type	Notes
`id` PK	UUID	uuid_generate_v4()
`name` NOT NULL	TEXT	—
`email` NOT NULLINDEX	TEXT	Indexed for lookup
`service_wing` NOT NULL	TEXT	Which wing the brief is for
`project_type` NOT NULL	TEXT	—
`budget_range`	TEXT	Self-reported band
`timeline`	TEXT	Desired delivery window
`description` NOT NULL	TEXT	The brief itself
`status` NOT NULLINDEX	quote_status	Lifecycle enum; default 'pending'
`admin_notes`	TEXT	Internal triage notes
`deleted_at` soft-delete	TIMESTAMPTZ	Soft delete

Indexes

idx_quote_requests_status (status) WHERE deleted_at IS NULL
idx_quote_requests_email

products

The catalog, SaaS, digital products and courses across the ecosystem.

RLSRLS on. Public read is policy-gated to status IN ('active','beta') AND deleted_at IS NULL, so coming-soon and archived rows are invisible to the client.

Column	Type	Notes
`id` PK	UUID	uuid_generate_v4()
`slug` UNIQUENOT NULLINDEX	TEXT	URL key
`name` NOT NULL	TEXT	—
`tier` NOT NULL	product_tier	access \| professional \| enterprise
`price_zar` NOT NULL	NUMERIC(10,2)	Base price
`price_usd`	NUMERIC(10,2)	Optional published USD
`billing_type` NOT NULL	billing_type	Default 'monthly'
`status` NOT NULLINDEX	product_status	Drives the public read policy
`features` JSONBNOT NULL	JSONB	Feature list; default '[]'
`updated_at` NOT NULL	TIMESTAMPTZ	Maintained by trigger
`deleted_at` soft-delete	TIMESTAMPTZ	Soft delete

Indexes

idx_products_status (status, sort_order)
idx_products_slug
trg_products_updated_at (BEFORE UPDATE)

orders

Purchases and deposits. Written by the visitor, confirmed only by the payment webhook.

RLSRLS on. Users may read and insert only their own rows (auth.uid() = user_id). The paid status is set exclusively by the service-role PayFast ITN handler, never trusted from the client.

Column	Type	Notes
`id` PK	UUID	uuid_generate_v4()
`user_id` FKNOT NULLINDEX	UUID	→ auth.users(id); RLS boundary
`product_id` FKNOT NULL	UUID	→ products(id)
`amount_zar` NOT NULL	NUMERIC(10,2)	—
`currency` NOT NULL	TEXT	Default 'ZAR'
`payment_provider` NOT NULL	payment_provider	payfast (canonical)
`payment_reference`	TEXT	Gateway reference
`status` NOT NULLINDEX	order_status	Default 'pending'; paid only via verified ITN
`referral_code`	TEXT	Attribution to a referral
`discount_applied` NOT NULL	NUMERIC(10,2)	Referral discount; default 0
`deleted_at` soft-delete	TIMESTAMPTZ	Soft delete

Indexes

idx_orders_user_id WHERE deleted_at IS NULL
idx_orders_status WHERE deleted_at IS NULL

case_studies

Published client work. The /case-studies index and detail pages, and the homepage proof strip, read from here.

RLSRLS on. Public read is gated to published_at IS NOT NULL AND published_at <= NOW() AND deleted_at IS NULL, so a draft, a future-dated piece, or a deleted one is never visible to the client.

Column	Type	Notes
`id` PK	UUID	uuid_generate_v4()
`slug` UNIQUENOT NULLINDEX	TEXT	URL key
`title` NOT NULL	TEXT	—
`client_name` NOT NULL	TEXT	—
`excerpt` NOT NULL	TEXT	Card + meta summary
`body`	TEXT	Full write-up
`service_wing` NOT NULL	TEXT	Which wing delivered it
`results` JSONBNOT NULL	JSONB	Metric→value pairs; default '{}'
`featured` NOT NULL	BOOLEAN	Homepage pull; default false
`published_at` INDEX	TIMESTAMPTZ	Gates the public read policy
`updated_at` NOT NULL	TIMESTAMPTZ	Maintained by trigger
`deleted_at` soft-delete	TIMESTAMPTZ	Soft delete

Indexes

idx_case_studies_published (published_at DESC) WHERE deleted_at IS NULL AND published_at IS NOT NULL
idx_case_studies_slug
trg_case_studies_updated_at (BEFORE UPDATE)

referrals

The public referral programme, R2,000 credit to the referrer, 5% off for the referee.

RLSRLS on. A user can read only their own referrals (auth.uid() = referrer_user_id). Codes are globally unique.

Column	Type	Notes
`id` PK	UUID	uuid_generate_v4()
`referrer_user_id` FKNOT NULLINDEX	UUID	→ auth.users(id); RLS boundary
`referee_email` NOT NULL	TEXT	Invited address
`code` UNIQUENOT NULLINDEX	TEXT	Shareable code
`credit_zar` NOT NULL	NUMERIC(10,2)	Referrer credit; default 2000
`discount_percent` NOT NULL	NUMERIC(5,2)	Referee discount; default 5
`redeemed_at`	TIMESTAMPTZ	When the referee converted
`expires_at` NOT NULL	TIMESTAMPTZ	DEFAULT NOW() + 90 days

Indexes

idx_referrals_code
idx_referrals_referrer

Across the ecosystem

Schema scale, per product

Each product carries its own database with the same discipline. The figures below are drawn from each product’s build journals; open a product spec for its full architecture diagram and decisions.

VarsityOS

149 tables · 391 RLS policies · 220 indexes · 88 migrations

profiles IS the user row, its id is the auth.users PK directly, every RLS policy is auth.uid() = id, and child tables cascade on delete
POPIA baked in: popia_consent NOT NULL + popia_consent_at, captured at signup via a Zod z.literal(true)
history-preserving deletes, group_messages.user_id / group_tasks.assigned_to use ON DELETE SET NULL so shared history survives a member leaving

AdminOS

86 tables · all under RLS · 147 policies · 43 migrations

Tenant claims (tenant_id, role, plan) live in service-role-only app_metadata, never user-owned metadata, so current_tenant_id() denies by default
conversations first-class (intent / sentiment / status at thread level); messages carry the full WhatsApp delivery lifecycle + from_cache + tokens_used
audit_log is append-only: UPDATE/DELETE revoked at the DB level, even for the service role

K53 Drill Master

1 Supabase table + ~15 localStorage stores

subscribers, id, user_id (FK auth.users), email, plan (CHECK monthly | bundle | lifetime | lifetime_pdp | group), status, payfast_payment_id, expires_at
RLS: read_own (auth.uid() = user_id) + service_insert (service-role only), "the anon key is public, and it doesn’t matter, because the DB says no"
two independent learning engines in localStorage: k53_sr_v1 (SM-2) and the Nervous System (k53_nerve_mastery_v1 / k53_xp_v1), plus freemium, streak, history and prefs keys

Sanyu Botanicals

25 tables · 53 migrations · 8 stored RPCs

products, stock_count (engine-synced) + reserved_count, is_combo + combo_items JSONB, variant_group, soft-delete via deleted_at
orders, status CHECK (pending|paid|packed|shipped|delivered|refunded|cancelled|expired), payment_method, a synced_at column with a partial index WHERE synced_at IS NULL for the backfill scan
angel_points_ledger, append-only: every movement stores delta + balance_after, RLS scoped to the owning Angel

The Selves of Us

25 tables · RLS + policies on all 25 · 8 enums · 32 migrations

profiles (role: founder | editor | studio) → stories → {editorial_messages, story_versions, comments, mirrors}
story_status enum: draft → pending → needs_edits → resubmitted → approved → published
separate comments vs story_comments, subscriptions vs supporter_subscriptions, modelled, not conflated

True Access

19 tables · PostGIS · 8 migrations · 13 RPCs

locations, PostGIS GIST index on geography(Point), compliance_score CHECK 0–100, mapbox_feature_id; partial indexes WHERE deleted_at IS NULL
accessibility_features, the SANS 10400-S:2011 checklist: 11 required ×10 + 6 optional ×5 (MAX 140), each row citing a clause like cl.5.1
consents, POPIA §26 special-personal-info: versioned, per-type (disability_data / marketing / analytics), revocable

StokvelOS

19 tables across 6 modular schema files · region-pinned jnb1

contributions / payouts / loans, each with a UNIQUE receipt_number and recorded_by
audit_log is append-only by design: its only RLS policy is SELECT-for-admins, so every write goes through the service role
every financial record dual-writes its notion_page_id; stokvels holds five notion_*_db_id columns

WatchSankofa

26 tables · 26 indexes · RLS on all 26 · 50 policies · trigger-driven

content (creators, works, series, albums, collections) + discovery (taste_profiles, history)
denormalized counters kept live by triggers: works.view/heart/comment/share_count + trending_score/is_trending
fan-out triggers write activity_feed + notifications on new work / follow / note; check_and_award_badges tiers badges automatically

Your system, documented like this

Every client build ships with this same data dictionary, you own the schema, the migrations and the docs.

Start a project Security & Trust →