supabase-migration

name: supabase-migration description: "Generate Supabase database migrations for the Accounted project with correct RLS policies, triggers, indexes, and Swedish accounting constraints. Use when creating new tables, adding columns, modifying constraints (e.g. source_type CHECK), or any DDL operation on the Supabase database. Ensures legal compliance with BFL 7-year retention, immutability triggers, and period lock enforcement."

Supabase Migration Generator

Migration Numbering

Early migrations used the sequential series 20240101000001–20240101000038; the project has long since moved to real timestamps. New migrations use a current UTC timestamp YYYYMMDDHHMMSS_description.sql (e.g. 20260603120000_add_x.sql). Never reuse or back-date a number.

New Table — Complete Template

Accounted is multi-tenant: company-scoped business data is owned by company_id and secured with the user_company_ids() RLS helper (NOT auth.uid() = user_id). Every new company-scoped table requires ALL of these. Missing any is a bug.

-- 1. Table with UUID PK + company_id + user_id FKs
CREATE TABLE public.tablename (
  id         uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  company_id uuid NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
  user_id    uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  -- domain columns --
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- 2. RLS
ALTER TABLE public.tablename ENABLE ROW LEVEL SECURITY;

-- 3. All four CRUD policies — scope to the user's companies
CREATE POLICY "view own-company tablename"
  ON public.tablename FOR SELECT USING (company_id IN (SELECT user_company_ids()));
CREATE POLICY "insert own-company tablename"
  ON public.tablename FOR INSERT WITH CHECK (company_id IN (SELECT user_company_ids()));
CREATE POLICY "update own-company tablename"
  ON public.tablename FOR UPDATE USING (company_id IN (SELECT user_company_ids()));
CREATE POLICY "delete own-company tablename"
  ON public.tablename FOR DELETE USING (company_id IN (SELECT user_company_ids()));

-- 4. Indexes (minimum: company_id + any FK/filter columns)
CREATE INDEX idx_tablename_company_id ON public.tablename (company_id);

-- 5. updated_at trigger
CREATE TRIGGER set_updated_at_tablename
  BEFORE UPDATE ON public.tablename
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

-- 6. Audit trigger
CREATE TRIGGER audit_tablename
  AFTER INSERT OR UPDATE OR DELETE ON public.tablename
  FOR EACH ROW EXECUTE FUNCTION public.write_audit_log();

-- 7. Reload PostgREST schema cache (required after structural DDL)
NOTIFY pgrst, 'reload schema';

Child Tables (No Direct user_id)

Tables owned via parent use subquery-based RLS:

CREATE POLICY "view own-company child" ON public.child_table
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.parent_table pt
      WHERE pt.id = child_table.parent_id
        AND pt.company_id IN (SELECT user_company_ids())
    )
  );
-- Repeat for INSERT (WITH CHECK), UPDATE, DELETE

Expanding source_type CHECK

When adding a new journal entry source type, expand the constraint:

ALTER TABLE public.journal_entries
  DROP CONSTRAINT IF EXISTS journal_entries_source_type_check;
ALTER TABLE public.journal_entries
  ADD CONSTRAINT journal_entries_source_type_check
  CHECK (source_type IN (
    'manual','bank_transaction','invoice_created','invoice_paid',
    'invoice_cash_payment','credit_note','salary_payment',
    'opening_balance','year_end','storno','correction','import','system',
    'supplier_invoice_registered','supplier_invoice_paid',
    'supplier_invoice_cash_payment','supplier_credit_note',
    'NEW_TYPE_HERE'
  ));

Protected Triggers — NEVER Modify

Migration 20240101000017 defines legally-required triggers:

• enforce_journal_entry_immutability — blocks edits/deletes on posted/reversed entries
• enforce_journal_entry_line_immutability — blocks line mods on committed entries
• enforce_period_lock — blocks writes to closed/locked periods
• block_document_deletion — prevents deletion of docs linked to committed entries
• enforce_retention_journal_entries — 7-year retention
• set_committed_at / calculate_retention_expiry — auto-set timestamps

Apply

Use mcp__plugin_supabase_supabase__apply_migration with snake_case name. Never modify existing migration files.

Common Mistakes

1. Missing ENABLE ROW LEVEL SECURITY — table publicly accessible
2. Missing DELETE policy — users can't remove own records
3. Missing updated_at trigger — column never updates
4. Missing audit trigger — no audit trail
5. Hardcoded UUIDs in data migrations — use subqueries
6. Forgetting source_type CHECK expansion for new entry generators