Database Schema

PostgreSQL (Neon). All tables live in the public schema. Migration runner: npx tsx db/migrate.ts (tracks applied files in schema_migrations).

Database Architecture Overview

Challenge lifecycle data flow

User submits evidence (webapp API)
        │
        ▼
public.evidence          ← written by: POST /api/aivm/intake, evidenceCollector worker
        │
        ▼ evidenceEvaluator worker
public.verdicts          ← written by: evidenceEvaluator worker
        │
        ▼ challengeDispatcher (gates on approved + verdict)
public.aivm_jobs         ← written by: challengeDispatcher, challengeWorker, aivmIndexer
        │
        ▼ challengeWorker → requestInferenceV2 on-chain
        │   [Lightchain network: commit → reveal → attest]
        ▼ aivmIndexer → submitProofFor + ChallengePay.finalize()
public.challenges        ← written by: lightchain indexer (status updates)
        │
        ▼ participant claims reward on-chain
public.claims            ← written by: POST /api/me/claims (UI path), claimsIndexer (indexer path)

Write sources per table

Table relationships

challenges (1) ──── (N) participants
challenges (1) ──── (N) evidence
challenges (1) ──── (1) aivm_jobs
challenges (1) ──── (N) verdicts
challenges (1) ──── (N) claims
challenges (1) ──── (N) reminders
challenges (1) ──── (N) challenge_invites

participants (challenge_id + subject) ← joined by evidence, verdicts, claims

linked_accounts ── feeds ──► evidenceCollector ──► evidence
identity_bindings ── used by ──► OpenDota / Riot adapters
models ── referenced by ──► challenge_templates, challenge proof params

notifications ── keyed by wallet ── displayed in webapp NotificationBell + iOS Activity

organizations (1) ──── (N) org_members
organizations (1) ──── (N) competitions
organizations (1) ──── (N) teams
organizations (1) ──── (N) api_keys
organizations (1) ──── (N) webhooks
organizations (1) ──── (1) whitelabel_configs
organizations (1) ──── (N) seasons

competitions (1) ──── (N) competition_registrations
competitions (1) ──── (N) bracket_matches
competitions (1) ──── (N) match_disputes

seasons (1) ──── (N) season_competitions
seasons (1) ──── (N) season_standings

teams (1) ──── (N) team_roster

Tables overview

Core challenge tables

Notifications and user tables

Competition platform tables

API and integration tables

evidence

Stores normalized evidence records submitted for a (challenge_id, subject) pair. One row is inserted per ingestion event; multiple rows per challenge are allowed — the most recent row is used for evaluation.

Indexes: (challenge_id), (challenge_id, lower(subject))

Key behaviours:

• The evaluator worker reads the latest row per (challenge_id, lower(subject), provider).
• The evidence collector worker skips insertion when evidence_hash matches the previous row (no new data).
• challenge_id = 0 is used for preview/test submissions that are not linked to a live challenge.

verdicts

Stores the result of evaluating evidence for a (challenge_id, subject) pair. One verdict per pair — subsequent evaluations UPSERT the existing row.

Authoritative for: verdict/pass status. Written by offchain/workers/evidenceEvaluator.ts.

Unique constraint: (challenge_id, subject) — one verdict per participant per challenge.

Indexes: (challenge_id)

Used by: /api/challenges/[id]/progress, /api/challenges/[id]/claim, evidence evaluator worker.

identity_bindings

Maps a wallet address to a platform account (Steam, Riot, Epic Games). Used to verify gaming identity in the challenge flow.

Unique constraint: (wallet, platform) — one platform account per wallet.

Indexes: (wallet), (platform, platform_id)

openid_nonces

Short-lived nonces for OpenID Connect / OAuth state replay protection. Replaces the previous file-based openid_nonce.json store.

Indexes: (expires_at) — supports periodic cleanup of expired rows.

participants

Off-chain cache of challenge join records. Populated when a user calls POST /api/challenge/[id]/participant after a successful on-chain joinChallenge transaction, and also upserted automatically when evidence is submitted for a non-zero challenge_id.

The on-chain Joined events remain the authoritative record; this table is a fast queryable cache used by the “My Challenges” page and status APIs.

Participant row sources

A participant row may be created by two paths:

Policy: Both paths produce valid participant rows. Evidence-intake participants have joined_at = null. This is acceptable — it means the user submitted evidence but may not have staked on-chain via joinChallenge.

Reward integrity is NOT compromised by evidence-intake participants because:

• All reward/claim eligibility is determined by on-chain simulation (claimEligible), not by this table
• chain_outcome from challenges.chain_outcome is the authoritative final outcome
• If there is no on-chain stake, claimEligible = false and no payout is offered

Lifecycle impact: Evidence-intake participants appear in “My Challenges” so users can track their submission status. This is intentional — a user who uploaded garmin data without a formal join tx should still see their evidence status.

Unique index: (challenge_id, lower(subject)) — one row per participant per challenge.

Indexes: (lower(subject)), (challenge_id)

Used by: GET /api/me/challenges, GET /api/challenge/[id]/participant, evidence collector worker.

linked_accounts

Stores OAuth tokens and external IDs for provider accounts connected to a wallet. Used by the evidence collector worker (offchain/workers/evidenceCollector.ts) to pull live activity/match data from provider APIs on a polling schedule.

Unique index: (lower(subject), provider) — one linked account per provider per wallet.

Indexes: (lower(subject)), (provider)

API: GET/POST/DELETE /api/accounts/link

Security note: access_token and refresh_token are stored in plaintext. Use database-level encryption or a secrets vault in production.

challenge_templates

Admin-managed challenge templates. The runtime code-side templates in webapp/lib/templates.ts remain authoritative for the challenge-creation flow (they carry paramsBuilder and ruleBuilder functions which cannot be serialised).

This table allows admins to add, edit, or disable templates without a code deploy. It is the backend for GET /api/admin/templates and PUT /api/admin/templates.

Indexes: (kind), (active)

Merge behaviour: At runtime, templateRegistry.ts merges DB rows with code-side templates. The DB row’s name, hint, and fields_json override the code-side values; paramsBuilder and ruleBuilder always come from code.

Migration 031: Renamed kind steps → walking to unify step-counting and walking-distance challenges under one kind.

challenge_invites

Invites for challenges. Created by POST /api/invites and listed by GET /api/invites.

Wallet invites are processed inline: the API creates a notification for the target wallet and marks the invite as sent immediately. Email and Steam invites are queued for a background worker (status = 'queued').

Indexes: (challenge_id), (status)

Lifecycle:

queued ──► sent ──► accepted (invitee joined challenge)
                └─► failed   (delivery failed or expired)

For wallet invites, queued → sent happens inline in the POST handler. For email/steam, a background worker polls queued rows.

Accept flow: POST /api/invites/[id]/accept sets status = 'accepted', accepted_by_wallet, joined_at.

models

AIVM model registry. Migrated from webapp/public/models/models.json (migration 007). Read by offchain/db/models.ts and served via GET /api/admin/models.

The webapp/public/models/models.json file is archived — its content reads: "_archived": "Migrated to public.models DB table (migration 007_models.sql)".

Indexes: (kind), (active)

API: GET /api/admin/models, PUT /api/admin/models

Legacy compatibility: The seed data (migration 007) includes one legacy ZK model (strava.distance_in_window@1 with kind='zk'). This is retained for backward compatibility only. The plonk_verifier column is a legacy field with no active readers. New models should use kind='aivm' or kind='custom'.

challenges

Indexed on-chain challenge state. Written by the lightchain indexer as ChallengePay events are observed on-chain. Read by evaluators, APIs, and the evidence collector.

Indexes: (status), (created_at), (status, created_at), and several on proof jsonb fields.

Authoritative source: on-chain events via statusIndexer. Do not write directly.

Source-of-truth rules for finalized challenges:

1. chain_outcome = 2 (Fail) → no reward regardless of DB verdict_pass
2. chain_outcome = 1 (Success) + verdict_pass = true + claimEligible = true (on-chain simulation) → reward claimable
3. chain_outcome = null → outcome not yet indexed; fall back to verdict_pass + claimEligible

aivm_jobs

AIVM job queue. One row per challenge; the status machine drives the evaluation pipeline. Managed by offchain/dispatchers/challengeDispatcher.ts and offchain/workers/challengeWorker.ts.

Unique constraint: (challenge_id) — one job per challenge.

Indexes: (status, created_at) (for worker polling), (task_id) (for indexer lookup)

Job status flow:

queued → processing → submitted → committed → revealed → done
                    ↘ failed (on error, retried up to max_attempts)
                    ↘ dead   (exhausted max_attempts — terminal, no more retries)
                    ↘ canceled (challenge became Finalized/Rejected/Canceled before submission)
skipped             (challenge was already finalized when worker ran — no-op)

Terminal states: done, dead, canceled, skipped — worker will never pick these up again.

canceled policy: The challengeDispatcher runs cancelTerminalJobs() each poll cycle, setting any queued/failed/processing job to canceled when its challenge has reached a terminal on-chain status. The challengeWorker also guards against this at claim time via a JOIN filter. Run scripts/ops/cancelTerminalJobs.ts to fix any pre-existing stale rows.

Worker does not call markJobDone on success — the aivmIndexer transitions to done when it observes an InferenceFinalized event for the matching task_id.

indexer_state

Key/value checkpoint store for indexer workers. Each indexer stores its last-processed block number here so it can resume after restart without re-scanning from genesis.

Known keys:

• last_aivm_block — used by offchain/indexers/aivmIndexer.ts
• last_claims_block — used by offchain/indexers/claimsIndexer.ts

reminders

Email reminder subscriptions for challenge proof deadlines. Created when a user opts in on the challenge page.

Unique constraint: (email, challenge_id, type) — no duplicate reminders.

Indexes: (sent, created_at) for pending-reminder worker queries.

FK: challenge_id → public.challenges(id)

claims

Persists on-chain claim events for challenge participants. One row per (challenge_id, subject, claim_type) — upserted on conflict so both write paths are idempotent and the same claim is never recorded twice.

Write paths:

1. UI path (primary): After the user’s wallet submits a claimETH / claimPrincipal / etc. transaction successfully, the frontend POSTs to POST /api/me/claims with source='ui'.
2. Indexer path (secondary/hardening): offchain/indexers/claimsIndexer.ts watches ChallengePay *Claimed events and Treasury ClaimedETH events, upserting with source='indexer'. The indexer is the authoritative source of truth — if source='indexer', UI writes do not downgrade it.

Authoritative for: CLAIMED lifecycle state. resolveLifecycle() reads hasClaim (derived from this table) and treats it as the highest-priority signal for the CLAIMED stage.

Indexes:

API: GET /api/me/claims?subject=0x..., POST /api/me/claims

Claim types ↔ ChallengePay events:

achievement_mints

Indexes soulbound achievement token mints from ChallengeAchievement on-chain events. Two achievement types: completion (any finalized participant) and victory (winners only).

Indexes: (lower(recipient)), (challenge_id)

Used by: GET /api/me/achievements, reputation engine.

reputation

Computed reputation scores per wallet, derived from achievement mints. Updated off-chain after each achievement mint. Drives the level system shown in the user profile.

Level thresholds: 1=Newcomer, 2=Challenger, 3=Competitor, 4=Champion, 5=Legend (point-based).

Used by: GET /api/me/reputation, achievements page.

notifications

In-app notifications delivered to a wallet. Displayed in the webapp notification bell and the iOS Activity inbox. Created by invite handlers, alert workers, and the notification API.

Indexes: (lower(wallet), created_at DESC), (lower(wallet), read)

API: GET /api/v1/notifications?wallet=, POST /api/v1/notifications/mark-read

user_profiles

Display name, bio, and avatar for each wallet. One row per wallet.

API: GET/PUT /api/me/profile, GET /api/player/[wallet]

competitions

Competitions bundle multiple challenges or bracket matches under one umbrella. Owned by an organization.

API: GET/POST /api/competitions, GET /api/competitions/[id]

competition_registrations

Per-wallet or per-team registration for a competition.

organizations

Organizations own competitions, teams, API keys, and webhooks.

API: GET/POST /api/org, GET /api/org/[slug]

org_members

Organization membership. Roles: owner, admin, member.

seasons

Seasons group competitions for aggregated standings and leaderboards.

season_competitions

Junction table linking seasons to competitions with a weight multiplier.

PK: (season_id, competition_id)

season_standings

Aggregated standings per wallet per season. Updated by the competition scoring worker.

Unique: (season_id, wallet)

teams

Teams within organizations, used for team-based competitions.

team_roster

Team membership. Roles: captain, player.

bracket_matches

Individual match records within bracket/tournament competitions.

match_disputes

Dispute filings for bracket matches. Filed by participants, resolved by org admins.

api_keys

Scoped API keys for organizations. Keys are stored as hashed values; the raw key is shown once at creation time.

webhooks

Webhook endpoint registrations per organization. Events are delivered to the registered URL with an HMAC signature using the shared secret.

webhook_deliveries

Tracks individual webhook delivery attempts and retries.

whitelabel_configs

Per-organization white-label branding configuration. Applied when the webapp is accessed via the organization’s custom domain.

Authoritative sources summary

Running migrations

Use the migration runner (tracks applied files in schema_migrations):

npx tsx db/migrate.ts

This is idempotent — safe to re-run. Already-applied migrations are skipped.

Fallback with raw Node when tsx is unavailable:

node -e "
const { Pool } = require('pg');
const fs = require('fs');
const pool = new Pool({ connectionString: process.env.DATABASE_URL, ssl: { rejectUnauthorized: false } });
const files = fs.readdirSync('db/migrations').sort().map(f => 'db/migrations/' + f);
(async () => {
  for (const f of files) {
    await pool.query(fs.readFileSync(f, 'utf8'));
    console.log('OK:', f);
  }
  await pool.end();
})().catch(e => { console.error(e.message); process.exit(1); });
"

Note: The raw Node fallback does not update schema_migrations. Use npx tsx db/migrate.ts wherever possible.