🪙 [BE] 🟨 Tip Jaar

# 💸 Tip-Jar Backend (BE) — Ship Ticket

> **Cycle:** 1 six-day cycle (ships with FE)
> **Goal:** Immutable tip ledger, fast summaries, automated payouts, secure webhooks, and admin ops.
> **Out of scope:** Multi-currency, disputes auto-reconciliation, dynamic splits, admin UI.

---

# 🍽 Appetite

Finish all server-side functionality for Tip-Jar in **one 6-day cycle**.

# ❓ Problem

Creators need a **real-time immutable tip ledger** and **automated monthly payouts**. Finance needs **audit logs** and **retry** paths when Stripe fails.

# 💡 Solution (fat-marker)

1. **Ledger**: `record_tip()` RPC (idempotent on `payment_intent`) appends to `public.tips` + audit table.
2. **Summaries**: `creator_earnings_mv` (cards + 12-week series), hourly refresh.
3. **Payouts**: `payout_month()` enqueues monthly rows → scheduled job creates Stripe **Transfers** → updates `creator_payouts` + audit.
4. **APIs + RLS**: creator-scoped reads, admin ops, Supabase Realtime on `tips`.

```mermaid
graph TD
subgraph DB Objects
C(creators)
T(tips)
CE(creator_earnings_mv)
CP(creator_payouts)
LOG(audit_tip_events)
PLOG(audit_payout_events)
end

subgraph RPC & Cron
RT(record_tip)
PM(payout_month)
end

subgraph Integration
WH(Stripe Webhook)
CT("Cron / pg_cron|Vercel|Supabase Edge")
SR(Service-role API)
end

WH --> RT
RT --> T
T --> CE
CE --> PM
PM --> CP
PM --> PLOG
CT --> PM
SR --admin--> CP
```

---

# 📐 Public Interfaces (final; FE aligned)

* **POST** `/api/tip/create-checkout-session` → `200 { url }` | `400 AMOUNT_NOT_ALLOWED | 400 INVALID_CREATOR | 409 CREATOR_NOT_ONBOARDED | 500`
* **POST** `/api/stripe/webhook` → calls `record_tip()` (idempotent)
* **GET** `/api/creator/earnings?creatorId=UUID` → **EarningsDTO** (MV or fallback)
* **GET** `/api/creator/tips?creatorId=UUID&limit=50&cursor?` → `{ data: TipDTO[], nextCursor? }`
* **GET** `/api/creator/payouts?creatorId=UUID` → `PayoutDTO[]`
* **Admin (service/finance only)**

* `POST /api/admin/refresh-earnings-mv`
* `POST /api/admin/payout-month?yyyymm=YYYY-MM`

---

# 🗄️ Schema (migrations)

### 001\_tip\_ledger.sql

```sql
create table if not exists public.tips (
id uuid primary key default gen_random_uuid(),
creator_id uuid not null,
tipper_id uuid,
content_id uuid,
amount_cents int not null check (amount_cents between 100 and 50000),
fee_cents int not null default 0,
platform_fee_cents int not null default 0,
stripe_payment jsonb not null, -- {payment_intent, charge, session, ...}
created_at timestamptz not null default now()
);

create unique index if not exists uq_tips_payment_intent
on public.tips ((stripe_payment->>'payment_intent'));

create index if not exists idx_tips_creator_created_at
on public.tips (creator_id, created_at desc);

alter table public.tips enable row level security;
create policy tips_read_own on public.tips for select using (auth.uid() = creator_id);
```

### 002\_creator\_payouts.sql

```sql
create table if not exists public.creator_payouts (
id uuid primary key default gen_random_uuid(),
creator_id uuid not null,
earnings_month char(7) not null, -- YYYY-MM
payout_cents int not null,
stripe_transfer text,
status text not null check (status in ('queued','sent','failed')),
run_at timestamptz not null default now(),
note text
);

create unique index if not exists uq_payouts_creator_month
on public.creator_payouts (creator_id, earnings_month);

alter table public.creator_payouts enable row level security;
create policy payouts_read_own on public.creator_payouts for select using (auth.uid() = creator_id);
```

### 003\_audit\_tables.sql

```sql
create table if not exists public.audit_tip_events (
id bigserial primary key,
occurred_at timestamptz default now(),
event_type text,
payment_intent text,
creator_id uuid,
payload jsonb
);

create table if not exists public.audit_payout_events (
id bigserial primary key,
occurred_at timestamptz default now(),
event_type text,
creator_id uuid,
earnings_month char(7),
transfer_id text,
payload jsonb
);
```

### 004\_creator\_earnings\_mv.sql

```sql
create materialized view if not exists public.creator_earnings_mv as
select
creator_id,
coalesce(sum(case when date_trunc('month', created_at)=date_trunc('month', now())
then amount_cents end),0)::int as gross_month_cents,
coalesce(sum(case when date_trunc('month', created_at)=date_trunc('month', now())
then (amount_cents*0.70)::int - fee_cents end),0)::int as net_month_cents,
coalesce(sum(case when date_trunc('month', created_at)=date_trunc('month', now()-interval '1 month')
then amount_cents end),0)::int as gross_prev_month_cents,
coalesce(sum(case when date_trunc('month', created_at)=date_trunc('month', now()-interval '1 month')
then (amount_cents*0.70)::int - fee_cents end),0)::int as net_prev_month_cents,
coalesce(sum(amount_cents),0)::int as gross_all_time_cents,
coalesce(sum((amount_cents*0.70)::int - fee_cents),0)::int as net_all_time_cents,
(
with weeks as (
select generate_series(date_trunc('week', now()) - interval '11 week',
date_trunc('week', now()), interval '1 week')::date wk
), agg as (
select creator_id, date_trunc('week', created_at)::date wk, sum(amount_cents)::int gross
from public.tips group by 1,2
)
select jsonb_agg(jsonb_build_object('weekStartISO', w.wk::text,
'gross_cents', coalesce(a.gross,0)) order by w.wk)
from weeks w left join agg a using (wk)
) as weekly_points
from public.tips
group by creator_id;
```

---

# 🧮 RPCs (SQL functions)

### 005\_rpc\_record\_tip.sql

```sql
create or replace function public.record_tip(
pi text,
p_creator uuid,
p_amount int,
p_fee int,
p_tipper uuid default null,
p_content uuid default null,
p_stripe jsonb default '{}'::jsonb
) returns uuid
language plpgsql
security definer
as $$
declare v_id uuid;
begin
insert into public.tips (creator_id, tipper_id, content_id, amount_cents, fee_cents,
platform_fee_cents, stripe_payment)
values (p_creator, p_tipper, p_content, p_amount, p_fee, round(p_amount*0.30),
p_stripe || jsonb_build_object('payment_intent', pi))
on conflict ((stripe_payment->>'payment_intent'))
do update set stripe_payment = public.tips.stripe_payment
returning id into v_id;

insert into public.audit_tip_events (event_type, payment_intent, creator_id, payload)
values ('tip.recorded', pi, p_creator, p_stripe)
on conflict do nothing;

return v_id;
end $$;
```

### 006\_rpc\_payout\_month.sql

```sql
create or replace function public.payout_month(p_month char(7) default to_char(now(),'YYYY-MM'))
returns int
language plpgsql
security definer
as $$
declare v_rows int;
begin
with month_rows as (
select creator_id,
sum((amount_cents*0.70)::int - fee_cents)::int as net_cents
from public.tips
where to_char(created_at at time zone 'UTC','YYYY-MM') = p_month
group by 1
having sum((amount_cents*0.70)::int - fee_cents) >= 5000 -- $50 threshold
)
insert into public.creator_payouts (creator_id, earnings_month, payout_cents, status, note)
select creator_id, p_month, net_cents, 'queued', 'auto-enqueued'
from month_rows
on conflict (creator_id, earnings_month) do nothing;

get diagnostics v_rows = row_count;
insert into public.audit_payout_events (event_type, earnings_month, payload)
values ('payout.queued.batch', p_month, jsonb_build_object('count', coalesce(v_rows,0)));
return coalesce(v_rows,0);
end $$;
```

---

# ⏰ Scheduling (MV refresh + payouts)

**Default (scalable, DB-centric)**

* **pg\_cron** (Supabase)

* Hourly MV refresh:

```sql
select cron.schedule(
'earnings_mv_hourly','5 * * * *',
$$ refresh materialized view concurrently public.creator_earnings_mv; $$
);
```
* Monthly enqueue (1st @ 02:10 UTC):

```sql
select cron.schedule(
'payout_month_enqueue','10 2 1 * *',
$$ select public.payout_month(to_char(now() - interval '1 day','YYYY-MM')); $$
);
```
* **Supabase Edge Function** `run-payouts` (scheduled \~02:15 UTC):

* Reads `creator_payouts where status='queued'`, creates Stripe **Transfers**, updates to `sent|failed`, writes `audit_payout_events`.
* **Fallback** if no Edge schedule: Vercel Cron → calls `/api/admin/payout-month`.

---

# 🔒 Security

* **RLS** enabled on `tips` and `creator_payouts` (creator reads own).
* Service role (webhook / scheduled jobs) performs writes.
* Webhook route uses **raw body** for Stripe signature verify.
* **Idempotency**: unique on `stripe_payment->>payment_intent`; payouts unique on `(creator_id, earnings_month)`.
* Rate-limit `create-checkout-session` (per IP/user).

---

# 💳 Stripe specifics

* **Checkout**: Connect destination charge.

* `transfer_data.destination = creators.stripe_account`
* `application_fee_amount = round(amount_cents * 0.30)`
* **Fees**: derive `fee_cents` from `charge.balance_transaction`.
* **Events**: use `payment_intent.succeeded` / `checkout.session.completed` to get `payment_intent`; pass to `record_tip()`.

---

# 🧰 Tooling & Verification (non-optional)

* **Postman CLI** (extend existing FE collection with Admin folder):

* `POST /api/admin/refresh-earnings-mv` (requires `SERVICE_ADMIN_TOKEN`)
* `POST /api/admin/payout-month?yyyymm=YYYY-MM`
* Negative cases: 400 invalid month; 401/403 without token
* CI job runs collection on preview; exports JUnit (`reports/postman-be.xml`)
* **Stripe CLI** (local loop):

* `stripe listen --forward-to localhost:3000/api/stripe/webhook`
* `stripe events trigger checkout.session.completed --override 'data.object.metadata.creatorId=...'`
* **(Optional) Stripe MCP** for engineers (restricted keys):

* Provide workspace MCP config for VS Code/Cursor to call safe tools (`list_payment_intents`, docs search).

---

# 🚢 Deliverables

* API routes:

* `/api/tip/create-checkout-session` (Connect + idempotency + errors)
* `/api/stripe/webhook` → `record_tip()` + audit (raw body verify)
* `/api/creator/{earnings|tips|payouts}` (tips with cursor)
* `/api/admin/{refresh-earnings-mv|payout-month}` (token-guarded)
* SQL migrations: **001–006** above
* **pg\_cron** schedules + **Edge Function** `run-payouts` (or Vercel Cron fallback)
* Postman collection updated (Admin + negatives) and CI job
* Tests:

* Webhook idempotency (same `pi` twice → one row)
* Tips cursor stability `(created_at,id)`
* Payout idempotency per `(creator, month)`
* MV values = raw aggregates
* Observability: structured logs (event id, payment\_intent, creator\_id, amount\_cents, transfer\_id, status)

---

# ✅ Acceptance Criteria

* Stripe test event → **exactly one** row in `tips`; `audit_tip_events` shows `webhook.received` + `tip.recorded`.
* `GET /api/creator/earnings` fields match MV; sums reconcile with ledger.
* `GET /api/creator/tips` returns ≤50 with stable cursor and `tipper` object.
* Monthly cron enqueues payouts; scheduled job creates Transfers; `creator_payouts` `status` set to `sent|failed`; `audit_payout_events` appended.
* RLS verified: cross-creator reads blocked.
* Postman (preview) **passes** public + admin routes; negatives behave as specified.

---

# 🛑 No-Gos

* CSV payout imports
* Dynamic revenue splits (fixed **70/30**)
* Multi-currency (USD only)
* Auto dispute reconciliation (log only)

---

# ⚠️ Risks & Mitigations

* **Disputes/Refunds** → log in audits; exclude from payouts until settled (future).
* **Cron failure** → `payout_month()` idempotent; rerunnable admin endpoint.
* **Webhook retries** → unique by `payment_intent`.
* **Month cutoff** → compute by `created_at at time zone 'UTC'`.

---

# 🧩 Companion Tickets

1. **DevOps / Platform** (parallel): secrets, webhook config, pg\_cron, Edge Function schedule, logs/alerts, Postman CI.
2. **Analytics** (optional): events `tip_checkout_created`, `tip_succeeded`, `payout_sent` (creatorId, amountCents, contentId, pi/transfer).
3. **Docs/Runbook**: `/docs/runbooks/tip-jar.md` with Stripe CLI steps and outage SOP.

---

> **Note:** If FE currently calls `/api/stripe/create-checkout-session`, add a thin proxy at `/api/tip/create-checkout-session` to keep FE contract stable during rollout.