Supabase & the projects table

One Supabase project backs the entire system. Both the marketing site and the admin read and write the same projects table — the site through capability RPCs, the admin directly as an authenticated user.

The Supabase project ref is recorded in each repo’s CLAUDE.md; it is not reproduced here (it is the value of a VITE_ env var). Region: East US / North Virginia.

Two schemas — same project, fully isolated

Schema	Used by	Purpose
public	Production (sitecrate.ca, admin.sitecrate.ca)	Real client data
staging	Staging (PR previews)	Test data — never touches prod

Both schemas are exposed via PostgREST and have an identical projects structure. src/lib/supabase.js reads the VITE_SUPABASE_SCHEMA env var (defaults to 'public'). Staging builds set VITE_SUPABASE_SCHEMA=staging.

Note

Production does not set VITE_SUPABASE_SCHEMA — it relies on the || 'public' fallback in supabase.js. Staging routes to the isolated schema purely through that one env var. See Environments.

The projects table

Column	Type	Notes
id	uuid	Primary key, gen_random_uuid()
status_token	uuid	Capability token — used in the /#status/{token} URL
client_name, client_email	text	Client contact
business_name	text	Display name throughout the admin
industry, business_description, target_customer, goals	text	Intake brief
has_logo, has_copy	boolean	Brand asset status
domain_name, color_vibe	text	Brand inputs
tier	text	presence / growth / platform (legacy: starter / standard)
billing	text	one-time / monthly (monthly = care plan)
timeline	text	Client’s requested timeline
stage	text	new → brief → building → review → live
stage_changed_at	timestamptz	Used for stuck-project detection in the admin
preview_url	text	Set by Ahmed — shown on the status page at review/live
client_approved	boolean	Set by the client on the status page
client_feedback, client_feedback_at	text / timestamptz	Client revision notes
admin_notes	text	Internal only — never shown to the client
created_at, updated_at	timestamptz	Server-side defaults

Stage lifecycle

new ──▶ brief ──▶ building ──▶ review ──▶ live

• stage_changed_at is stamped on each transition; the admin’s isStuck() check compares it against STAGE_WARN_DAYS to surface stalled projects.
• Moving to review triggers the review email only if preview_url is set. Moving to live triggers the launch email.

Tiers & billing

tier drives pricing and which Build Brief prompt the admin generates:

Tier	One-time	Monthly	Build Brief
presence	$997	$49	Tier 1 prompt
growth	$3997	$149	Tier 2 prompt
platform	custom	custom	— (custom-quoted)

starter and standard are legacy tiers, kept only so older projects render correct historical values. See the admin revenue math.

Running ad-hoc queries

Against production, via the Supabase Management API (the access token lives in ~/.supabase/access-token; the project ref is in CLAUDE.md):

curl -s "https://api.supabase.com/v1/projects/<project-ref>/database/query" \
  -H "Authorization: Bearer $(cat ~/.supabase/access-token)" \
  -H "Content-Type: application/json" \
  -d '{"query": "SELECT id, business_name, stage FROM projects ORDER BY created_at DESC LIMIT 10;"}'

RLS is enabled on both schemas. The anon role has no direct table access — the next page explains how all public access is mediated instead.