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

Tables overview

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.

challenge_invites

Queued invites for challenges. Created by POST /api/invites and listed by GET /api/invites. A background job (not yet implemented) is expected to process queued rows and transition them to sent / accepted / failed.

Indexes: (challenge_id), (status)

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.

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.