# VOTD — Database Schema Reference

**File:** `docs/schema.sql`
**Branch:** `feature/supabase-backend`
**Apply to:** Both `votd-prototype` and `votd-live` Supabase projects

---

## Tables at a glance

| Table | Purpose |
|---|---|
| `jurisdictions` | Civic bodies — councils, boards, agencies |
| `votes` | The core vote items shown to users |
| `vote_sources` | Supporting URLs / documents per vote |
| `profiles` | Voter accounts (extends Supabase `auth.users`) |
| `user_votes` | A voter's yes/no response to a vote |
| `proposals` | User-submitted vote ideas awaiting review |
| `flags` | Anomaly signals and content reports |
| `admin_users` | Editorial/ops team (separate from voters) |
| `audit_log` | Immutable log of every admin action |

---

## Key design decisions

### Two Supabase projects, identical schema
The prototype and live environments are completely separate Supabase projects — not schemas, not schemas within the same project. This gives hard isolation: a mistake in the admin prototype view can never touch live data. The admin dashboard switches between them via different API keys in environment variables.

### Voters and admins are separate auth users
Voter accounts (`profiles`) and admin accounts (`admin_users`) both authenticate through Supabase Auth, but they live in different tables and have completely different RLS policies. An admin account cannot cast votes. A voter account cannot access queues.

### MFA enforced at the database level
The `admin_users` table has an `mfa_enrolled` boolean. RLS write policies on sensitive tables (`votes`, `proposals`, `flags`) check that `mfa_enrolled = true` for the requesting admin. This means even if someone gets an admin password, they cannot take any action until their TOTP device is enrolled — the database itself blocks them.

### Vote counts are denormalised
`votes.yes_count` and `votes.no_count` are maintained by a trigger on `user_votes` inserts/updates/deletes. This keeps feed queries fast — no join needed to show counts — at the cost of slightly more complex write logic. The trigger handles all edge cases including vote changes.

### Audit log is append-only
Two Postgres rules (`audit_log_no_delete`, `audit_log_no_update`) make the `audit_log` table immutable. Every approve, reject, publish, and ban action by an admin is recorded with a before/after JSON snapshot. This is especially important for the live environment.

### Jurisdiction hierarchy
Jurisdictions have a `parent_id` self-reference, allowing a tree: city council → county → state → federal. This will power the "votes near me" feature — once a user's address is verified, we know their exact position in the hierarchy and can surface relevant votes at every tier.

### Address data
Voter addresses in `profiles` are marked for encryption at rest using Supabase Vault. They are never returned via the public API — only used server-side for jurisdiction matching and voter roll verification.

---

## Status flows

**Vote:**
```
draft → published → votd (optional)
              ↓
           closed → archived
```

User-facing labels for vote status:

| Backend status | User-facing label | Meaning |
|---|---|---|
| `draft` | **Upcoming** | Proposal accepted, publish date assigned, not yet open |
| `published` | **Open** | Live in the feed, accepting votes |
| `closed` | **Closed** | 30-day window elapsed, results final |
| `archived` | — | Hidden from all user-facing views |

Note: `draft` maps to "Upcoming" only once a `publish_date` is set. A draft without a publish date is an internal editor state and has no user-facing label.

**Proposal:**
```
pending → needs_revision → pending (resubmitted)
       → accepted → (vote draft created)
       → rejected
```

User-facing labels for proposal status:

| Backend status | User-facing label | Meaning |
|---|---|---|
| `pending` | **Pending** | Submitted, awaiting editorial review |
| `needs_revision` | **Needs Revision** | Editor requested changes |
| `accepted` | — | Proposal approved; transitions to a vote in `draft` state |
| `rejected` | **Not Accepted** | Proposal did not meet criteria |

**Flag:**
```
open → reviewing → resolved
                 → dismissed
```

---

## RLS summary

| Table | Public | Voter (own) | Editor | Superuser |
|---|---|---|---|---|
| jurisdictions | read | — | read | all |
| votes | read (published+) | — | read all, write (MFA) | all |
| vote_sources | read (published+) | — | read all | all |
| profiles | — | all | — | all |
| user_votes | — | all | — | all |
| proposals | — | own | read all, update (MFA) | all |
| flags | — | — | all (MFA) | all |
| admin_users | — | — | read own | all |
| audit_log | — | — | read | read |

---

## Voter verification ladder

This is the canonical state machine for a voter account. The mobile app prototype uses this model today via `ProfileContext.verificationLevel`. The production `profiles` table will mirror this as a Postgres enum.

```
unverified → phone_verified → verifying → verified
                                        ↘ rejected
```

| State | What it means | Votes counted? | Ball in whose court? |
|---|---|---|---|
| `unverified` | Guest — no phone auth yet | No | User |
| `phone_verified` | OTP passed, name entered | No (saved locally) | User (needs address) |
| `verifying` | Name + address submitted, matching against voter roll | No (queued) | VOTD system |
| `verified` | Voter roll match confirmed | Yes | — |
| `rejected` | Match failed | No | User (can re-submit or appeal) |

**Key insight:** `verifying` is the normal resting state for most users — they have done everything they can and are waiting on the system. The UX should make this clear (amber "Verifying" badge + explanatory copy) so users don't feel stuck.

### What's editable per state

| Field | `unverified` | `phone_verified` | `verifying` | `verified` |
|---|---|---|---|---|
| Display name | — (sign up first) | ✅ free edit | ✅ free edit | ✅ free edit |
| Photo | — | ✅ free edit | ✅ free edit | ✅ free edit |
| Address | — | ✅ free edit | ⚠️ triggers re-verification (back to `verifying`) | ⚠️ triggers re-verification |
| Phone | — | ❌ auth identity | ❌ auth identity | ❌ auth identity |

Phone number is the auth identity and is never editable in-app.

---

## Feature access by verification level

Decisions finalized March 2026. Gates are enforced via `SkipOverlay` prompts in the mobile app.

| Action | `unverified` | `phone_verified` | `verifying` | `verified` |
|---|---|---|---|---|
| View votes + tally | ✅ | ✅ | ✅ | ✅ |
| Share vote item (neutral link) | ✅ | ✅ | ✅ | ✅ |
| Vote (counted in public results) | ❌ prompt | ❌ prompt | queued | ✅ |
| Save / follow a vote | ❌ prompt | ✅ | ✅ | ✅ |
| Add research / attach sources | ❌ prompt | ❌ prompt | ❌ prompt | ✅ only |
| Share that you voted | ❌ natural gate¹ | ✅ | ✅ | ✅ |
| Share how you voted (Yes/No) | ❌ natural gate¹ | ✅ | ✅ | ✅ |

¹ **Natural gate** — "Share that I voted" and "Share how I voted" are only reachable after casting a vote. Voting already requires phone verification, so no additional gate is needed for these share actions.

### Rationale for research gate (verified only)

Research submission is public civic attribution — the contributor's name is attached to a source visible to all users. This carries higher accountability than voting (which is private). Phone verification alone proves a real phone number but not a real identity. Voter roll match (`verified`) confirms name + address + voter registration — the identity confidence needed for public contributions. The `verifying` state is excluded even though the user has submitted all information, because the match has not yet been confirmed.

### Share action breakdown

| Share action | What is included | Gate | Component |
|---|---|---|---|
| Share vote item | URL / deep link to vote — no user data | None — open to all | iOS share sheet |
| Share that you voted | "I just voted on [vote title]" — no stance | Phone verified (natural gate via voting) | iOS share sheet |
| Share how you voted | "I voted Yes/No on [vote title]" | Phone verified (natural gate via voting) | iOS share sheet + `autoShare` param |

### Guest prompt sequence (vote action)

Two-tap design — respects the user's first skip decision before repeating the ask. Same `SkipOverlay` component both times, same buttons, only the message changes.

**First tap of Vote Now (unverified guest):**
> *"Verify your number to get started"*
> **"Verify"** → /signup · **"Skip for now"** → dismiss, pure guest, nothing recorded

**Second tap of Vote Now (same session, still guest):**
> *"Voting requires phone verification."*
> **"Verify"** → /signup · **"Skip for now"** → dismiss

Only the message line changes between taps. Button label, skip label, layout all identical. Second-tap state tracked via `useRef` boolean on the feed screen — not persisted, resets each session.

### Sequence B — Account recovery ("Already have an account?")

Entry: onboarding "Already have an account?" or any "Sign in" link. Routes to `/signin` (B1).

The flow is B1 → B2 → one of four exits, determined by the account state returned after OTP confirmation.

| Exit | Condition | Screen | Destination |
|---|---|---|---|
| **Straight to VOTD** | OTP matches an account that is `verified` or `verifying` | No interstitial — skip B3a entirely | `/(tabs)` as returning user |
| **B3a — We found you** | OTP matches a `phone_verified`-only account (started sign-up but never finished) | Centered screen: 👋, "We found you!", "Finish Sign Up" / "Skip for now" | "Finish Sign Up" → `/signup` (A3 name screen). "Skip for now" → `/(tabs)` as `phone_verified`. |
| **B3b — Try again** | Wrong OTP code entered | Inline error on OTP screen: "That code didn't match. Try again." Digits clear, cursor refocuses. | User retries on same screen. "Resend code" available. |
| **B3c — Can't get in** | Multiple failed OTP attempts or no matching account found | Centered screen: 🤔, "Hmm… that didn't work.", "Sign up" / "Get support" / "Skip for now" | "Sign up" → `/signup`. "Get support" → support flow (TBD). "Skip for now" → `/(tabs)` as guest. |

Rules:

- OTP is always sent regardless of whether the phone number maps to an existing account. This prevents account enumeration — an attacker cannot determine if a number is registered by observing whether a code is sent.
- B3b stays on the OTP screen (not a separate screen). Error state is inline. The "Didn't get the text?" link changes to "Resend code" after a failed attempt.
- B3c is reached after repeated failures. In the prototype, the mock routes to B3a (found) by default. Change `setScreen('found')` to `setScreen('failed')` in verify-signin.tsx to test B3c.
- "Skip for now" on B3a preserves the `phone_verified` level — the user can browse and vote but doesn't get full verification benefits until they complete sign-up.
- "Skip for now" on B3c drops the user into guest mode (`unverified`) since no account was recovered.

---

## `profiles` table — field reference (prototype → production mapping)

The mobile `ProfileContext` stores a subset of what the production `profiles` table will hold. This table tracks both fields:

| DB column | Context key | Type | Notes |
|---|---|---|---|
| `id` | — | `uuid` | Supabase Auth user ID |
| `phone` | — | `text` | E.164 format, auth identity |
| `first_name` | `firstName` | `text` | |
| `last_name` | `lastName` | `text` | |
| `avatar_url` | `avatarUri` | `text` | Object storage URL in production; local URI in prototype |
| `address_street` | `address.street` | `text` | USPS-normalized |
| `address_unit` | `address.unit` | `text` | Optional |
| `address_city` | `address.city` | `text` | |
| `address_state` | — | `text` | Locked to `CA` for pilot |
| `address_zip` | `address.zip` | `text` | 5-digit |
| `verification_level` | `verificationLevel` | `enum` | See ladder above |
| `jurisdiction_ids` | — | `uuid[]` | Populated after voter roll match; drives which votes are shown |
| `voter_roll_matched_at` | — | `timestamptz` | Set when state transitions to `verified` |
| `created_at` | — | `timestamptz` | |
| `updated_at` | — | `timestamptz` | |

Address columns are candidates for Supabase Vault encryption at rest (see Address data note above). They are never exposed via public API — only used server-side for jurisdiction resolution and voter roll matching.

### Prototype context location
`apps/mobile/lib/ProfileContext.tsx` — AsyncStorage-persisted, hydrated on app launch. Exports `VerificationLevel` and `ProfileAddress` types.

### Signup sequence (prototype)
```
onboarding → signup (phone entry) → verify-otp → profile-setup (name)
  → address-setup (address + sets verifying) → photo-setup → /(tabs)
```

---

*See `docs/admin.md` for admin role model, queue structure, and next steps.*