-- ============================================================================ -- SAG Manager — 0002 finance -- ============================================================================ -- Bills, Plaid (accounts, encrypted access tokens, txn overrides, splits), -- subscriptions, owner-draws, Toast imports + labor scaffold. -- -- Modeled on: -- - Bill (src/lib/finance/bill-types.ts) -- - ConnectedAccount (src/components/app/plaid-connect.tsx; key sag.plaid.accounts) -- - TxnOverride (src/lib/finance/txn-overrides.ts) -- - TxnSplit / TxnSplitPart (src/lib/finance/txn-splits.ts) — parts as JSONB -- - StoredSubscription (src/lib/finance/subscriptions-store.ts) -- - Subscription ignore-list (sag.finance.subscription_ignored) -- - OwnerDraw (src/lib/income/owner-draws.ts) -- - Toast imports (sag.toast.imports) and a stub for the future labor import -- ============================================================================ create extension if not exists "pgcrypto"; -- ───────────────────────────────────────────────────────────────────────── -- bills — Bill v2 from bill-types.ts -- ───────────────────────────────────────────────────────────────────────── create table if not exists public.bills ( id uuid primary key default gen_random_uuid(), entity_slug text not null, vendor text not null, amount numeric(14,2) not null, due_date timestamptz not null, status text not null default 'Unscheduled' check (status in ('Unscheduled','Pending Review','Scheduled','Paid','Overdue','Cancelled')), recurring boolean not null default false, recurrence_pattern text, notes text, category text, account_number text, source text not null default 'manual' check (source in ('manual','gmail','pdf','csv','plaid')), source_ref text, -- gmail thread id, plaid txn id, filename, etc. line_items jsonb not null default '[]'::jsonb, -- BillLineItem[] attachments jsonb not null default '[]'::jsonb, -- BillAttachment[] payment jsonb, -- BillPayment | null created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); create index if not exists bills_entity_due_idx on public.bills (entity_slug, due_date); create index if not exists bills_status_idx on public.bills (status); create index if not exists bills_recurring_idx on public.bills (recurring) where recurring; drop trigger if exists bills_set_updated_at on public.bills; create trigger bills_set_updated_at before update on public.bills for each row execute function public.set_updated_at(); -- ───────────────────────────────────────────────────────────────────────── -- plaid_accounts — ConnectedAccount shape from plaid-connect.tsx -- One row per Plaid account; `item_id` groups accounts under one Plaid Item. -- ───────────────────────────────────────────────────────────────────────── create table if not exists public.plaid_accounts ( id uuid primary key default gen_random_uuid(), account_id text not null unique, -- Plaid's stable account id item_id text not null, -- Plaid Item id name text not null, official_name text, type text, subtype text, mask text, balance numeric(14,2), currency text, institution text, entity_slug text, -- user-assigned attribution; null = unassigned connected_at timestamptz not null default now(), created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); create index if not exists plaid_accounts_item_idx on public.plaid_accounts (item_id); create index if not exists plaid_accounts_entity_idx on public.plaid_accounts (entity_slug); drop trigger if exists plaid_accounts_set_updated_at on public.plaid_accounts; create trigger plaid_accounts_set_updated_at before update on public.plaid_accounts for each row execute function public.set_updated_at(); -- ───────────────────────────────────────────────────────────────────────── -- plaid_access_tokens — encrypted Plaid access token per Item -- IMPORTANT: `access_token_encrypted` MUST be encrypted at the application -- layer (libsodium / KMS / pgsodium) before insert. The column type stays -- text so the encrypted ciphertext is portable; raw Plaid tokens should -- NEVER be persisted to this column. -- ───────────────────────────────────────────────────────────────────────── create table if not exists public.plaid_access_tokens ( item_id text primary key, -- Plaid Item id access_token_encrypted text not null, -- ciphertext (e.g. pgsodium / KMS-wrapped) encryption_key_id text, -- which key encrypted this (for rotation) institution text, created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); drop trigger if exists plaid_access_tokens_set_updated_at on public.plaid_access_tokens; create trigger plaid_access_tokens_set_updated_at before update on public.plaid_access_tokens for each row execute function public.set_updated_at(); -- ───────────────────────────────────────────────────────────────────────── -- plaid_txn_overrides — F5 single-attribution overrides for a Plaid txn -- Mirrors TxnOverride in txn-overrides.ts. -- ───────────────────────────────────────────────────────────────────────── create table if not exists public.plaid_txn_overrides ( txn_id text primary key, -- Plaid transactionId category text, entity_slug text, created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); create index if not exists plaid_txn_overrides_entity_idx on public.plaid_txn_overrides (entity_slug); drop trigger if exists plaid_txn_overrides_set_updated_at on public.plaid_txn_overrides; create trigger plaid_txn_overrides_set_updated_at before update on public.plaid_txn_overrides for each row execute function public.set_updated_at(); -- ───────────────────────────────────────────────────────────────────────── -- plaid_txn_splits — F6 split-transaction across N entities / categories -- The TxnSplit shape is: { txnId, parts: TxnSplitPart[], updatedAt }. -- Parts are stored as JSONB so the validation rules in txn-splits.ts -- (>= 2 parts, sum to 100, per-part >0) stay enforced in app code rather -- than splintered across SQL constraints — easier to evolve. -- ───────────────────────────────────────────────────────────────────────── create table if not exists public.plaid_txn_splits ( txn_id text primary key, -- Plaid transactionId parts jsonb not null, -- TxnSplitPart[] — validated app-side created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); drop trigger if exists plaid_txn_splits_set_updated_at on public.plaid_txn_splits; create trigger plaid_txn_splits_set_updated_at before update on public.plaid_txn_splits for each row execute function public.set_updated_at(); -- ───────────────────────────────────────────────────────────────────────── -- subscriptions — F1 manual store (StoredSubscription) -- Amounts normalized to monthly equivalent regardless of cadence. -- ───────────────────────────────────────────────────────────────────────── create table if not exists public.subscriptions ( id uuid primary key default gen_random_uuid(), vendor text not null, monthly_amount numeric(14,2) not null, cadence text not null check (cadence in ('weekly','biweekly','monthly','quarterly','annual','irregular')), entity_slug text, payment_method text, next_renewal_date timestamptz, notes text, confirmed_from_detected text, -- detector cluster id (FNV-hash of normalized merchant) created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); create index if not exists subscriptions_entity_idx on public.subscriptions (entity_slug); create index if not exists subscriptions_renewal_idx on public.subscriptions (next_renewal_date); drop trigger if exists subscriptions_set_updated_at on public.subscriptions; create trigger subscriptions_set_updated_at before update on public.subscriptions for each row execute function public.set_updated_at(); -- ───────────────────────────────────────────────────────────────────────── -- subscription_ignored — merchant strings the user has hidden from the -- subscription detector. Key = normalized merchant string. -- ───────────────────────────────────────────────────────────────────────── create table if not exists public.subscription_ignored ( merchant_normalized text primary key, ignored_at timestamptz not null default now() ); -- ───────────────────────────────────────────────────────────────────────── -- owner_draws — P2 manual log of personal owner draws from SAG entities -- Mirrors OwnerDraw in owner-draws.ts. -- ───────────────────────────────────────────────────────────────────────── create table if not exists public.owner_draws ( id uuid primary key default gen_random_uuid(), draw_date timestamptz not null, amount numeric(14,2) not null check (amount > 0), from_entity_slug text not null, method text, note text, created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); create index if not exists owner_draws_date_idx on public.owner_draws (draw_date desc); create index if not exists owner_draws_entity_idx on public.owner_draws (from_entity_slug); drop trigger if exists owner_draws_set_updated_at on public.owner_draws; create trigger owner_draws_set_updated_at before update on public.owner_draws for each row execute function public.set_updated_at(); -- ───────────────────────────────────────────────────────────────────────── -- toast_imports — raw Toast Sales Category CSV imports -- Mirrors SavedImport in dashboard-stats.tsx (id, name, uploadedAt, rowCount, -- rows: ToastSalesRow[]). The `rows` payload is captured as JSONB for fidelity -- with the original CSV and so the importer can be replayed. -- ───────────────────────────────────────────────────────────────────────── create table if not exists public.toast_imports ( id uuid primary key default gen_random_uuid(), name text not null, -- "sales-breakdown-2026-04-01.csv" uploaded_at timestamptz not null default now(), row_count integer not null, rows jsonb not null, -- ToastSalesRow[] notes text, created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); create index if not exists toast_imports_uploaded_idx on public.toast_imports (uploaded_at desc); drop trigger if exists toast_imports_set_updated_at on public.toast_imports; create trigger toast_imports_set_updated_at before update on public.toast_imports for each row execute function public.set_updated_at(); -- ───────────────────────────────────────────────────────────────────────── -- toast_labor — reserved for the future "Hours and Earnings by Range" -- importer. Schema matches ToastLaborRow in payroll-prep.ts. Empty for now; -- callers (rollupPayroll) currently pass `[]`. -- ───────────────────────────────────────────────────────────────────────── create table if not exists public.toast_labor ( id uuid primary key default gen_random_uuid(), labor_date timestamptz not null, -- maps to ToastLaborRow.date employee_id text not null, -- raw Toast name OR resolved Employee.id entity_slug text, hours numeric(8,2) not null default 0, wages numeric(14,2), tips numeric(14,2), raw jsonb, -- preserve the source row created_at timestamptz not null default now(), updated_at timestamptz not null default now() ); create index if not exists toast_labor_date_idx on public.toast_labor (labor_date desc); create index if not exists toast_labor_employee_idx on public.toast_labor (employee_id); drop trigger if exists toast_labor_set_updated_at on public.toast_labor; create trigger toast_labor_set_updated_at before update on public.toast_labor for each row execute function public.set_updated_at();