Datenbank-Schema¶
Datenbank: PostgreSQL 16 (containerisiert) Name: chainbets Zugriff: via
DATABASE_URLEnvironment-Variable
Übersicht¶
┌─────────────────────────────────────────────────┐
│ Settlement-Tabellen │
│ ├── settlement_pipeline_runs │
│ ├── settlement_runs │
│ ├── settlement_artifacts │
│ ├── settlement_proofs │
│ ├── settlement_winner_payouts │
│ ├── settlement_affiliate_payouts │
│ └── daily_prize_breakdown │
├─────────────────────────────────────────────────┤
│ Ticket-Tabellen │
│ ├── buy_intents │
│ ├── tickets │
│ └── ticket_tips │
├─────────────────────────────────────────────────┤
│ User & Bonus │
│ ├── users │
│ ├── affiliate_rates │
│ ├── extra_bonus_vouchers │
│ └── push_subscriptions │
├─────────────────────────────────────────────────┤
│ Monitoring & Config │
│ ├── chain_stats │
│ ├── global_stats_cache │
│ ├── bo_config │
│ ├── seed_updates │
│ ├── claim_events │
│ ├── re_engagement_emails │
│ ├── watchdog_runs │
│ └── watchdog_alerts │
└─────────────────────────────────────────────────┘
Settlement-Tabellen¶
settlement_pipeline_runs¶
Trackt die tägliche Settlement-Pipeline als Ganzes.
settlement_pipeline_runs (
day_id INT PRIMARY KEY,
started_at TIMESTAMP,
finished_at TIMESTAMP,
status VARCHAR, -- RUNNING / DONE / FAILED
export_ok BOOLEAN,
affiliate_ok BOOLEAN,
compute_ok BOOLEAN,
approve_ok BOOLEAN,
commit_ok BOOLEAN,
stats_ok BOOLEAN
)
settlement_runs¶
Einzelne Settlement-Berechnungen (ein Tag kann mehrere dry_runs haben).
settlement_runs (
id SERIAL PRIMARY KEY,
day_id INT,
schema_version INT,
merkle_root VARCHAR, -- Winners-Merkle-Root
affiliate_root VARCHAR, -- Affiliate-Merkle-Root
jackpot_hit_arr BOOLEAN[], -- [5] Jackpot-Flags
pool_payout_arr NUMERIC[], -- [5] Regular-Pot-Auszahlungen
pool_jackpot_payout_arr NUMERIC[], -- [5] Jackpot-Reserve-Auszahlungen
total_payout NUMERIC,
affiliate_total NUMERIC,
charity_total NUMERIC,
status VARCHAR, -- dry_run / approved / committed
run_hash VARCHAR, -- SHA256 für Deduplizierung
commit_tx_hash VARCHAR,
commit_block INT,
committed_at TIMESTAMP,
created_at TIMESTAMP DEFAULT NOW(),
UNIQUE(day_id, run_hash)
)
Status-Lifecycle: dry_run → approved → committed
settlement_artifacts¶
JSON-Artefakte als Source-of-Truth.
settlement_artifacts (
id SERIAL PRIMARY KEY,
day_id INT,
artifact_type VARCHAR, -- 'inputs' / 'affiliates'
artifact_json JSONB,
checksum VARCHAR, -- SHA256
created_at TIMESTAMP DEFAULT NOW(),
UNIQUE(day_id, artifact_type)
)
settlement_proofs¶
Merkle-Proofs für Claim-Verifikation.
settlement_proofs (
id SERIAL PRIMARY KEY,
settlement_run_id INT REFERENCES settlement_runs(id),
proof_type VARCHAR, -- 'winners_merkle' / 'affiliate_merkle'
proof_json JSONB, -- Root, Leaves, Proofs
checksum VARCHAR
)
settlement_winner_payouts¶
Individuelle Gewinner-Auszahlungen.
settlement_winner_payouts (
id SERIAL PRIMARY KEY,
settlement_run_id INT REFERENCES settlement_runs(id),
player VARCHAR NOT NULL, -- Wallet-Adresse
amount NUMERIC NOT NULL, -- Auszahlungsbetrag (6 Decimals)
claimed BOOLEAN DEFAULT false,
claimed_pending BOOLEAN DEFAULT false,
pending_tx_hash VARCHAR,
claim_tx_hash VARCHAR,
claimed_at TIMESTAMP
)
settlement_affiliate_payouts¶
Affiliate-Provisionsauszahlungen (gleiche Struktur wie Winner).
settlement_affiliate_payouts (
id SERIAL PRIMARY KEY,
settlement_run_id INT REFERENCES settlement_runs(id),
affiliate VARCHAR NOT NULL,
amount NUMERIC NOT NULL,
claimed BOOLEAN DEFAULT false,
claimed_pending BOOLEAN DEFAULT false,
pending_tx_hash VARCHAR,
claim_tx_hash VARCHAR,
claimed_at TIMESTAMP
)
daily_prize_breakdown¶
Dashboard-Daten für die Gewinnübersicht.
daily_prize_breakdown (
day_id INT,
pool_id INT,
class_id INT, -- 2/3/4/5 (3er/4er/5er/6er)
class_name VARCHAR,
winner_count INT,
share_total NUMERIC,
amount_per_winner NUMERIC,
redistributed BOOLEAN, -- Jackpot-Redistribution?
PRIMARY KEY(day_id, pool_id, class_id)
)
Ticket-Tabellen¶
buy_intents¶
Kaufabsichten (Lifecycle: CREATED → SIGNED → EXECUTING → CONFIRMED/FAILED).
buy_intents (
intent_id VARCHAR PRIMARY KEY,
buyer VARCHAR, -- Wallet-Adresse
pool_id INT,
day_id INT,
tips JSONB, -- Gewählte Zahlen
tip_count INT,
bonus_tip_count INT,
price_per_tip NUMERIC,
affiliate VARCHAR,
affiliate_rate_bp INT,
tips_hash VARCHAR,
status VARCHAR, -- PENDING/SIGNED/EXECUTING/CONFIRMED/FAILED
tx_hash VARCHAR,
ticket_id BIGINT,
rng_numbers INT[], -- [6] Gewinnzahlen
block_number INT,
created_at TIMESTAMP,
confirmed_at TIMESTAMP,
failed_at TIMESTAMP,
error_code VARCHAR,
error_message TEXT
)
tickets¶
Bestätigte Tickets (immutable nach Erstellung).
tickets (
ticket_id BIGINT PRIMARY KEY,
day_id INT,
pool_id INT,
player VARCHAR,
affiliate VARCHAR,
affiliate_rate_bp INT,
affiliate_base NUMERIC,
tip_count INT,
bonus_tip_count INT,
tips_hash VARCHAR,
stake_per_tip NUMERIC,
total_stake NUMERIC,
fee_amount NUMERIC, -- 27.5%
pot_amount NUMERIC, -- 60% vom 67.5%
rollover_amount NUMERIC, -- 5%
jackpot_amount NUMERIC, -- 40% vom 67.5%
rng_numbers INT[], -- [6] Gewinnzahlen
tx_hash VARCHAR,
block_number INT,
intent_id VARCHAR,
source VARCHAR,
created_at TIMESTAMP
)
ticket_tips¶
Einzelne Tipps pro Ticket mit Trefferauswertung.
ticket_tips (
ticket_id BIGINT,
tip_index INT,
numbers INT[], -- [6] Getippte Zahlen
is_bonus BOOLEAN,
matches INT, -- Anzahl Treffer (0-6)
class_id INT, -- 2=3er, 3=4er, 4=5er, 5=6er
PRIMARY KEY(ticket_id, tip_index)
)
Monitoring-Tabellen¶
chain_stats¶
Gecachter On-Chain-State (aktualisiert alle 15 Min).
Keys: jackpot_pool_0..4, jackpot_reserve_pool_0..4, no_hit_streak_pool_0..4, charity_balance
seed_updates¶
Audit-Log für VRF/Drand-Seed-Rotationen.
seed_updates (
id SERIAL PRIMARY KEY,
seed_type VARCHAR, -- 'vrf' / 'drand'
seed_value VARCHAR,
trigger_info TEXT, -- z.B. "counter=45, threshold=42"
tx_hash VARCHAR,
created_at TIMESTAMP DEFAULT NOW()
)
claim_events¶
Audit-Log für alle Claim-Aktionen.
claim_events (
id SERIAL PRIMARY KEY,
event_type VARCHAR, -- CLAIM_PENDING_SET, CLAIM_TX_REVERTED, etc.
scope VARCHAR, -- 'winner' / 'affiliate' / 'system'
tx_hash VARCHAR,
details JSONB,
created_at TIMESTAMP DEFAULT NOW()
)
bo_config¶
Key-Value-Konfiguration für die BO-Engine.
Wichtige Keys: charity_rate_bp, jackpot_miss_trigger_days, reengagement_*, resend_api_key
Verbindungs-Pattern¶
// Alle Worker/Services nutzen:
import { Client as PgClient } from "pg";
const pg = new PgClient({ connectionString: process.env.DATABASE_URL });
await pg.connect();
try {
// Operationen
} finally {
await pg.end();
}
Kein Connection-Pool - jeder Prozess öffnet eigene Verbindungen. Bei Cron-basierten Workern ist dies ausreichend, da sie kurzlebig sind.
Transaktionen¶
Kritische Operationen nutzen explizite Transaktionen:
await pg.query("BEGIN");
try {
await pg.query("INSERT INTO tickets ...");
await pg.query("INSERT INTO ticket_tips ...");
await pg.query("UPDATE buy_intents ...");
await pg.query("COMMIT");
} catch (e) {
await pg.query("ROLLBACK");
throw e;
}