Overview

aSaaSin uses Supabase (Postgres + RLS). The schema is minimal, practical, and built for real SaaS flows — profiles, plans, subscriptions, projects, API tokens, roadmap voting, and key/value config.

Relationships

auth.users ───┐
              ├─ public.profiles (1:1 via id, created by trigger on auth.users insert)
              ├─ public.subscriptions (1:1 user_id)
              ├─ public.projects (1:N user_id)
              └─ public.api_tokens (1:N user_id)

public.subscription_plans (1:N) ── public.subscriptions (subscription_plan_id)

public.roadmap_votes (standalone per slug)
public.config        (key/value)

Security model

  • RLS is enabled on all user-owned tables.
  • Users can only read/update their own rows using auth.uid().
  • subscription_plans is public read-only; writes are disallowed.
  • Profile rows are created via handle_new_user trigger on auth.users.
  • Voting uses SECURITY DEFINER functions with tight scope.
  • Service role keys are server-only (webhooks, admin tasks).

Do not expose the service role key to the client. Keep write operations that cross user boundaries on the server (e.g. webhooks).

Core tables

profiles

  • id matches auth.users.id.
  • Policies: public select; users can insert/update their own row.
  • Trigger: inserts a profile on new auth.users record.

subscription_plans

  • Unique on (name, billing_cycle).
  • features (JSONB) holds capability flags, e.g. maxApiTokens, maxProjects.
  • Public read; no writes via RLS.

subscriptions

  • One active row per user (user_id unique).
  • References subscription_plans and stores Lemon Squeezy ids.
  • Policies: user can read/update own row. Written by webhook.

projects

  • status: draft | active | completed.
  • Full CRUD restricted to the owner.

api_tokens

  • Per-user tokens; consider hashing the private token and showing once.
  • Owner-only read/insert/delete.

roadmap_votes

  • Per slug counters, updated via increment_vote(slug) / decrement_vote(slug).
  • Readable by anyone; updates allowed for counting.

config

  • Simple key/value (e.g. maintenance mode toggles).

Plans & subscription mapping

Plans are defined in subscription_plans with features JSONB for capability flags. The Lemon Squeezy variant id is stored per plan and mapped in the webhook to update the user’s row in subscriptions.

Keep (name, billing_cycle) unique. Customize pricing and add lemon_variant_id per plan. Extend features as your product evolves.

Local development

You can run the full database locally using the Supabase CLI:

  1. Install the CLI and start services: supabase start
  2. Set env vars in .env.local:
    • NEXT_PUBLIC_SUPABASE_URL
    • NEXT_PUBLIC_SUPABASE_ANON_KEY
    • SUPABASE_SERVICE_ROLE_KEY (server-only; used in API routes/webhooks)
  3. Apply schema (migrations/SQL): run your SQL in the Supabase SQL editor or re-seed with supabase db reset.
  4. (Optional) Seed plans: insert free, plus, pro for both billing cycles and set lemon_variant_id if you already created variants.

Never commit real service role keys. Use separate keys for local, staging, and production.

Migrations & environments

  1. Create a migration
    • Empty file: supabase migration new <name> then add SQL
    • From changes: supabase db diff -f <name>.sql
  2. Apply & test locally
    • supabase db reset (rebuilds from migrations and runs seeds)
  3. Push to a remote project
    • Link: supabase link --project-ref <ref>
    • Deploy: supabase db push (use --dry-run first)
  4. Pull remote schema
    • supabase db pull to capture remote changes into a migration
  5. Recommended flow
    • Branch → changes → db diff → db reset → commit → push/deploy