Loading…
← Handbook
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.
.jpg&w=3840&q=75)
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.
.jpg&w=3840&q=75)
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.
.jpg&w=3840&q=75)
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.)
subscribersNewsletter 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 |
|---|---|---|
idPK | UUID | uuid_generate_v4() |
emailUNIQUENOT NULLINDEX | TEXT | One row per address |
source | TEXT | Capture point: 'homepage', 'quote_form', 'build_shop'… |
sequenceNOT NULL | TEXT | Which nurture track; default 'mirembe_brief' |
sequence_stepINDEX | INTEGER | Position in the track; partial index where unsubscribed_at IS NULL |
languageNOT NULL | TEXT | Locale for sends; default 'en' |
confirmed_at | TIMESTAMPTZ | Double opt-in confirmation |
unsubscribed_at | TIMESTAMPTZ | Suppresses all further sends |
created_atNOT NULL | TIMESTAMPTZ | DEFAULT NOW() |
Indexes
- idx_subscribers_email
- idx_subscribers_sequence (sequence, sequence_step) WHERE unsubscribed_at IS NULL
quote_requestsInbound 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 |
|---|---|---|
idPK | UUID | uuid_generate_v4() |
nameNOT NULL | TEXT | — |
emailNOT NULLINDEX | TEXT | Indexed for lookup |
service_wingNOT NULL | TEXT | Which wing the brief is for |
project_typeNOT NULL | TEXT | — |
budget_range | TEXT | Self-reported band |
timeline | TEXT | Desired delivery window |
descriptionNOT NULL | TEXT | The brief itself |
statusNOT NULLINDEX | quote_status | Lifecycle enum; default 'pending' |
admin_notes | TEXT | Internal triage notes |
deleted_atsoft-delete | TIMESTAMPTZ | Soft delete |
Indexes
- idx_quote_requests_status (status) WHERE deleted_at IS NULL
- idx_quote_requests_email
productsThe 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 |
|---|---|---|
idPK | UUID | uuid_generate_v4() |
slugUNIQUENOT NULLINDEX | TEXT | URL key |
nameNOT NULL | TEXT | — |
tierNOT NULL | product_tier | access | professional | enterprise |
price_zarNOT NULL | NUMERIC(10,2) | Base price |
price_usd | NUMERIC(10,2) | Optional published USD |
billing_typeNOT NULL | billing_type | Default 'monthly' |
statusNOT NULLINDEX | product_status | Drives the public read policy |
featuresJSONBNOT NULL | JSONB | Feature list; default '[]' |
updated_atNOT NULL | TIMESTAMPTZ | Maintained by trigger |
deleted_atsoft-delete | TIMESTAMPTZ | Soft delete |
Indexes
- idx_products_status (status, sort_order)
- idx_products_slug
- trg_products_updated_at (BEFORE UPDATE)
ordersPurchases 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 |
|---|---|---|
idPK | UUID | uuid_generate_v4() |
user_idFKNOT NULLINDEX | UUID | → auth.users(id); RLS boundary |
product_idFKNOT NULL | UUID | → products(id) |
amount_zarNOT NULL | NUMERIC(10,2) | — |
currencyNOT NULL | TEXT | Default 'ZAR' |
payment_providerNOT NULL | payment_provider | payfast (canonical) |
payment_reference | TEXT | Gateway reference |
statusNOT NULLINDEX | order_status | Default 'pending'; paid only via verified ITN |
referral_code | TEXT | Attribution to a referral |
discount_appliedNOT NULL | NUMERIC(10,2) | Referral discount; default 0 |
deleted_atsoft-delete | TIMESTAMPTZ | Soft delete |
Indexes
- idx_orders_user_id WHERE deleted_at IS NULL
- idx_orders_status WHERE deleted_at IS NULL
case_studiesPublished 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 |
|---|---|---|
idPK | UUID | uuid_generate_v4() |
slugUNIQUENOT NULLINDEX | TEXT | URL key |
titleNOT NULL | TEXT | — |
client_nameNOT NULL | TEXT | — |
excerptNOT NULL | TEXT | Card + meta summary |
body | TEXT | Full write-up |
service_wingNOT NULL | TEXT | Which wing delivered it |
resultsJSONBNOT NULL | JSONB | Metric→value pairs; default '{}' |
featuredNOT NULL | BOOLEAN | Homepage pull; default false |
published_atINDEX | TIMESTAMPTZ | Gates the public read policy |
updated_atNOT NULL | TIMESTAMPTZ | Maintained by trigger |
deleted_atsoft-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)
referralsThe 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 |
|---|---|---|
idPK | UUID | uuid_generate_v4() |
referrer_user_idFKNOT NULLINDEX | UUID | → auth.users(id); RLS boundary |
referee_emailNOT NULL | TEXT | Invited address |
codeUNIQUENOT NULLINDEX | TEXT | Shareable code |
credit_zarNOT NULL | NUMERIC(10,2) | Referrer credit; default 2000 |
discount_percentNOT NULL | NUMERIC(5,2) | Referee discount; default 5 |
redeemed_at | TIMESTAMPTZ | When the referee converted |
expires_atNOT 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
22 tables · RLS on all 22 · 29 indexes
- profiles (extends auth.users; subscription_tier, Nova usage counters, POPIA consent)
- nova_conversations (one row/user, JSONB history)
- expenses / income_entries (denormalised month_year for fast aggregation)
AdminOS
21 tables · 100% RLS · 31 migrations
- Tenant isolation: RLS compares JWT tenant_id to row tenant_id on every table
- conversations as a first-class entity (intent/sentiment/status at thread level)
- audit_log with UPDATE/DELETE revoked at the DB level — append-only, even for the service role
K53 Drill Master
1 Supabase table + 5 localStorage stores
- subscribers (RLS: read-own, insert service-role only; renewals via upsert)
- k53_sr_v1 — the SM-2 spaced-repetition card state
- k53_usage / k53_premium / k53_history_v1 / k53_streak_v2
Sanyu Botanicals
11 tables
- products (variants, combos, live stock_count)
- orders / qr_cards / angels (loyalty)
- journal_entries (private AI hair journal) + journal_posts (public CMS)
The Selves of Us
~24 tables · RLS + policies · 7 enums
- profiles (role: founder | editor | studio) → stories → {editorial_messages, story_versions, comments, mirrors}
- story_status enum: draft → pending → needs_edits → resubmitted → approved → published
- soft-delete everywhere (deleted_at) — never a hard delete
True Access
~10 core tables · PostGIS · RLS
- locations (PostGIS GIST index, compliance_score 0–100, mapbox_feature_id)
- accessibility_features (SANS 10400-S checklist: 9 required ×10 + 8 optional ×5, MAX 130)
- audit_submissions (JSONB checklist) · businesses (plan_tier) · orders (paystack_reference)
StokvelOS
~19 tables across modular schema files
- contributions / payouts / loans — each with a UNIQUE receipt_number and recorded_by
- every record stores its notion_page_id (the transparency mirror)
- immutable audit_log (before_state / after_state JSONB)
WatchSankofa
15 tables · 12 indexes · RLS + triggers
- creators, works, series, albums, tips, follows, hearts, history, taste_profiles
- collabs / collab_applications
- DB-maintained counter triggers (heart_count, follower_count, works_count)
.jpg&w=3840&q=75)
Your system, documented like this
Every client build ships with this same data dictionary — you own the schema, the migrations and the docs.