Database reference

PostgreSQL is the operational system of record. The base schema is initialized from database/schema.sql; the API also performs additive identity migrations at startup.

Identity and profile

Table	Purpose
`users`	Root user identity, type, display name, locale, jurisdiction, soft deletion
`creator_profiles`	Bio, languages, regions, narratives, availability, visibility
`social_accounts`	X/Telegram pseudonymous subject, handle, normalized profile, encrypted token, consent version
`wallet_links`	Chain/address ownership evidence, signature hash, verification/revocation
`auth_sessions`	Opaque browser sessions with expiry/revocation
`auth_challenges`	Single-use wallet/X challenges, nonce hash, encrypted PKCE secret, payload, expiry
`score_snapshots`	Versioned score, tier, components, explanations, confidence, timestamp

Provider subjects are unique across a provider. A user has at most one row for each social provider. A wallet address is unique per chain. Revocation preserves wallet history while removing it from active profile output.

Marketplace

Table	Purpose
`projects`	Project identity and ownership-verification facts
`campaigns`	Terms, state, economics, verification/retention/refund policy, escrow pointer
`mission_claims`	One creator reservation per campaign
`deliverables`	Canonical URL and content hash per claim
`verifications`	Outcome, rule results, fraud signals, attestation hash, quorum
`settlements`	Gross/immediate/retained/fee amounts and transaction receipts
`service_listings`	Direct creator service, price, lead time, jurisdictions, activation
`reviews`	Campaign-bound creator or project rating

Token amounts use numeric(78,0) to preserve uint256-scale integers without floating point. Addresses and transaction hashes are binary values. Application code must format units only at the presentation boundary.

Measurement and external state

Table	Purpose
`attribution_events`	Campaign/creator event with pseudonymous subject, source, time, metadata
`pons_events`	Idempotent chain event keyed by chain, transaction hash, and log index
`policy_versions`	Versioned rules, sources, effective and expiry timestamps

The current SQL attribution table is more generic than the typed domain envelope. Production ingestion must persist consent, dedupe key, expiry, and typed properties explicitly or prove their durable representation.

Lifecycle consistency

The SQL campaign_state enum, domain campaign state machine, and onchain claim states use different names and levels of abstraction. Do not write direct string translations. Introduce an explicit mapping and tests before persistent campaign orchestration.

Migration and recovery

Startup DDL is acceptable for the current foundation but should be replaced by ordered, immutable migrations before production data evolves. Production operations require encrypted backups, point-in-time recovery where available, restore drills, schema-version recording, and a deletion/retention worker.

Never restore a database over current production state without validating target, timestamp, expected data loss, application compatibility, and a recovery copy.