Schema Overview

Map of the DirectoryLaunch Supabase schema — every table, what it stores, RLS basics, timestamp triggers and the is_admin and role admin quirk.

All tables live in one file: supabase/schema.sql. That file is the single source of truth. Run it once when you set up Supabase (see Supabase Setup) and forget about it.

The important tables

Table	What it stores
`users`	User profiles — name, email, avatar, role, `is_admin` flag, notification prefs
`apps`	Every submitted project — name, slug, URL, description, categories, status, upvotes, views, and optional location (`latitude`, `longitude`, `address_*`)
`categories`	Directory categories with `sphere`, `slug`, `sort_order`, `icon`, `color`
`payments`	One row per Stripe transaction — amount, provider, status, related app/user
`ratings`	1–5 star reviews on projects
`comments`	Comment threads on project pages
`bookmarks`	"Save for later" per user
`newsletter`	Email newsletter subscribers
`promotions`	Active paid promo placements (banner, catalog, detail)
`partners`	Sponsor / partner entries shown in sidebar
`analytics`	Page view + event data for the built-in dashboard
`competitions`	Weekly competitions, entries and winners
`site_settings`	Key/value store for admin knobs flipped without deploying. Keys include `"theme"` (custom colors), `"layout"` / `"display"` (show/hide logo & image), and `"map"` (default projection + lighting)
`changelog`	Entries that render on `/changelog`
`email_notifications`	Log of emails sent (for debugging / audit)
`external_webhooks`	Discord / Slack webhook configs, managed via admin UI
`link_type_changes`	Audit trail when admins toggle backlink type

Location columns on `apps` (optional)

When the map feature is used, listings carry geography. These columns are nullable and populated only when a submitter picks a location:

Column	Type	Notes
`latitude` / `longitude`	`NUMERIC`	Coordinates. A partial index `idx_apps_location` covers rows where both are set.
`address_country` / `address_city` / `address_state`	`TEXT`	Structured address parts.
`address_exact`	`TEXT`	The full formatted address string.

Rows without coordinates simply don't appear on the map — no migration is needed to adopt the feature.

Security (RLS)

Every table has Row Level Security enabled. That means queries from the browser (using the publishable / anon key) are filtered by policies defined in schema.sql. Typical policies:

Anyone can read apps where status = 'live'
Users can insert/update their own rows (user_id = auth.uid())
Admins can do anything (role = 'admin')

Server code that uses getSupabaseAdmin() (service role) bypasses RLS — it sees everything.

Timestamps — set by triggers

Every table has created_at and updated_at columns. Triggers set them automatically — don't include them in your insertOne() / updateOne() calls:

// GOOD — trigger handles it
await db.insertOne('apps', { name: 'Foo', slug: 'foo', status: 'pending' });
 
// WRONG — overrides the trigger, may break things
await db.insertOne('apps', { name: 'Foo', created_at: new Date() });

Admin field quirk

Two columns do similar things:

Column	Type	Checked by
`is_admin`	boolean	`isAdmin()` helper in code
`role`	text	RLS policies in the DB

Keep them in sync when granting admin access. Set is_admin = true and role = 'admin'.

Tables in the schema but not in the `db` layer

A handful of tables are accessed via the Supabase client directly (not through db.find()):

changelog
email_notifications
site_settings
link_type_changes

They still have full RLS and follow the same patterns — just not wrapped in the Mongo-style API.