backend/database/migrations/040_invoice_public_id.sql

-- Opaque public invoice id (unguessable link token; not the sequential PK)

ALTER TABLE invoices
  ADD COLUMN IF NOT EXISTS public_id VARCHAR(32);

-- Backfill existing rows with unique 12-digit codes
DO $$
DECLARE
  r RECORD;
  candidate TEXT;
BEGIN
  FOR r IN SELECT id FROM invoices WHERE public_id IS NULL LOOP
    LOOP
      candidate := lpad((floor(random() * 900000000000) + 100000000000)::bigint::text, 12, '0');
      EXIT WHEN NOT EXISTS (SELECT 1 FROM invoices WHERE public_id = candidate);
    END LOOP;
    UPDATE invoices SET public_id = candidate WHERE id = r.id;
  END LOOP;
END $$;

ALTER TABLE invoices
  ALTER COLUMN public_id SET NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS idx_invoices_public_id
  ON invoices (public_id);