Team Dashboard — Database Schema

This document describes the Supabase/PostgreSQL schema that powers the Team Dashboard at routes like:

/{primaryTeam}/dashboard (e.g. http://localhost:3000/super-admin/dashboard)

High-level data flow

flowchart TB
    subgraph sources["Source tables"]
        SIG["signatures"]
        RATES["rates"]
        TXN["transactions"]
        CIRC["circulators"]
        CAMP["campaigns"]
        TEAMS["teams"]
    end

    subgraph pipeline["DB pipeline"]
        TRIG["Stale-marking triggers"]
        RECOMP["recompute_circulator_earnings()"]
        CRON["pg_cron: recompute_stale_earnings()"]
        RPC["refresh_my_earnings() RPC"]
    end

    ROLLUP["circulator_earnings<br/>(rollup table)"]

    subgraph app["Next.js app"]
        SVC["getCirculatorEarnings()"]
        UI["CirculatorDashboard"]
    end

    SIG --> TRIG
    RATES --> TRIG
    TXN --> TRIG
    TRIG --> ROLLUP
    RECOMP --> ROLLUP
    CRON --> RECOMP
    RPC --> RECOMP

    SIG --> RECOMP
    RATES --> RECOMP
    TXN --> RECOMP
    CIRC --> RECOMP

    ROLLUP --> SVC
    CAMP --> SVC
    SVC --> UI

Batch identity: Every earnings row is keyed by (circulator, campaign, team, turn_in_date).

Entity relationship diagram

erDiagram
    teams ||--o{ circulator_earnings : "team"
    circulators ||--o{ circulator_earnings : "circulator"
    campaigns ||--o{ circulator_earnings : "campaign"
    circulators ||--o{ signatures : "circulator"
    teams ||--o{ signatures : "team"
    campaigns ||--o{ signatures : "campaign"
    teams ||--o{ rates : "team"
    campaigns ||--o{ rates : "campaign"
    users ||--o{ transactions : "circulator"
    teams ||--o{ transactions : "team"
    campaigns ||--o{ transactions : "campaign"
    circulators ||--o| users : "user_id"
    teams ||--o{ campaign_team_circulators : "team"
    campaigns ||--o{ campaign_team_circulators : "campaign"
    circulators ||--o{ campaign_team_circulators : "circulator"

    circulator_earnings {
        uuid circulator PK
        text campaign PK
        text team PK
        date turn_in_date PK
        int total_signatures
        int valid_signatures
        float validity_rate
        float rate_applied
        bool incentive_met
        float amount_earned
        float amount_paid
        float kitty
        float balance
        timestamptz last_calculated_at
        bool is_stale
    }

    signatures {
        uuid id PK
        uuid circulator FK
        text campaign FK
        text team FK
        date turnInDate
        text status
    }

    rates {
        uuid id PK
        text team FK
        text campaign FK
        float rate
        float incentive_threshold
        date startDate
        date endDate
        bool current
    }

    transactions {
        uuid id PK
        uuid circulator FK
        text campaign FK
        text team FK
        date turnInDate
        float totalAmount
        float amountPaid
        float kitty
    }

    circulators {
        uuid id PK
        uuid user_id FK
        text firstName
        text lastName
    }

    campaigns {
        text id PK
        text campaignName
        text status
    }

    teams {
        text id PK
        text name
    }

Primary read table: `circulator_earnings`

Rollup table — one row per circulator turn-in batch. Populated by DB functions, not written directly by the app.

Migration: supabase/migrations/20260606083135_circulator_calculations.sql
Reference snippet: supabase/snippets/circulator_earnings.sql

Column	Type	Description
`circulator`	`uuid`	FK → `circulators.id`
`campaign`	`text`	FK → `campaigns.id`
`team`	`text`	FK → `teams.id`
`turn_in_date`	`date`	Matches `signatures."turnInDate"`
`total_signatures`	`integer`	Submitted count (`status != 'invalid'`)
`valid_signatures`	`integer`	Valid count (`status = 'valid'`)
`validity_rate`	`double precision`	`valid / submitted × 100`
`rate_applied`	`double precision`	`$` per signature from `rates`
`incentive_met`	`boolean`	`validity_rate >= incentive_threshold`
`amount_earned`	`double precision`	See Earnings formula
`amount_paid`	`double precision`	Sum of `transactions."amountPaid"`
`kitty`	`double precision`	Sum of `transactions.kitty`
`balance`	`double precision`	`amount_earned - amount_paid - kitty`
`last_calculated_at`	`timestamptz`	When the batch was last recomputed
`is_stale`	`boolean`	`true` = pending recompute

Primary key / unique index: (circulator, campaign, team, turn_in_date)

Indexes:

idx_circulator_earnings_circulator — filter by circulator (dashboard query)
idx_circulator_earnings_campaign_team — filter by campaign + team
idx_circulator_earnings_stale — partial index where is_stale = true

RLS: SELECT allowed for authenticated users (permissive policy). Writes happen via SECURITY DEFINER functions and triggers.

Source tables

These tables feed the rollup computation. Changes to them mark matching circulator_earnings rows as stale.

`signatures`

Signature records grouped into earnings batches.

Column	Type	Role in dashboard
`circulator`	`uuid`	Batch key → `circulator_earnings.circulator`
`campaign`	`text`	Batch key → `circulator_earnings.campaign`
`team`	`text`	Batch key → `circulator_earnings.team`
`"turnInDate"`	`date`	Batch key → `circulator_earnings.turn_in_date`
`status`	`text`	`'valid'` vs non-`'invalid'` drives counts

Counting logic (count_earnings_batch_signatures):

total_signatures = COUNT(*) FILTER (WHERE status IS DISTINCT FROM 'invalid')
valid_signatures = COUNT(*) FILTER (WHERE status = 'valid')

Index: idx_signatures_circulator_campaign_team_turn_in_date on (circulator, campaign, team, "turnInDate")

`rates`

Pay rate and validity incentive threshold per team + campaign.

Column	Type	Role in dashboard
`team`	`text`	Lookup key
`campaign`	`text`	Lookup key
`rate`	`double precision`	`$` per signature → `rate_applied`
`incentive_threshold`	`double precision`	Minimum validity % to earn on all submitted sigs
`"startDate"` / `"endDate"`	`date`	Date-range rate selection
`current`	`boolean`	Fallback when no date-range match

Lookup order (lookup_earnings_batch_rate):

Rate whose date range contains turn_in_date (most recent startDate wins)
Else the row where current = true for that team + campaign
Defaults to 0 if none found

`transactions`

Payment records. Important: transactions.circulator references users.id, not circulators.id. The pipeline resolves circulators.user_id → transactions.circulator.

Column	Type	Role in dashboard
`circulator`	`uuid`	FK → `users.id` (via circulator profile)
`campaign`	`text`	Batch key
`team`	`text`	Batch key
`"turnInDate"`	`date`	Batch key
`"amountPaid"`	`double precision`	Generated column → summed into `amount_paid`
`kitty`	`double precision`	Generated column → summed into `kitty`

`circulators`

Canonical circulator person records.

Column	Type	Role in dashboard
`id`	`uuid`	Primary key used in `circulator_earnings` and `signatures`
`user_id`	`uuid`	Optional FK → `users.id`; links to `transactions`
`firstName`, `lastName`	`text`	Display name in dashboard header

`campaigns`

Column	Type	Role in dashboard
`id`	`text`	Campaign identifier (text, not UUID)
`"campaignName"`	`text`	Display label in table rows
`status`	`text`	Active/inactive filter tabs (`'active'` vs other)

`teams`

Column	Type	Role in dashboard
`id`	`text`	Team slug (e.g. `super-admin`) — route param `primaryTeam`
`name`	`text`	Displayed in dashboard header

`campaign_team_circulators` (admin circulator picker)

When the admin dashboard is wired to live data, the circulator dropdown will likely query this junction table:

Column	Type	Description
`campaign`	`text`	FK → `campaigns.id`
`team`	`text`	FK → `teams.id`
`circulator`	`uuid`	FK → `circulators.id`
`type`	`circulator_type`	`'volunteer'` \| `'paid'`

PK: (campaign, team, circulator)

Earnings formula

From calc_earnings_amount():

validity_rate = (valid_signatures / total_signatures) × 100   -- 0 if total = 0
incentive_met = validity_rate >= incentive_threshold

amount_earned = IF incentive_met
                  THEN total_signatures × rate_applied
                  ELSE valid_signatures × rate_applied

balance = amount_earned - amount_paid - kitty

Bad signatures (shown in UI) are computed client-side:

bad_signatures = total_signatures - valid_signatures

Computation pipeline

Stale marking (lightweight triggers)

Trigger	Table	Effect
`trigger_mark_earnings_stale_on_signature_change`	`signatures`	Marks affected batch stale on INSERT/UPDATE/DELETE
`trigger_mark_earnings_stale_on_rate_change`	`rates`	Marks all batches for that team + campaign stale
`trigger_mark_earnings_stale_on_transaction_change`	`transactions`	Resolves user → circulator, marks batch stale

Recompute functions

Function	Purpose
`recompute_circulator_earnings(circulator, campaign, team, turn_in_date)`	Full recalc for one batch
`recompute_stale_earnings(limit)`	Worker: discovers new batches from `signatures`, processes stale rows
`refresh_my_earnings()`	On-demand RPC for logged-in circulator (dashboard Refresh button)

Scheduled job

-- Daily at midnight UTC, processes up to 500 stale batches
SELECT cron.schedule(
  'recompute-stale-earnings',
  '0 0 * * *',
  $$SELECT public.recompute_stale_earnings(500)$$
);

High-level data flow​

Entity relationship diagram​

Primary read table: circulator_earnings​

Source tables​

signatures​

rates​

transactions​

circulators​

campaigns​

teams​

campaign_team_circulators (admin circulator picker)​

Earnings formula​

Computation pipeline​

Stale marking (lightweight triggers)​

Recompute functions​

Scheduled job​