plm-lottery/docs/modello-dati.md

Modello dati

Questo documento descrive il database di PLM Lottery: tutte le tabelle, ogni colonna, i valori ammessi per i campi di stato e le transizioni tra di essi, gli indici e i vincoli, le invarianti che legano più tabelle, la configurazione del motore SQLAlchemy e la politica delle migrazioni Alembic.

La definizione di riferimento è app/db/models.py; lo schema effettivo di un'istanza è quello prodotto dalla catena di migrazioni in migrations/versions/. Le due cose coincidono (vedi Migrazioni), con le piccole differenze segnalate lì.

Cosa non c'è qui, e dove trovarlo:

• i flussi che scrivono queste righe, passo per passo: registrazione-e-depositi.md, scommesse-e-prelievi.md, ciclo-di-vita-del-round.md, transazioni-e-riconciliazione.md;
• il catalogo dei tipi di evento di audit_log: audit-log.md;
• gli endpoint che leggono e scrivono queste tabelle: api.md;
• chiavi, password e token: sicurezza.md;
• backup e interventi manuali sul database: operativita.md;
• la storia dei bug citati come B-nn: storico-bug.md.

Indice

• Diagramma delle relazioni
• Convenzioni comuni
• Tabelle: users · utxo_events · rounds · round_participants · round_config · pending_transactions · pending_transaction_txids · withdrawals · bug_reports · audit_log · alembic_version
• Indici e vincoli
• Invarianti tra tabelle
• Motore e sessioni
• Migrazioni
• Scelte specifiche di SQLite

Diagramma delle relazioni

Dieci tabelle applicative. round_config non ha relazioni: è una riga singola di parametri. Gli indirizzi della piattaforma che non appartengono a un utente sono derivati dalla master key, e i loro UTXO si leggono dalla rete, non da una tabella:

Indirizzo	Percorso	Dove è registrato
Utente	m/84'/746'/0'/0/<derivation_index>	users.address
Riserva del pool (il "pool" unico di oggi)	m/84'/746'/0'/1/0	nessuna tabella; app/wallet/hd.py:derive_pool_key
Montepremi di un round	m/84'/746'/0'/2/<round_id>	rounds.pool_address ([DAT-60])

Il diagramma descrive lo schema atteso; ciò che non esiste ancora è marcato da implementare nelle tabelle di dettaglio.

erDiagram
    users ||--o{ utxo_events : "possiede"
    users ||--o{ round_participants : "scommette"
    rounds ||--o{ round_participants : "raccoglie"
    users |o--o{ rounds : "vince (winner_user_id)"
    users ||--o{ withdrawals : "chiede"
    rounds |o--o{ pending_transactions : "bet / payout"
    withdrawals |o--o{ pending_transactions : "withdrawal"
    users |o--o{ pending_transactions : "bet / withdrawal"
    pending_transactions ||--|{ pending_transaction_txids : "ogni versione"
    users |o--o{ bug_reports : "segnala"
    users |o--o{ audit_log : "riguarda"
    rounds |o--o{ audit_log : "riguarda"

    users {
        int id PK
        string username UK "unico anche su lower(username)"
        string password_hash "Argon2"
        int derivation_index UK
        string address UK "P2WPKH plm1..."
        bigint cached_balance_sats "cache di utxo_events"
        int token_version
        datetime created_at
    }
    utxo_events {
        int id PK
        int user_id FK
        string txid "UK con vout"
        int vout
        bigint amount_sats
        int confirmed_height
        datetime confirmed_at
        string spent_txid "NULL, txid o external-spend"
    }
    rounds {
        int id PK
        string status "open closing drawing paying_out closed"
        datetime opened_at
        datetime closed_at
        int duration_seconds "snapshot"
        int cooldown_seconds "snapshot"
        bigint bet_amount_sats "snapshot"
        string fee_address "snapshot"
        bigint max_round_pool_sats "snapshot"
        int draw_confirmations "snapshot"
        string pool_address
        string server_seed_enc "cifrato fino alla rivelazione"
        string server_seed "in chiaro solo dopo la rivelazione"
        string server_seed_hash
        string participants_hash
        int draw_height
        datetime drawing_started_at
        int draw_block_height
        string draw_block_hash
        string seed_int
        int winner_user_id FK
        bigint pool_amount_sats
        bigint winner_amount_sats
        bigint fee_amount_sats
        bigint bump_reserve_sats
        string payout_txid
        datetime payout_blocked_at
    }
    round_participants {
        int id PK
        int round_id FK "UK con user_id"
        int user_id FK
        bigint bet_amount_sats
        string bet_txid
        int bet_vout
        datetime broadcast_at
        datetime confirmed_at
        int draw_position "UK con round_id"
        string status "building broadcast confirmed reorged_out"
    }
    round_config {
        int id PK
        string fee_address
        bigint bet_amount_sats
        int round_duration_seconds
        int round_cooldown_seconds
        int draw_animation_seconds
        int fee_rate_sat_vb
        int rbf_timeout_seconds
        int deposit_confirmations
        int draw_confirmations
        bigint max_round_pool_sats
        bool paused
        string paused_reason
        datetime updated_at
    }
    pending_transactions {
        int id PK
        string kind "bet payout withdrawal"
        int round_id FK
        int withdrawal_id FK
        int user_id FK
        string current_txid
        int fee_rate_sat_vb
        text raw_tx_hex
        datetime broadcast_at
        datetime last_broadcast_at
        string status "building pending confirmed failed"
        string replaced_by_txid
        int attempt_count
        string failure_reason
        datetime failed_at
        datetime updated_at
    }
    pending_transaction_txids {
        int id PK
        int pending_transaction_id FK
        string txid UK
        text raw_hex
        datetime created_at
    }
    withdrawals {
        int id PK
        int user_id FK
        string external_address
        bigint amount_requested_sats
        bigint amount_sent_sats
        string txid
        string status "building broadcast confirmed failed"
        datetime created_at
        datetime confirmed_at
    }
    bug_reports {
        int id PK
        text description
        string contact
        int user_id FK
        string status "open read resolved"
        datetime created_at
    }
    audit_log {
        int id PK
        string event_type
        text payload_json
        int user_id FK
        int round_id FK
        datetime created_at
    }

Convenzioni comuni

• [DAT-01] Ogni importo è un numero intero di satoshi (1 PLM = 100 000 000 sat), in una colonna BigInteger il cui nome finisce in _sats. Nessun importo passa mai per un float: anche il valore degli input letti dalla rete per un RBF è preso come intero dalla transazione grezza (B-40). Codice: app/db/models.py · app/tx/broadcast.py:_prevout_amount · Test: tests/unit/test_broadcast.py

• [DAT-02] Ogni timestamp è in UTC. Si scrive con app/db/models.py:utcnow (default di colonna) o con datetime.now(timezone.utc); le colonne sono DateTime senza fuso, e SQLite restituisce valori naive. Chi legge un timestamp per confrontarlo con l'ora corrente lo marca UTC con .replace(tzinfo=timezone.utc), e ogni risposta API lo serializza con isoformat_utc, che aggiunge l'offset (B-35). Codice: app/db/models.py:utcnow · app/api/timeutil.py:isoformat_utc · app/rounds/service.py:round_deadline · Test: tests/unit/test_api_timeutil.py, tests/unit/test_scheduler.py

• [DAT-03] I campi di stato (status, kind) sono stringhe libere (String(16)), senza CHECK né enum nel database. I valori ammessi sono solo quelli elencati in questo documento, e a garantirlo è esclusivamente il codice che li scrive; l'unico vincolo del database che dipende da un valore di stato è l'indice ix_rounds_single_active ([DAT-17]). Codice: app/db/models.py

• [DAT-04] Le chiavi esterne sono dichiarate ma, su SQLite, non applicate: PRAGMA foreign_keys non viene attivato ([DAT-52]). Nessuna chiave esterna ha ON DELETE. Le sole cancellazioni fisiche nel codice sono quelle di una scommessa fallita (la riga round_participants e la sua pending_transactions, con le sue versioni in pending_transaction_txids, [DAT-24], [DAT-38], [DAT-70]); nessun'altra tabella perde righe, quindi i riferimenti restano validi per costruzione, non per vincolo. Codice: app/db/base.py:_register_sqlite_pragmas · app/bets/service.py:_release_failed_bet · app/tx/reconcile.py:_abandon

• [DAT-05] Ogni chiave primaria è un intero autoincrementale id, assegnato dal database al flush/commit. Nessuna tabella usa chiavi naturali come chiave primaria; le chiavi naturali (txid+vout, round_id+user_id, username) sono vincoli di unicità separati. Codice: app/db/models.py

Tabelle

users

Un account. Nasce alla registrazione e non viene mai cancellato.

Colonna	Tipo	Null	Default	Significato	Scritta da
id	Integer PK	no	autoincrement	Identificativo; è il sub del JWT	database
username	String(64)	no	—	Nome scelto dall'utente, conservato così come digitato	app/auth/routes.py:register
password_hash	String(256)	no	—	Hash Argon2 della password	register; app/api/routes/users.py:change_password; app/api/routes/admin.py:reset_user_password
derivation_index	Integer, unico	no	—	Indice BIP84 dell'indirizzo: m/84'/746'/0'/0/<index>	register
address	String(128), unico	no	—	Indirizzo P2WPKH (plm1…) derivato da derivation_index; deposito, vincite e resto	register
cached_balance_sats	BigInteger	no	0	Saldo confermato e spendibile, cache di utxo_events	solo app/wallet/balance.py:recompute_balance
token_version	Integer	no	0 (ORM e server_default nel modello)	Versione dei token di sessione; il JWT la porta nel claim tv	change_password, reset_user_password
created_at	DateTime	no	utcnow	Istante della registrazione	database/ORM

• [DAT-06] Lo username è unico senza distinzione tra maiuscole e minuscole (B-57): lo garantisce l'indice unico funzionale ix_users_username_lower su lower(username), e registrazione e login confrontano con func.lower(...). Il valore è comunque memorizzato così come l'utente l'ha scritto, ed è quello mostrato in /admin e nel registro di audit. Poiché lo username ammesso è solo ASCII (^[A-Za-z0-9_.-]+$, 3–32 caratteri, validato dall'API), lower() è l'intera normalizzazione. Resta anche l'indice unico ix_users_username sulla colonna esatta, ridondante rispetto al primo ma innocuo. Codice: app/db/models.py:User · app/auth/routes.py:register · app/auth/routes.py:login · Test: tests/unit/test_auth.py

• [DAT-07] derivation_index è assegnato alla registrazione come max(derivation_index) + 1 (0 per il primo utente). Se il commit fallisce per una collisione sull'indice (due registrazioni concorrenti) si ricalcola e si riprova, fino a 5 volte; una collisione sullo username invece non si riprova e risponde username_taken. derivation_index e address non vengono mai modificati dopo l'inserimento: l'indirizzo è permanente. Codice: app/auth/routes.py:register · app/wallet/hd.py:derive_user_address · Test: tests/unit/test_auth.py

• [DAT-08] cached_balance_sats è uguale, in ogni momento osservabile, alla somma di amount_sats delle righe utxo_events dell'utente con spent_txid IS NULL. La fonte di verità è utxo_events; la colonna è scritta solo da recompute_balance, che va chiamata nella stessa transazione di qualunque modifica alle righe utxo_events dell'utente (un inserimento o un cambio di spent_txid da o verso NULL). I chiamanti sono: credit_confirmed_utxos, reinstate_reappeared_utxos, mark_utxos_spent_externally, place_bet, _release_failed_bet, request_withdrawal, _release_failed_withdrawal e il _abandon del riconciliatore. Il retarget di bump_fee ([DAT-14]) non la ricalcola perché sostituisce un txid con un altro e non cambia la somma. Il saldo "in attesa" mostrato all'utente non è una colonna: è calcolato al volo (vedi registrazione-e-depositi.md). Codice: app/wallet/balance.py:recompute_balance · app/deposits/service.py:credit_confirmed_utxos · app/bets/service.py:place_bet · app/withdrawals/service.py:request_withdrawal · app/tx/reconcile.py:_abandon · Test: tests/unit/test_balance.py, tests/unit/test_deposits.py

• [DAT-09] token_version parte da 0 e aumenta di 1 a ogni cambio password (self-service) e a ogni reset da parte dell'amministratore; nient'altro la modifica. Un token il cui claim tv non coincide con il valore corrente è rifiutato (B-34). Un token senza claim tv vale come tv = 0. Codice: app/api/routes/users.py:change_password · app/api/routes/admin.py:reset_user_password · app/auth/dependencies.py:get_current_user · app/auth/security.py:decode_access_token · Test: tests/unit/test_security.py, tests/unit/test_admin.py

utxo_events

Un output confermato che paga l'indirizzo di un utente: un deposito, il resto di una sua scommessa o di un suo prelievo, una vincita. Dal punto di vista del database sono tutti uguali, perché arrivano tutti dallo stesso percorso (l'accredito dei depositi, vedi registrazione-e-depositi.md).

Colonna	Tipo	Null	Default	Significato	Scritta da
id	Integer PK	no	autoincrement	—	database
user_id	FK users.id, indicizzata	no	—	Proprietario dell'indirizzo pagato	app/deposits/service.py:credit_confirmed_utxos
txid	String(64)	no	—	Transazione che ha creato l'output	credit_confirmed_utxos
vout	Integer	no	—	Posizione dell'output	credit_confirmed_utxos
amount_sats	BigInteger	no	—	Valore dell'output (value di listunspent)	credit_confirmed_utxos
confirmed_height	Integer	no	—	Altezza del blocco riportata dal server (sempre > 0)	credit_confirmed_utxos
confirmed_at	DateTime	no	utcnow	Istante dell'accredito nel database, non l'orario del blocco	ORM
spent_txid	String(64)	sì	NULL	Chi ha speso l'output, vedi [DAT-12]	vedi tabella sotto

• [DAT-10] Una riga si inserisce solo per un output che non è già presente, che ha raggiunto le conferme richieste per l'accredito e che gli altri server Electrum hanno corroborato con lo stesso importo (B-59; la corroborazione è in connessione-electrum.md). Le conferme richieste e la soglia sotto cui un output non si accredita (DUST_LIMIT_SATS) sono regole del deposito, definite in registrazione-e-depositi.md (B-82): lo schema non le ripete. Il numero di conferme configurabile è la colonna round_config.deposit_confirmations ([DAT-62]). L'inserimento è idempotente su (txid, vout), che è unico su tutta la tabella, non solo per utente. Le righe non si cancellano mai e, dopo l'inserimento, solo spent_txid cambia. Codice: app/deposits/service.py:find_new_credit_candidates · app/deposits/service.py:credit_confirmed_utxos · app/electrum/listener.py:ElectrumListener.refresh_user · Test: tests/unit/test_deposits.py, tests/unit/test_electrum_listener.py, tests/unit/test_deposit_confirmations.py

• [DAT-11] Gli UTXO dei montepremi e della riserva non sono in utxo_events: non appartengono a un utente. Il pagamento di un round spende solo gli outpoint delle scommesse di quel round (bet_txid, bet_vout dei partecipanti confirmed, [DAT-63]) all'indirizzo rounds.pool_address, dopo averli verificati sulla rete; qualunque altro output presente allo stesso indirizzo non viene speso. Resta l'esclusione degli outpoint già impegnati da un pagamento non ancora risolto (letti dal raw_tx_hex delle righe pending_transactions di tipo payout in building/pending). Per lo stesso motivo l'abbandono di un pagamento non ha input da liberare. Codice: app/rounds/scheduler.py:_reserved_payout_outpoints · app/rounds/scheduler.py:RoundScheduler._trigger_payout · app/tx/reconcile.py:_abandon · Test: tests/unit/test_scheduler.py, tests/unit/test_pool_verification.py::test_the_payout_spends_exactly_the_verified_bet_outputs_and_nothing_else Un round aperto prima dell'indirizzo per round (il cui montepremi è l'indirizzo di riserva condiviso) conserva la scelta di prima: le monete confermate dell'indirizzo, lette dal server attivo.

• [DAT-12] spent_txid ha tre significati:

  • NULL: l'output è spendibile e conta nel saldo;
  • un txid: l'output è impegnato da una transazione della piattaforma (scommessa o prelievo), dal momento della sua costruzione, prima ancora della trasmissione; resta impostato per sempre una volta confermata;
  • la stringa external-spend: l'output è sparito dalla listunspent e gli altri server hanno confermato che è stato speso fuori dalla piattaforma (B-29).

  Le transizioni ammesse sono queste, e nessun'altra funzione scrive la colonna:

  Da	A	Funzione	Quando
  NULL	txid	app/bets/service.py:place_bet, app/withdrawals/service.py:request_withdrawal	fase 1, prima del broadcast
  txid	nuovo txid	app/tx/broadcast.py:_retarget_txid_references	bump RBF, e conferma di una versione precedente della stessa transazione ([DAT-14])
  txid	NULL	app/bets/service.py:_release_failed_bet, app/withdrawals/service.py:_release_failed_withdrawal	la rete ha rifiutato esplicitamente il broadcast
  txid	NULL	app/tx/reconcile.py:_release_inputs	la transazione è stata abbandonata con una prova ([DAT-13])
  NULL	external-spend	app/deposits/service.py:mark_utxos_spent_externally	spesa esterna corroborata
  external-spend	NULL	app/deposits/service.py:reinstate_reappeared_utxos	l'outpoint ricompare come non speso, corroborato dal quorum

  Chi sceglie gli input di una nuova transazione considera solo le righe con spent_txid IS NULL. Codice: le funzioni in tabella · Test: tests/unit/test_deposits.py, tests/unit/test_bets.py, tests/unit/test_withdrawals.py, tests/unit/test_reconcile.py, tests/unit/test_confirmation_proof.py

• [DAT-13] Il riconciliatore libera un input solo se il suo spent_txid è uno dei txid della riga che sta abbandonando (current_txid o una sua versione in pending_transaction_txids, [DAT-70]): se nel frattempo lo stesso output risulta impegnato da un'altra transazione, quella rivendicazione è quella valida e non va toccata. Codice: app/tx/reconcile.py:_release_inputs · Test: tests/unit/test_reconcile.py, tests/unit/test_tx_versions.py

• [DAT-14] Per ogni output impegnato, spent_txid è sempre il txid corrente della transazione che lo spende, cioè il current_txid della sua riga pending_transactions. Un bump RBF cambia il txid e quindi riscrive, nella stessa transazione del database, ogni spent_txid uguale al vecchio txid. Quando conferma una versione diversa da quella corrente (una versione precedente registrata in pending_transaction_txids, [DAT-70]), current_txid, raw_tx_hex, lo spent_txid degli input e il txid della riga di dominio vengono riportati a quella versione, nella stessa transazione del database che segna la conferma. Senza questo allineamento [DAT-13] non riconoscerebbe più gli input di una transazione rimpiazzata. Codice: app/tx/broadcast.py:bump_fee · app/tx/broadcast.py:_retarget_txid_references · app/tx/confirmation.py:poll_once · Test: tests/unit/test_broadcast.py, tests/unit/test_tx_versions.py

rounds

Un round della lotteria, dall'apertura al pagamento confermato. I round chiusi restano nella tabella come storico.

Colonna	Tipo	Null	Default	Significato	Scritta da
id	Integer PK	no	autoincrement	—	database
status	String(16)	no	"open"	Fase del round: open, closing, drawing, paying_out, closed (RND-02)	vedi RND-02
opened_at	DateTime	no	utcnow	Apertura; base della scadenza	app/rounds/service.py:open_new_round_if_needed (default)
closed_at	DateTime	sì	NULL	Chiusura delle scommesse, poi conferma del pagamento (RND-28)	app/rounds/scheduler.py:RoundScheduler._tick; app/rounds/confirmation.py:_on_payout_confirmed
duration_seconds	Integer	no	600 (anche server_default)	Durata di questo round, copiata da round_config all'apertura	open_new_round_if_needed
cooldown_seconds	Integer	no	30 (anche server_default)	Pausa dopo questo round, copiata da round_config all'apertura	open_new_round_if_needed
bet_amount_sats	BigInteger	no	backfill da round_config nella migrazione	Costo della scommessa di questo round, copiato all'apertura ([DAT-61])	open_new_round_if_needed
fee_address	String(128)	no	backfill da round_config nella migrazione	Destinatario della commissione di questo round, copiato all'apertura ([DAT-61])	open_new_round_if_needed
max_round_pool_sats	BigInteger	no	0 (server_default); backfill 0	Tetto alla somma delle scommesse di questo round, copiato all'apertura ([DAT-61], [DAT-27]); 0 = nessun tetto	open_new_round_if_needed
draw_confirmations	Integer	no	3 (server_default)	Conferme richieste sul blocco di estrazione di questo round, copiate all'apertura ([DAT-61])	open_new_round_if_needed
pool_address	String(128)	no	"" solo fino alla fine della transazione di apertura; backfill con l'indirizzo di riserva per i round esistenti	Indirizzo del montepremi di questo round ([DAT-60])	open_new_round_if_needed
server_seed_enc	Text	sì	NULL	Il segreto del round cifrato con Fernet, con la stessa chiave della master xprv; scritto all'apertura ([DAT-64])	open_new_round_if_needed
server_seed	String(64)	sì	NULL	Il segreto in chiaro (32 byte in esadecimale), scritto solo alla rivelazione ([DAT-64])	RoundScheduler._complete_draw; RoundScheduler._close_and_draw (round senza partecipanti)
server_seed_hash	String(64)	sì	NULL	sha256(server_seed) in esadecimale, pubblicato dall'apertura ([DAT-64])	open_new_round_if_needed
participants_hash	String(64)	sì	NULL	Impronta dell'elenco ordinato dei partecipanti ([DAT-65])	RoundScheduler._close_and_draw
draw_height	Integer	sì	NULL	Altezza del blocco di estrazione, fissata all'ingresso in drawing ([DAT-65])	_close_and_draw
drawing_started_at	DateTime	sì	NULL	Istante del passaggio a drawing	RoundScheduler._close_and_draw
draw_block_height	Integer	sì	NULL	Altezza del blocco usato per l'estrazione; è sempre uguale a draw_height	_complete_draw
draw_block_hash	String(64)	sì	NULL	Hash (esadecimale) di quel blocco	_complete_draw
seed_int	String(128)	sì	NULL	Il seme in decimale (fino a 78 cifre, per questo è una stringa). Il valore intero di sha256(server_seed ‖ block_hash ‖ participants_hash), con la codifica definita in ciclo-di-vita-del-round.md (RND-17)	_complete_draw
winner_user_id	FK users.id	sì	NULL	Il vincitore	_complete_draw
pool_amount_sats	BigInteger	sì	NULL	Montepremi: somma delle scommesse confermate e ancora sulla catena ([DAT-21], [DAT-67])	_close_and_draw, _complete_draw; RoundScheduler._trigger_payout (la ridefinisce la verifica, solo al ribasso)
winner_amount_sats	BigInteger	sì	NULL	Quanto riceve il vincitore (70% meno la commissione di rete)	RoundScheduler._trigger_payout
fee_amount_sats	BigInteger	sì	NULL	Quanto va a fee_address: 30% più il resto dell'arrotondamento, meno bump_reserve_sats	_trigger_payout
bump_reserve_sats	BigInteger	sì	NULL	Output di riserva per l'RBF del pagamento, verso l'indirizzo di riserva ([DAT-73])	_trigger_payout; app/tx/broadcast.py:bump_fee
payout_txid	String(64)	sì	NULL	Txid corrente della transazione di pagamento	_trigger_payout; _retarget_txid_references; app/tx/reconcile.py:_abandon
payout_blocked_at	DateTime	sì	NULL	Il pagamento è trattenuto perché la scommessa del vincitore non è più sulla catena ([DAT-68])	_trigger_payout; app/api/routes/admin.py:resume_lottery

• [DAT-15] Assorbito da RND-02: i valori di status e le transizioni sono comportamento del round, e il loro unico proprietario è il documento del ciclo di vita. Qui restano i fatti di schema: status è una String(16) senza CHECK ([DAT-03]); gli stati open, closing, drawing, paying_out sono quelli attivi coperti dall'indice ix_rounds_single_active ([DAT-17]); la sola scrittura di status fuori dal codice applicativo è la migrazione 8a1c4e7b2d90 ([DAT-59]). Il trattenimento di un pagamento (B-82) non è un nuovo stato ma la colonna payout_blocked_at ([DAT-68]), così l'elenco degli stati attivi, e con esso l'indice su espressione, non cambia.

• [DAT-16] place_bet protegge l'inserimento del partecipante con un compare-and-set sulla riga del round, nella stessa transazione: UPDATE rounds SET status = 'open' WHERE id = :id AND status = 'open'. Se l'aggiornamento non tocca esattamente una riga la transazione viene annullata e la scommessa rifiutata prima di qualunque broadcast (B-53). L'aggiornamento non cambia alcun valore: garantisce che, al commit, il round fosse ancora open nella stessa transazione che inserisce il partecipante, così il passaggio a closing e l'inserimento non possono intrecciarsi. La transazione è aperta con BEGIN IMMEDIATE ([DAT-74]), quindi le letture che precedono il compare-and-set (saldo, partecipanti già presenti) vedono lo stesso stato su cui si scrive. Un OperationalError in questo punto è il busy_timeout scaduto (database is locked), non un conflitto di snapshot: la scommessa è rifiutata allo stesso modo, perché nulla è ancora stato trasmesso. Codice: app/bets/service.py:place_bet · Test: tests/unit/test_bets.py · tests/unit/test_sqlite_transactions.py

• [DAT-17] Al massimo un round attivo alla volta, come vincolo del database: ix_rounds_single_active è un indice unico sull'espressione costante (1), parziale sugli stati attivi (WHERE status IN ('open', 'closing', 'drawing', 'paying_out')). La tabella può quindi contenere quanti round closed si vuole, ma un secondo inserimento attivo fallisce con IntegrityError (B-09). open_new_round_if_needed gestisce il conflitto annullando il proprio inserimento e adottando il round del concorrente, con al massimo 3 tentativi. L'indice è dichiarato sia con sqlite_where sia con postgresql_where, così resta parziale anche su PostgreSQL. get_active_round ordina comunque per id decrescente e registra un errore se ne trova più di uno. Codice: app/db/models.py:Round · app/rounds/service.py:open_new_round_if_needed · app/rounds/service.py:get_active_round · Test: tests/unit/test_rounds_service.py

• [DAT-18] Assorbito da RND-05 e RND-04: la copia della durata e della pausa all'apertura, la scadenza e il conteggio della pausa dal closed_at sono regole del round. Qui resta il fatto di schema: duration_seconds e cooldown_seconds sono colonne NOT NULL con server_default (tabella sopra), scritte solo all'inserimento del round.

• [DAT-19] Assorbito da RND-28: le due scritture di closed_at (chiusura delle scommesse, poi conferma del pagamento) sono comportamento del round. Qui resta il fatto di schema: closed_at è l'unica colonna di rounds scritta due volte.

• [DAT-20] drawing_started_at si scrive una sola volta, al passaggio a drawing. Da lì si misura quanto l'estrazione sta aspettando un blocco (draw_waiting_since in GET /rounds/current, le voci draw_stalled del registro, B-36). Codice: app/rounds/scheduler.py:RoundScheduler._close_and_draw · app/api/routes/rounds.py:current_round · Test: tests/unit/test_rounds_route.py

• [DAT-21] draw_block_height, draw_block_hash, seed_int, winner_user_id e pool_amount_sats si scrivono insieme, nella stessa transazione che porta il round a paying_out. Poi draw_block_height, draw_block_hash, seed_int e winner_user_id non cambiano più. pool_amount_sats è la somma di bet_amount_sats dei soli partecipanti confirmed, gli stessi fra cui si estrae il vincitore; può essere solo ridotto, una volta, dalla verifica che precede il pagamento ([DAT-67]). Codice: app/rounds/scheduler.py:RoundScheduler._close_and_draw · Test: tests/unit/test_scheduler.py

• [DAT-22] winner_amount_sats, fee_amount_sats, bump_reserve_sats e payout_txid si scrivono nella fase 3 di _trigger_payout, insieme alla riga pending_transactions del pagamento e prima del broadcast (B-25). payout_txid segue poi il txid corrente a ogni bump RBF. Quando il riconciliatore abbandona il pagamento tornano tutti a NULL, nella stessa transazione del database, così un round in paying_out senza pagamento in volo non mostra importi di un tentativo che non esiste più. Un bump RBF non cambia winner_amount_sats né fee_amount_sats: riduce solo l'output di riserva ([DAT-73]). Codice: app/rounds/scheduler.py:RoundScheduler._trigger_payout · app/tx/broadcast.py:_retarget_txid_references · app/tx/reconcile.py:_abandon · Test: tests/unit/test_scheduler.py, tests/unit/test_broadcast.py, tests/unit/test_reconcile.py, tests/unit/test_state_consistency.py

• [DAT-60] Ogni round ha il proprio indirizzo del montepremi, pool_address, derivato all'apertura da m/84'/746'/0'/2/<id> e scritto nella stessa transazione che inserisce il round (l'id è noto dopo il flush, prima del commit); non cambia più. Le scommesse del round pagano questo indirizzo. I round aperti prima della migrazione ricevono come pool_address l'indirizzo unico di oggi (m/84'/746'/0'/1/0), che da allora è l'indirizzo di riserva: il loro pagamento spende, come per ogni round, gli outpoint delle loro scommesse a quell'indirizzo ([DAT-11]). Per questo la colonna non ha un vincolo UNIQUE: i round nuovi hanno indirizzi distinti per costruzione (percorso diverso per ogni id), i round vecchi condividono l'indirizzo di riserva. Codice: app/rounds/service.py:open_new_round_if_needed · app/wallet/hd.py:derive_pool_key · migrations/versions/477bddae1a84_add_pool_address_to_rounds.py · Test: tests/unit/test_round_pool_address.py

  La colonna ha "" come valore di inserimento (l'id non esiste prima del flush) e "" come server_default, che serve solo ad aggiungerla a una tabella con righe; il codice lo riscrive prima del commit e ogni consumatore rifiuta un indirizzo vuoto.

• [DAT-61] bet_amount_sats, fee_address, max_round_pool_sats e draw_confirmations di rounds sono una copia dei valori di round_config presa quando il round si apre, nella stessa transazione dell'inserimento, e non cambiano più. Le scommesse del round costano rounds.bet_amount_sats, il suo pagamento paga la commissione a rounds.fee_address, la sua estrazione attende rounds.draw_confirmations conferme, le scommesse oltre il tetto rounds.max_round_pool_sats sono rifiutate. La regola di dominio è in ciclo-di-vita-del-round.md (B-95). La migrazione riempie le righe esistenti con i valori attuali di round_config ([DAT-55]). Codice: app/rounds/service.py:open_new_round_if_needed · app/bets/service.py:place_bet · app/rounds/scheduler.py:RoundScheduler._trigger_payout · Test: tests/unit/test_round_snapshot.py, tests/unit/test_rounds_service.py, tests/unit/test_migrations.py

• [DAT-64] Il segreto del round sono 32 byte casuali generati con secrets.token_bytes(32). All'inserimento del round si scrivono insieme server_seed_hash (sha256 di quei 32 byte, esadecimale minuscolo) e server_seed_enc: il segreto cifrato con Fernet usando la stessa chiave che cifra la master xprv (il file di cifratura, sicurezza.md SEC-53). Il segreto in chiaro non viene mai scritto nel database prima della rivelazione: server_seed resta NULL e si valorizza (esadecimale minuscolo, 64 caratteri) nella stessa transazione che scrive winner_user_id, oppure alla chiusura di un round senza partecipanti. Così una copia del database o un suo backup, senza la chiave, non rivela il segreto di un round ancora aperto. Nessun endpoint e nessuna voce del registro di audit contengono il segreto prima della rivelazione (api.md, GET /rounds/{id}/proof). Nessuna colonna cambia più dopo la rivelazione; server_seed_enc resta com'è. Se all'avvio la chiave non decifra il server_seed_enc di un round non ancora rivelato, il server rifiuta di partire (sicurezza.md SEC-49). I round aperti prima della migrazione hanno le tre colonne a NULL. Codice: app/rounds/service.py:open_new_round_if_needed · app/rounds/scheduler.py:RoundScheduler._complete_draw · app/api/routes/rounds.py:round_proof · Test: tests/unit/test_rounds_service.py, tests/unit/test_draw_proof.py

• [DAT-65] All'ingresso in drawing, nella stessa transazione che cambia lo stato, si scrivono draw_height, participants_hash e il draw_position di ogni partecipante confirmed ([DAT-66]). draw_height è l'altezza della punta nota in quel momento più 1; participants_hash è l'impronta dell'elenco ordinato dei partecipanti con la codifica di ciclo-di-vita-del-round.md (B-73). Nessuno dei tre cambia più. Sono ciò che permette di riprendere l'estrazione dopo un errore o un riavvio ottenendo lo stesso vincitore (B-81): la ripresa legge questi valori dal database e non ricalcola nulla. Codice: app/rounds/scheduler.py:RoundScheduler._close_and_draw · Test: tests/unit/test_scheduler.py

• [DAT-67] Prima di costruire il pagamento, gli outpoint delle scommesse vengono verificati sulla rete; i partecipanti la cui scommessa non c'è più passano a reorged_out ([DAT-24]) e pool_amount_sats viene riscritto come somma dei partecipanti rimasti confirmed, nella stessa transazione del database. Il valore scritto è sempre minore o uguale al precedente. La regola di dominio (quorum, ripartizione su ciò che c'è) è in ciclo-di-vita-del-round.md (B-82). Codice: app/rounds/scheduler.py:RoundScheduler._verify_round_pool · Test: tests/unit/test_pool_verification.py

• [DAT-68] payout_blocked_at si scrive quando la verifica di [DAT-67] trova assente la scommessa del vincitore: il round resta paying_out, non si costruisce alcun pagamento finché la colonna non è NULL, e nella stessa transazione si attiva la pausa con paused_reason = 'payout_blocked' ([DAT-69]). Torna NULL solo con POST /admin/resume (api.md), dopo di che la verifica si ripete al tentativo successivo. Codice: app/rounds/scheduler.py:RoundScheduler._trigger_payout · app/api/routes/admin.py:resume_lottery · Test: tests/unit/test_pool_verification.py::test_the_winners_own_bet_gone_holds_the_payout_and_pauses_the_lottery, tests/unit/test_pool_verification.py::test_after_the_resume_the_verification_repeats, tests/unit/test_admin.py::test_resume_releases_a_payout_held_because_the_winners_bet_left_the_chain

• [DAT-73] Ogni pagamento ha un output di riserva verso l'indirizzo di riserva (m/84'/746'/0'/1/0), di valore max(DUST_LIMIT_SATS, vsize stimata × 10 sat/vB), preso dalla quota delle commissioni; bump_reserve_sats ne registra il valore iniziale. Un bump RBF del pagamento riduce questo output (non winner_amount_sats né fee_amount_sats) e non riscrive bump_reserve_sats. La regola di dominio è in ciclo-di-vita-del-round.md (B-79). Codice: app/rounds/scheduler.py:RoundScheduler._trigger_payout · app/wallet/psbt_builder.py:build_payout_transaction · app/tx/broadcast.py:bump_fee · Test: tests/unit/test_scheduler.py::test_trigger_payout_always_carries_a_bump_reserve_to_the_reserve_address, tests/unit/test_broadcast.py::test_a_payout_is_bumped_out_of_its_reserve_output_and_nothing_else, tests/unit/test_migrations.py::test_the_bump_reserve_column_exists_at_head_and_is_nullable

round_participants

Una scommessa: un utente in un round.

Colonna	Tipo	Null	Default	Significato	Scritta da
id	Integer PK	no	autoincrement	—	database
round_id	FK rounds.id, indicizzata	no	—	Il round	app/bets/service.py:place_bet
user_id	FK users.id, indicizzata	no	—	Chi scommette	place_bet
bet_amount_sats	BigInteger	no	—	Quanto arriva davvero al pool: costo della scommessa meno la commissione di rete	place_bet
bet_txid	String(64)	no	—	Txid corrente della scommessa; dopo la conferma, il txid della versione confermata ([DAT-14])	place_bet; app/tx/broadcast.py:_retarget_txid_references
bet_vout	Integer	no	0 (server_default, backfill 0)	Posizione dell'output verso il montepremi nella transazione della scommessa ([DAT-63])	place_bet
broadcast_at	DateTime	no	utcnow	Istante della fase 1 di place_bet, vedi [DAT-25]	place_bet
confirmed_at	DateTime	sì	NULL	Istante in cui la scommessa è risultata confermata	app/bets/confirmation.py:_on_bet_confirmed
draw_position	Integer	sì	NULL	Posizione (da 0) nell'elenco ordinato dell'estrazione; valorizzata solo per i partecipanti confirmed all'ingresso in drawing ([DAT-66])	app/rounds/scheduler.py:RoundScheduler._close_and_draw
status	String(16)	no	"broadcast" (mai usato: si scrive sempre esplicitamente)	Vedi [DAT-24]	vedi diagramma

• [DAT-23] Al massimo una scommessa per utente per round: vincolo unico su (round_id, user_id). place_bet lo verifica già prima di costruire la transazione (already_betting). Il vincolo è anche la chiave con cui i gestori di conferma, bump e riconciliazione ritrovano la riga, invece del txid che cambia. Codice: app/db/models.py:RoundParticipant · app/bets/service.py:place_bet · app/bets/confirmation.py:_on_bet_confirmed · Test: tests/unit/test_bets.py

• [DAT-24] status assume solo i valori building, broadcast, confirmed, reorged_out. Una scommessa che non va a buon fine prima della conferma viene cancellata, e l'utente non risulta nel round. reorged_out è terminale e riguarda solo una scommessa già confirmed il cui outpoint non è più sulla catena alla verifica che precede il pagamento ([DAT-67]): la riga non si cancella, perché ha già un draw_position e fa parte dell'elenco pubblicato con participants_hash, ma non conta nel montepremi. Diagramma del comportamento atteso (oggi manca reorged_out):

  stateDiagram-v2
      [*] --> building: place_bet fase 1
      building --> broadcast: place_bet fase 2 / _promote
      building --> confirmed: _on_bet_confirmed
      broadcast --> confirmed: _on_bet_confirmed
      building --> [*]: cancellata (_release_failed_bet / _abandon)
      broadcast --> [*]: cancellata (_abandon)
      confirmed --> reorged_out: verifica prima del pagamento
      confirmed --> [*]
      reorged_out --> [*]
  

  Da	A	Funzione
  —	building	app/bets/service.py:place_bet (fase 1, prima del broadcast)
  building	broadcast	place_bet (fase 2, broadcast riuscito); app/tx/reconcile.py:_promote
  building, broadcast	confirmed	app/bets/confirmation.py:_on_bet_confirmed
  building	cancellata	app/bets/service.py:_release_failed_bet (broadcast rifiutato esplicitamente)
  building, broadcast	cancellata	app/tx/reconcile.py:_abandon (abbandono con prova)
  confirmed	reorged_out	app/rounds/scheduler.py:RoundScheduler._verify_round_pool (da _trigger_payout), per ogni partecipante tranne il vincitore (RND-39)

  Se il compare-and-set di [DAT-16] fallisce, la riga building non arriva nemmeno al commit. Un broadcast dall'esito ambiguo (timeout, connessione caduta) lascia la riga in building per il riconciliatore (scommesse-e-prelievi.md B-76). Codice: app/bets/service.py:place_bet · app/bets/service.py:_release_failed_bet · app/tx/reconcile.py:_promote · app/tx/reconcile.py:_abandon · app/bets/confirmation.py:_on_bet_confirmed · Test: tests/unit/test_bets.py, tests/unit/test_confirmation.py, tests/unit/test_reconcile.py, tests/unit/test_pool_verification.py

• [DAT-25] broadcast_at è l'istante della fase 1 di place_bet, appena prima del broadcast, e non viene mai riscritto (nemmeno da un bump RBF). Non determina più l'ordine di estrazione, che è quello di [DAT-66]: resta un'informazione per /admin e per il registro. Codice: app/bets/service.py:place_bet · app/rounds/scheduler.py:RoundScheduler._close_and_draw · Test: tests/unit/test_scheduler.py

• [DAT-63] bet_vout è l'indice dell'output della scommessa che paga rounds.pool_address; con il costruttore attuale è sempre 0 (recipient_sats è il primo output). Insieme a bet_txid identifica l'outpoint che l'elenco dell'estrazione pubblica e che il pagamento spende ([DAT-11]). Codice: app/bets/service.py:place_bet · app/wallet/psbt_builder.py:RECIPIENT_VOUT · Test: tests/unit/test_bets.py

• [DAT-66] draw_position si scrive una sola volta, all'ingresso in drawing ([DAT-65]), per ogni partecipante confirmed del round: i partecipanti sono ordinati per bet_txid crescente (confronto delle stringhe esadecimali minuscole) e numerati da 0. Il vincolo unico (round_id, draw_position) impedisce due partecipanti nella stessa posizione; i partecipanti senza posizione hanno NULL, ammesso più volte dal vincolo. draw_position non cambia nemmeno quando la riga passa a reorged_out. Codice: app/rounds/scheduler.py:RoundScheduler._close_and_draw · app/db/models.py:RoundParticipant · Test: tests/unit/test_scheduler.py, tests/unit/test_migrations.py

• [DAT-26] bet_amount_sats è l'importo del primo output della scommessa (recipient_sats), cioè il costo della scommessa del round (rounds.bet_amount_sats, [DAT-61]) meno la commissione di rete. Non cambia più: un bump RBF riduce il resto dell'utente, non l'output verso il pool. Per questo il montepremi si calcola sommando questa colonna, non moltiplicando il numero di partecipanti per il costo (B-11). Codice: app/bets/service.py:place_bet · app/wallet/psbt_builder.py:build_signed_transaction · app/api/routes/rounds.py:current_round · Test: tests/unit/test_bets.py, tests/unit/test_round_snapshot.py

• [DAT-27] Il numero di righe di un round, contando tutti gli stati, non supera MAX_PARTICIPANTS_PER_ROUND (400): la 401ª scommessa è rifiutata con round_full prima che si muova denaro (B-52). Con rounds.max_round_pool_sats (copiato da round_config all'apertura, [DAT-61], [DAT-62]) diverso da 0, una scommessa è rifiutata con round_full anche quando la somma dei bet_amount_sats del round, contando tutti gli stati, ha già raggiunto quel tetto; la regola di dominio è in scommesse-e-prelievi.md (B-95). Codice: app/bets/service.py:place_bet · app/wallet/psbt_builder.py · Test: tests/unit/test_bets.py, tests/unit/test_round_snapshot.py

round_config

I parametri operativi, in una sola riga, modificabili a caldo da /admin senza riavviare (vedi limiti-noti.md).

Colonna	Tipo	Default	Limiti accettati da PUT /admin/config	Significato	Quando ha effetto
id	Integer PK	autoincrement	—	—	—
fee_address	String(128), non null	nessun default nel modello; la riga nasce con ""	indirizzo PLM bech32 valido	Dove va il 30% di ogni round	Dal round successivo, copiato all'apertura ([DAT-61]); il round in corso paga la commissione all'indirizzo con cui si è aperto
bet_amount_sats	BigInteger	1_000_000_000 (10 PLM)	> 0, ≤ 100 000 PLM	Costo della scommessa; anche il prelievo minimo	Per le scommesse, dal round successivo ([DAT-61]); per il prelievo minimo, subito (ADR-20). Le scommesse già piazzate tengono il proprio importo ([DAT-26])
round_duration_seconds	Integer	600	30 s – 7 giorni	Durata del round	Dal round successivo (RND-05)
round_cooldown_seconds	Integer	30	0 s – 24 h	Pausa dopo la chiusura	Dal round successivo: la pausa dopo il round in corso è quella copiata alla sua apertura (RND-05)
draw_animation_seconds	Integer	20	0 – 600 s	Durata minima dell'animazione dell'estrazione nel frontend; non condiziona l'estrazione vera	Subito (letto da GET /rounds/current)
fee_rate_sat_vb	Integer	1	1 – MAX_FEE_RATE_SAT_VB (5 000)	Commissione di rete delle nuove transazioni	Subito, per le transazioni costruite dopo; quelle in volo tengono il proprio fee_rate_sat_vb
rbf_timeout_seconds	Integer	900	60 s – 7 giorni	Dopo quanto senza conferme si fa un bump RBF	Subito, anche per le transazioni già in volo (il bumper lo rilegge a ogni giro)
deposit_confirmations	Integer	6	1 – 100	Conferme richieste per accreditare un deposito ([DAT-62])	Subito, per gli accrediti successivi; gli output già accreditati restano
draw_confirmations	Integer	3	1 – 20	Conferme richieste sul blocco di estrazione ([DAT-62])	Dal round successivo, copiato all'apertura ([DAT-61])
max_round_pool_sats	BigInteger	0 (tetto disattivato)	0, oppure ≥ bet_amount_sats e ≤ 4 000 000 000 000 000 (400 × 100 000 PLM)	Tetto alla somma delle scommesse di un round ([DAT-27], [DAT-62])	Dal round successivo, copiato all'apertura ([DAT-61]): un round tiene il tetto con cui si è aperto
paused	Boolean	False	non modificabile da PUT /admin/config	Pausa della lotteria	Subito per l'apertura del round successivo; il round in corso non è toccato
paused_reason	String(16)	NULL	non modificabile da PUT /admin/config	Perché la lotteria è in pausa ([DAT-69])	Insieme a paused
updated_at	DateTime	utcnow, aggiornato a ogni modifica (onupdate)	—	Ultima modifica della riga	ORM

• [DAT-28] La tabella contiene al più una riga, creata alla prima lettura con i default di colonna di RoundConfig e fee_address = "". Tutti leggono la configurazione attraverso get_round_config, che prende la prima riga trovata. Il database non impone l'unicità della riga: la garanzia è che solo get_round_config ne inserisce una. I default di un'istanza nuova sono quelli del modello, non variabili d'ambiente. Codice: app/rounds/config.py:get_round_config · app/db/models.py:RoundConfig · Test: tests/unit/test_admin.py

• [DAT-29] Le colonne modificabili da PUT /admin/config sono esattamente fee_address, bet_amount_sats, round_duration_seconds, round_cooldown_seconds, fee_rate_sat_vb, rbf_timeout_seconds, draw_animation_seconds, deposit_confirmations, draw_confirmations, max_round_pool_sats, entro i limiti della tabella sopra. Ogni modifica effettiva scrive una voce config_updated con il valore prima e dopo, e un valore uguale a quello attuale non conta come modifica. Codice: app/api/routes/admin.py:update_config · app/api/routes/admin.py:RoundConfigUpdate · Test: tests/unit/test_admin.py

• [DAT-30] paused e paused_reason si modificano solo con POST /admin/pause e POST /admin/resume, che scrivono lottery_paused e lottery_resumed nel registro (B-10), e con le pause automatiche di [DAT-69], che scrivono la propria voce di audit. Codice: app/api/routes/admin.py:pause_lottery · app/api/routes/admin.py:resume_lottery · Test: tests/unit/test_admin.py

• [DAT-62] deposit_confirmations (predefinito 6) è il numero di conferme dopo cui un output che paga un utente si accredita ([DAT-10]); draw_confirmations (predefinito 3) è il numero di blocchi che la catena deve avere a partire da draw_height prima che si legga l'hash del blocco di estrazione, copiato su ogni round all'apertura ([DAT-61]); max_round_pool_sats (predefinito 0) è il tetto di [DAT-27], disattivato quando vale 0. Il significato di dominio è in registrazione-e-depositi.md (B-82), ciclo-di-vita-del-round.md (B-73) e scommesse-e-prelievi.md (B-95). La migrazione le aggiunge con un server_default temporaneo pari al predefinito ([DAT-55]). Codice: app/db/models.py:RoundConfig · app/rounds/config.py:get_round_config · Test: tests/unit/test_admin.py, tests/unit/test_migrations.py

• [DAT-69] paused_reason è NULL esattamente quando paused è falso; con paused vero vale uno di questi valori, e nessun altro:

  Valore	Chi lo scrive	Quando
  operator	app/api/routes/admin.py:pause_lottery; l'avvio con START_PAUSED=true (operativita.md B-90)	pausa decisa dall'operatore
  reorg	il listener Electrum	riorganizzazione di profondità ≥ 2 (connessione-electrum.md B-85)
  payout_blocked	app/rounds/scheduler.py:RoundScheduler._trigger_payout	la scommessa del vincitore non è più sulla catena ([DAT-68], B-82)
  resurrected_tx	app/tx/reconcile.py:reconcile_once	una riga failed risulta confermata ([DAT-72], B-78)

  Una pausa automatica (reorg, payout_blocked, resurrected_tx) scrive sempre il proprio motivo, anche sopra una pausa già attiva; una pausa operator non sovrascrive un motivo automatico già presente, così la ragione che richiede un controllo non si perde. POST /admin/resume è l'unico modo di tornare a paused = false, paused_reason = NULL, per qualunque motivo. La pausa, come oggi, impedisce solo l'apertura del round successivo. Codice: app/api/routes/admin.py:pause_lottery · app/api/routes/admin.py:resume_lottery · app/electrum/listener.py:ElectrumListener._record_reorg · app/rounds/scheduler.py:RoundScheduler._trigger_payout · app/tx/reconcile.py:reconcile_once · Test: tests/unit/test_pause_reason.py, tests/unit/test_electrum_listener.py

• [DAT-31] Con fee_address vuoto (o di soli spazi) non si apre nessun round (B-66). Una volta impostato, l'API non permette di svuotarlo di nuovo: la validazione rifiuta la stringa vuota come indirizzo non valido. Codice: app/rounds/service.py:rounds_can_open · app/api/routes/admin.py:RoundConfigUpdate · Test: tests/unit/test_rounds_service.py, tests/unit/test_admin.py

pending_transactions

Ogni transazione in uscita dalla piattaforma: scommesse, prelievi e pagamenti. È la riga su cui lavorano il poller delle conferme, il bumper RBF e il riconciliatore (vedi transazioni-e-riconciliazione.md).

Colonna	Tipo	Null	Default	Significato	Scritta da
id	Integer PK	no	autoincrement	—	database
kind	String(16)	no	—	bet, withdrawal o payout	chi crea la riga
round_id	FK rounds.id	sì	NULL	Il round (bet, payout)	place_bet; _trigger_payout
withdrawal_id	FK withdrawals.id	sì	NULL	Il prelievo (withdrawal)	request_withdrawal
user_id	FK users.id	sì	NULL	Il mittente (bet, withdrawal); NULL per payout	place_bet; request_withdrawal
current_txid	String(64)	no	—	Txid della versione più recente della transazione	creazione; bump_fee
fee_rate_sat_vb	Integer	no	—	Commissione della versione corrente, in sat/vB	creazione; bump_fee
raw_tx_hex	Text	no	—	Transazione firmata della versione corrente	creazione; bump_fee
broadcast_at	DateTime	no	utcnow	Prima trasmissione, vedi [DAT-35]	ORM, alla creazione
last_broadcast_at	DateTime	no	utcnow	Ultima trasmissione, vedi [DAT-35]	ORM; bump_fee
status	String(16)	no	"pending" (mai usato: si crea sempre building)	Vedi [DAT-33]	vedi diagramma
replaced_by_txid	String(64)	sì	NULL	Il txid sostituito dall'ultimo bump (nonostante il nome, punta all'indietro). La cronologia completa è in pending_transaction_txids ([DAT-70]) e questa colonna resta solo informativa	bump_fee
attempt_count	Integer	no	1	1 + numero di bump riusciti	bump_fee
failure_reason	String(128)	sì	NULL	Perché la transazione è fallita; solo con status = failed	_abandon; _release_failed_withdrawal
failed_at	DateTime	sì	NULL	Istante del passaggio a failed; da lì si contano i 14 giorni di sorveglianza ([DAT-72])	_abandon; _release_failed_withdrawal
updated_at	DateTime	no	utcnow, onupdate	Ultima modifica	ORM

Le funzioni citate sono app/bets/service.py:place_bet, app/withdrawals/service.py:request_withdrawal, app/rounds/scheduler.py:RoundScheduler._trigger_payout, app/tx/broadcast.py:bump_fee, app/tx/reconcile.py:_abandon e app/withdrawals/service.py:_release_failed_withdrawal.

• [DAT-32] Le colonne di collegamento dipendono da kind:

  kind	round_id	user_id	withdrawal_id	Firmata con	Riga di dominio collegata
  bet	il round	lo scommettitore	NULL	chiave dell'utente	round_participants (round_id, user_id)
  withdrawal	NULL	chi preleva	il prelievo	chiave dell'utente	withdrawals (withdrawal_id)
  payout	il round	NULL	NULL	chiave del montepremi del round (m/84'/746'/0'/2/<round_id>; la chiave di riserva per i round anteriori a B-80)	rounds (round_id)

  I gestori delle conferme e del riconciliatore ritrovano la riga di dominio da questi identificativi, che non cambiano, e non dal txid, che cambia a ogni bump (B-02). L'indirizzo di cui controllare la cronologia si ricava da kind e user_id e, per un payout, da rounds.pool_address del round_id (B-80). Codice: app/tx/pending_address.py:own_address_for · app/tx/broadcast.py:_signing_context · app/bets/confirmation.py:_on_bet_confirmed · app/withdrawals/confirmation.py:_on_withdrawal_confirmed · app/rounds/confirmation.py:_on_payout_confirmed · Test: tests/unit/test_confirmation.py, tests/unit/test_pending_address.py

• [DAT-33] status assume solo i valori building, pending, confirmed, failed. confirmed è terminale; failed è terminale per il bumper e per il poller ordinario, ma per 14 giorni da failed_at la riga resta sorvegliata e può ancora passare a confirmed se una delle sue versioni conferma ([DAT-72]). Diagramma del comportamento atteso (oggi failed è definitivo e il broadcast ambiguo non esiste come caso a sé):

  stateDiagram-v2
      [*] --> building: creazione, prima del broadcast
      building --> building: broadcast ambiguo (timeout, connessione caduta)
      building --> pending: broadcast accettato / _promote
      building --> failed: rifiuto esplicito / _abandon con prova
      building --> [*]: cancellata (_release_failed_bet, rifiuto esplicito)
      pending --> confirmed: poll_once
      pending --> failed: _abandon con prova
      failed --> confirmed: conferma tardiva entro 14 giorni (pending_tx_resurrected)
      confirmed --> [*]
      failed --> [*]: dopo 14 giorni da failed_at
  

  Da	A	Funzione
  —	building	app/bets/service.py:place_bet, app/withdrawals/service.py:request_withdrawal, app/rounds/scheduler.py:RoundScheduler._trigger_payout
  building	pending	le stesse tre, dopo che il server attivo ha accettato il broadcast (anche con un rifiuto "già noto / già nel mempool"); app/tx/reconcile.py:_promote
  pending	confirmed	app/tx/confirmation.py:poll_once (poi il gestore del kind)
  building, pending	failed	app/tx/reconcile.py:_abandon, solo con una prova (transazioni-e-riconciliazione.md B-78)
  building	failed	app/withdrawals/service.py:_release_failed_withdrawal, solo per un rifiuto esplicito
  building	cancellata	app/bets/service.py:_release_failed_bet, solo per un rifiuto esplicito
  failed	confirmed	app/tx/reconcile.py:reconcile_once ([DAT-72])

  Un broadcast dall'esito ambiguo lascia la riga in building per il riconciliatore, per tutti e tre i kind (B-76). Il poller delle conferme guarda le righe pending, più le failed entro 14 giorni. Codice: le funzioni in tabella · Test: tests/unit/test_confirmation.py, tests/unit/test_reconcile.py, tests/unit/test_reconcile_proof.py, tests/unit/test_scheduler.py, tests/unit/test_bets.py, tests/unit/test_withdrawals.py

• [DAT-34] Dopo la creazione, current_txid, raw_tx_hex, fee_rate_sat_vb, replaced_by_txid, attempt_count e last_broadcast_at cambiano solo in bump_fee, e current_txid/raw_tx_hex anche nel riallineamento a una versione precedente confermata ([DAT-14]). bump_fee procede in quest'ordine:

  1. scrive la nuova versione in pending_transaction_txids come candidata, con un commit, prima di trasmetterla ([DAT-71]);
  2. la trasmette;
  3. aggiorna la riga con UPDATE ... WHERE id = :id AND status = 'pending' AND current_txid = :vecchio, insieme al retarget di [DAT-14], in una sola transazione. Se l'aggiornamento tocca zero righe (la riga è stata confermata, abbandonata o già riaccelerata nel frattempo) non scrive nulla e registra il conflitto, che il riconciliatore risolve.

  fee_rate_sat_vb registra la commissione effettivamente pagata dalla nuova versione, non quella obiettivo. Codice: app/tx/broadcast.py:bump_fee · Test: tests/unit/test_broadcast.py, tests/unit/test_tx_versions.py

• [DAT-35] broadcast_at è la prima trasmissione e non viene mai riscritta: è il riferimento da cui il riconciliatore misura i propri tempi di attesa, definiti in transazioni-e-riconciliazione.md. last_broadcast_at è l'ultima trasmissione: la aggiorna bump_fee e da lì should_bump misura se serve un altro bump. Tenerle separate impedisce che bump ripetuti rinviino all'infinito il controllo del riconciliatore (B-27). Codice: app/tx/reconcile.py:_is_due · app/tx/broadcast.py:should_bump · app/tx/broadcast.py:bump_fee · Test: tests/unit/test_broadcast.py, tests/unit/test_reconcile.py

• [DAT-36] failure_reason e failed_at si scrivono solo quando la riga passa a failed, failure_reason troncato a 128 caratteri. Il motivo nomina la prova su cui si è abbandonato (un input speso da un'altra transazione confermata, con il suo txid, oppure il rifiuto esplicito della ritrasmissione, con il messaggio della rete, B-78), o il messaggio d'errore della rete per un broadcast rifiutato. Una riga che risorge ([DAT-72]) conserva failure_reason e failed_at. Codice: app/tx/reconcile.py:_abandon · app/withdrawals/service.py:_release_failed_withdrawal · Test: tests/unit/test_reconcile.py, tests/unit/test_reconcile_proof.py

• [DAT-72] Una riga failed con failed_at negli ultimi 14 giorni resta tra quelle che il riconciliatore controlla a ogni giro (120 s, transazioni-e-riconciliazione.md TX-23), su tutti i txid di pending_transaction_txids. Se una versione risulta confermata, la riga passa a confirmed, si scrive pending_tx_resurrected e la lotteria va in pausa con paused_reason = 'resurrected_tx' ([DAT-69]), tutto nella stessa transazione. Le righe di dominio già riportate indietro dall'abbandono (partecipante cancellato, prelievo failed, input liberati) non vengono ricostruite automaticamente: le riallinea l'operatore (operativita.md B-78). Codice: app/tx/reconcile.py:reconcile_once · app/tx/reconcile.py:_abandon · Test: tests/unit/test_reconcile_proof.py

• [DAT-37] Per le righe bet e withdrawal in building/pending, raw_tx_hex è la fonte del saldo "in attesa" (gli output che tornano all'indirizzo dell'utente). Per le righe payout in building/pending definisce gli outpoint del pool già impegnati ([DAT-11]). Per ogni riga abbandonata definisce gli input da liberare ([DAT-13]). Codice: app/wallet/balance.py:compute_pending_balance · app/rounds/scheduler.py:_reserved_payout_outpoints · app/tx/reconcile.py:_release_inputs · Test: tests/unit/test_balance.py, tests/unit/test_scheduler.py, tests/unit/test_reconcile.py

• [DAT-38] Le righe pending_transactions non si cancellano, con una sola eccezione: la riga bet il cui broadcast è stato rifiutato, cancellata insieme al partecipante da _release_failed_bet. Un prelievo rifiutato invece lascia la riga, in failed. Codice: app/bets/service.py:_release_failed_bet · app/withdrawals/service.py:_release_failed_withdrawal · Test: tests/unit/test_bets.py, tests/unit/test_withdrawals.py

pending_transaction_txids

Ogni versione che una riga pending_transactions abbia mai avuto: la prima, ogni sostituzione RBF trasmessa e ogni candidata scritta prima di un bump. Serve perché una qualunque di queste versioni può essere quella che la rete conferma.

Colonna	Tipo	Null	Default	Significato	Scritta da
id	Integer PK	no	autoincrement	—	database
pending_transaction_id	FK pending_transactions.id, indicizzata	no	—	La transazione di cui questa è una versione	place_bet; request_withdrawal; _trigger_payout; bump_fee
txid	String(64), unico	no	—	Txid della versione	come sopra
raw_hex	Text	no	—	Transazione firmata di questa versione; stringa vuota solo per un replaced_by_txid ricostruito dal backfill della migrazione, di cui la transazione grezza non è più disponibile	come sopra
created_at	DateTime	no	utcnow	Istante in cui la versione è stata scritta, prima della sua trasmissione	ORM

• [DAT-70] Ogni txid che una riga pending_transactions abbia mai avuto come versione ha una riga qui, scritta nello stesso commit che precede la sua trasmissione: la prima versione insieme alla riga pending_transactions in building ([DAT-44]), ogni sostituzione nella fase 1 di bump_fee ([DAT-34]). Le righe non si modificano e non si cancellano; txid è unico su tutta la tabella, quindi un txid appartiene a una sola transazione della piattaforma. Il poller delle conferme e il riconciliatore cercano tutti i txid della riga, non solo current_txid: la conferma di uno qualunque conferma la riga ([DAT-14]). La riga bet cancellata da _release_failed_bet ([DAT-38]) porta con sé le proprie versioni, nella stessa transazione. Codice: app/db/models.py:PendingTransactionTxid · app/tx/versions.py · app/tx/broadcast.py:bump_fee · app/tx/confirmation.py:poll_once · app/tx/reconcile.py:reconcile_once · Test: tests/unit/test_tx_versions.py, tests/unit/test_migrations.py

• [DAT-71] Una versione candidata è una riga di questa tabella il cui txid non è (ancora) current_txid: bump_fee l'ha scritta ma la fase 3 non l'ha ancora adottata, perché il processo si è fermato, la trasmissione è fallita o l'aggiornamento condizionale ha trovato un conflitto. Una candidata mai trasmessa è innocua (nessuno la conferma); una candidata trasmessa e poi confermata viene riconosciuta come qualunque altra versione. Il riconciliatore, quando ritrasmette, usa il raw_tx_hex corrente, non quello di una candidata. Codice: app/tx/broadcast.py:bump_fee · app/tx/reconcile.py:reconcile_once · Test: tests/unit/test_tx_versions.py

withdrawals

Una richiesta di prelievo verso un indirizzo esterno. A differenza di una scommessa, resta nella tabella anche quando fallisce: è un'istruzione data dall'utente.

Colonna	Tipo	Null	Default	Significato	Scritta da
id	Integer PK	no	autoincrement	—	database
user_id	FK users.id, indicizzata	no	—	Chi preleva	app/withdrawals/service.py:request_withdrawal
external_address	String(128)	no	—	Destinatario: un indirizzo PLM valido, diverso da quello dell'utente e da ogni indirizzo di montepremi, di riserva e delle commissioni	request_withdrawal
amount_requested_sats	BigInteger	no	—	Importo chiesto dall'utente	request_withdrawal
amount_sent_sats	BigInteger	sì (sempre valorizzata dal codice)	NULL	Quanto riceve il destinatario, vedi [DAT-40]	request_withdrawal
txid	String(64)	sì	NULL	Txid corrente; NULL dopo un fallimento	request_withdrawal; _retarget_txid_references; _release_failed_withdrawal; _abandon
status	String(16)	no	"pending" (mai usato: si crea sempre building)	Vedi [DAT-39]	vedi diagramma
created_at	DateTime	no	utcnow	Istante della richiesta	ORM
confirmed_at	DateTime	sì	NULL	Istante in cui il prelievo è risultato confermato	app/withdrawals/confirmation.py:_on_withdrawal_confirmed

• [DAT-39] status assume solo i valori building, broadcast, confirmed, failed; gli ultimi due sono terminali. Il passaggio a failed azzera txid. Diagramma del comportamento atteso (oggi un broadcast ambiguo porta a failed come un rifiuto):

  stateDiagram-v2
      [*] --> building: request_withdrawal
      building --> building: broadcast ambiguo (risposta 202)
      building --> broadcast: broadcast accettato / _promote
      building --> confirmed: _on_withdrawal_confirmed
      broadcast --> confirmed: _on_withdrawal_confirmed
      building --> failed: rifiuto esplicito / _abandon con prova
      broadcast --> failed: _abandon con prova
      confirmed --> [*]
      failed --> [*]
  

  Da	A	Funzione
  —	building	app/withdrawals/service.py:request_withdrawal
  building	broadcast	request_withdrawal (broadcast accettato); app/tx/reconcile.py:_promote
  building, broadcast	confirmed	app/withdrawals/confirmation.py:_on_withdrawal_confirmed
  building	failed	app/withdrawals/service.py:_release_failed_withdrawal (rifiuto esplicito)
  building, broadcast	failed	app/tx/reconcile.py:_abandon (abbandono con prova)

  Se la riga pending_transactions di un prelievo failed risorge ([DAT-72]), il prelievo resta failed finché l'operatore non lo riallinea. Codice: le funzioni in tabella · Test: tests/unit/test_withdrawals.py, tests/unit/test_reconcile.py, tests/unit/test_reconcile_proof.py

• [DAT-40] amount_requested_sats è ciò che l'utente ha chiesto; amount_sent_sats è l'output effettivo verso il destinatario: l'importo chiesto meno la commissione di rete e, per un prelievo dell'intero saldo, meno quanto serve a lasciare un resto di DUST_LIMIT_SATS (B-62). Le due colonne si scrivono insieme alla creazione e non cambiano più: un bump RBF riduce il resto, non l'output del destinatario. Codice: app/withdrawals/service.py:request_withdrawal · app/wallet/psbt_builder.py:build_signed_transaction · Test: tests/unit/test_withdrawals.py

bug_reports

Una segnalazione inviata da /report-bug, anche senza login.

Colonna	Tipo	Null	Default	Significato	Scritta da
id	Integer PK	no	autoincrement	—	database
description	Text	no	—	Testo della segnalazione, già ripulito dagli spazi, 1–2000 caratteri (limite dell'API)	app/api/routes/bug_reports.py:create_bug_report
contact	String(256)	sì	NULL	Recapito facoltativo; una stringa vuota diventa NULL	create_bug_report
user_id	FK users.id	sì	NULL	Chi ha segnalato, se aveva fatto il login	create_bug_report
status	String(16)	no	"open"	open, read o resolved	create_bug_report (default); app/api/routes/admin.py:update_bug_report_status
created_at	DateTime	no	utcnow	Istante della segnalazione	ORM

• [DAT-41] status assume solo i valori open, read, resolved. Nasce open. Poi lo cambia solo l'amministratore, verso uno qualunque dei tre valori (il percorso previsto è open → read → resolved, ma anche i ritorni indietro sono ammessi). Ogni cambio effettivo scrive bug_report_status_changed con il valore prima e dopo, e riassegnare lo stesso valore non scrive nulla (B-60). read esiste per permettere a chi ha segnalato di distinguere "visto" da "risolto". Codice: app/api/routes/admin.py:update_bug_report_status · app/api/routes/bug_reports.py:create_bug_report · Test: tests/unit/test_bug_reports.py

audit_log

Il registro di audit: chi ha fatto cosa, e quando. Il catalogo dei tipi di evento e dei loro payload è in audit-log.md.

Colonna	Tipo	Null	Default	Significato
id	Integer PK	no	autoincrement	Ordine di scrittura
event_type	String(32)	no	—	Tipo di evento (es. bet_placed, payout_failed)
payload_json	Text	no	—	Dettagli dell'evento, oggetto JSON serializzato
user_id	FK users.id	sì	NULL	Utente interessato, se c'è
round_id	FK rounds.id	sì	NULL	Round interessato, se c'è
created_at	DateTime	no	utcnow	Istante di scrittura

• [DAT-42] Il registro è in sola aggiunta: le righe si inseriscono solo con write_audit_log, che aggiunge la riga alla sessione del chiamante senza fare commit, così la voce entra nella stessa transazione del cambiamento che descrive. Nessun codice aggiorna o cancella righe di audit_log. payload_json è Text (B-47). Codice: app/audit/log.py:write_audit_log · Test: tests/unit/test_models_text_columns.py, tests/unit/test_admin.py

• [DAT-43] Il registro non è solo informativo. Il created_at dell'ultima voce payout_failed di un round fa da orologio per il riprovare del pagamento: un nuovo tentativo parte solo 60 s dopo (B-26). Per questo ogni uscita anticipata di _trigger_payout scrive una voce payout_failed. Codice: app/rounds/scheduler.py:RoundScheduler._retry_payout_if_due · app/rounds/scheduler.py:RoundScheduler._log_payout_failure · Test: tests/unit/test_scheduler.py

alembic_version

Tabella di servizio di Alembic: una riga con la revisione applicata (version_num). Non è nel modello e il codice applicativo non la legge.

Indici e vincoli

Nome	Tabella	Definizione	Perché
PK id	tutte	intero autoincrementale	[DAT-05]
ix_users_username	users	UNIQUE (username)	unicità esatta, dallo schema iniziale; ridondante rispetto alla riga sotto
ix_users_username_lower	users	UNIQUE (lower(username))	username senza distinzione di maiuscole, [DAT-06] (B-57)
unico derivation_index	users	UNIQUE	un indice HD, quindi un indirizzo, per utente, [DAT-07]
unico address	users	UNIQUE	un indirizzo non appartiene a due utenti
unico (txid, vout)	utxo_events	UNIQUE	un outpoint si accredita una volta sola, [DAT-10]
ix_utxo_events_user_id	utxo_events	indice su user_id	calcolo del saldo e selezione degli UTXO per utente
ix_rounds_single_active	rounds	UNIQUE ((1)) WHERE status IN ('open','closing','drawing','paying_out')	al massimo un round attivo, [DAT-17] (B-09)
unico (round_id, user_id)	round_participants	UNIQUE	una scommessa per utente per round, [DAT-23]
unico (round_id, draw_position)	round_participants	UNIQUE (i NULL non collidono)	una posizione di estrazione per partecipante, [DAT-66]; creato come indice (ux_round_participants_round_draw_position)
unico txid	pending_transaction_txids	UNIQUE	un txid appartiene a una sola transazione, [DAT-70]
ix_pending_transaction_txids_pending_transaction_id	pending_transaction_txids	indice	versioni di una transazione
ix_round_participants_round_id	round_participants	indice	partecipanti di un round
ix_round_participants_user_id	round_participants	indice	round di un utente (last-round-result)
ix_withdrawals_user_id	withdrawals	indice	prelievi di un utente

Non ci sono indici su pending_transactions, audit_log o bug_reports oltre alla chiave primaria. In particolare pending_transactions.status e audit_log.event_type/round_id, usati dai filtri dei task in background, sono letti con una scansione completa.

Invarianti tra tabelle

• [DAT-44] Prima si scrive, poi si trasmette. Ogni versione di una transazione in uscita esiste nel database prima di essere trasmessa. Per la prima versione: la riga pending_transactions in building, la sua riga in pending_transaction_txids, la riga di dominio (partecipante in building, prelievo in building, oppure payout_txid sul round) e, per scommesse e prelievi, gli input già marcati con spent_txid, tutto in un commit che precede il broadcast (B-08, B-25). Per una sostituzione RBF: la sua riga candidata in pending_transaction_txids, in un commit che precede il broadcast ([DAT-34], [DAT-71]). Nessuna sessione del database resta aperta durante una chiamata di rete. Codice: app/bets/service.py:place_bet · app/withdrawals/service.py:request_withdrawal · app/rounds/scheduler.py:RoundScheduler._trigger_payout · app/tx/broadcast.py:bump_fee · Test: tests/unit/test_bets.py, tests/unit/test_scheduler.py, tests/unit/test_tx_versions.py

• [DAT-45] Ogni versione è riconoscibile, una sola è corrente. Per ogni transazione non terminale, pending_transactions.current_txid coincide con il txid registrato sulla riga di dominio (round_participants.bet_txid, withdrawals.txid, rounds.payout_txid) e con lo spent_txid di ogni input impegnato ([DAT-14]); ogni altro txid che la transazione abbia avuto è in pending_transaction_txids ([DAT-70]), e la conferma di uno qualunque di essi conferma la transazione. current_txid cambia solo con un aggiornamento condizionato al valore precedente ([DAT-34]), che aggiorna tutti i riferimenti nella stessa transazione del database. Per un pagamento, payout_txid si aggiorna solo se era ancora uguale al vecchio txid. Codice: app/tx/broadcast.py:_retarget_txid_references · app/tx/broadcast.py:bump_fee · app/tx/confirmation.py:poll_once · Test: tests/unit/test_broadcast.py, tests/unit/test_tx_versions.py

• [DAT-46] Il montepremi di ogni round si conserva, separato dagli altri. Per un round pagato, con i soli partecipanti rimasti confirmed dopo la verifica di [DAT-67]:

  • gli input del pagamento sono esattamente gli outpoint (bet_txid, bet_vout) di quei partecipanti, all'indirizzo rounds.pool_address del round ([DAT-11]); nessun input viene da un altro round o da un altro indirizzo;
  • pool_amount_sats = somma dei loro bet_amount_sats = somma dei valori di quegli input;
  • 70% (arrotondato per difetto) di pool_amount_sats = winner_amount_sats
    • commissione di rete del pagamento;
  • il resto (30% più l'arrotondamento) = fee_amount_sats + bump_reserve_sats ([DAT-73]).

  Quindi pool_amount_sats = winner_amount_sats + fee_amount_sats + bump_reserve_sats + commissione di rete, al satoshi. La commissione di rete esce dalla quota del vincitore, la riserva da quella della piattaforma. La ripartizione è nel codice, non in round_config. Codice: app/rounds/service.py:winner_share · app/rounds/scheduler.py:RoundScheduler._trigger_payout · app/wallet/psbt_builder.py:build_payout_transaction · Test: tests/unit/test_payout_builder.py, tests/unit/test_scheduler.py, tests/unit/test_pool_verification.py::test_a_reorged_out_bet_is_excluded_and_the_split_is_computed_on_what_is_there

• [DAT-47] Assorbito da RND-12: l'attesa delle scommesse in volo prima dell'estrazione è comportamento del round. Il testo di qui diceva anche che l'attesa finisce perché ogni riga in volo viene "prima o poi" confermata o cancellata: non è una garanzia (una riga non viene abbandonata senza una prova, B-78) e non va ripetuta.

• [DAT-48] Assorbito da RND-26: le due protezioni contro il doppio pagamento sono comportamento del round. Lo schema non ha un vincolo che le imponga; ciò che il database fornisce è il raw_tx_hex da cui si leggono gli outpoint impegnati ([DAT-37]).

• [DAT-49] Le transazioni ritrovano la loro riga senza il txid. Le righe collegate a una pending_transactions si ritrovano tramite round_id, user_id e withdrawal_id ([DAT-32]); il txid serve solo come ripiego per righe senza collegamento. Codice: app/bets/confirmation.py:_on_bet_confirmed · app/rounds/confirmation.py:_on_payout_confirmed · Test: tests/unit/test_confirmation.py

Motore e sessioni

• [DAT-50] C'è un solo motore asincrono per processo, creato all'importazione di app/db/base.py da settings.database_url (DATABASE_URL; predefinito sqlite+aiosqlite:///./plm_lottery.db, e nel container sqlite+aiosqlite:////app/db_data/plm_lottery.db, montato da ./data/db). Le sessioni si creano con AsyncSessionLocal (async_sessionmaker con expire_on_commit=False): gli oggetti letti restano utilizzabili dopo il commit. Codice: app/db/base.py · app/config.py

• [DAT-51] Gli endpoint HTTP ricevono una sessione per richiesta dalla dipendenza get_session. I task in background ricevono AsyncSessionLocal come factory e aprono sessioni brevi, chiuse prima di ogni chiamata di rete. Il commit è sempre esplicito: get_session non ne fa alla fine della richiesta. Codice: app/db/session.py:get_session · app/main.py · Test: tests/unit/test_broadcast.py

• [DAT-52] Su SQLite ogni nuova connessione esegue PRAGMA journal_mode=WAL, PRAGMA synchronous=FULL e PRAGMA busy_timeout=5000 (B-39): lettori e scrittori non si bloccano a vicenda, un secondo scrittore aspetta fino a 5 s invece di fallire subito, e ogni commit è sul disco prima che il commit ritorni, così un'interruzione di corrente non perde un commit già confermato al chiamante (una registrazione, e con essa il suo derivation_index, B-88). Con un altro dialetto non viene eseguito nulla. PRAGMA foreign_keys non è tra questi, da cui [DAT-04]. Codice: app/db/base.py:_register_sqlite_pragmas · Test: tests/unit/test_db_base.py · tests/unit/test_sqlite_transactions.py

• [DAT-74] Ogni transazione del database che sposta denaro o ne fissa lo stato inizia con BEGIN IMMEDIATE, che prende il lock di scrittura di SQLite prima della prima lettura: le letture su cui la transazione decide (saldo, UTXO liberi, partecipanti, stato del round o della riga pending_transactions) e le scritture che ne seguono vedono lo stesso stato, senza che un altro scrittore si inserisca fra le due. Riguarda almeno le fasi di scrittura di place_bet, request_withdrawal, _trigger_payout, bump_fee, l'accredito dei depositi (accredito, marcatura come speso all'esterno, reintegro: quest'ultimo solo quando c'è qualcosa da reintegrare, perché gira a ogni aggiornamento), le transizioni di stato dello scheduler, i gestori di conferma, il riconciliatore e l'allocazione dell'indice di derivazione alla registrazione (senza il lock due registrazioni simultanee leggevano lo stesso massimo). Si ottiene con begin_immediate(session), che chiude l'eventuale transazione di sola lettura già aperta (di solito la ricerca dell'utente autenticato) e apre quella nuova con BEGIN IMMEDIATE; le altre transazioni, in sola lettura, restano BEGIN semplice e in WAL non aspettano nessuno. Un OperationalError database is locked è il busy_timeout di [DAT-52] scaduto: prima di un broadcast la transazione viene annullata e l'operazione rifiutata o rimandata al giro successivo; dopo un broadcast non si annulla nulla sulla rete, la riga resta nello stato scritto prima del broadcast (building) e il riconciliatore la risolve (transazioni-e-riconciliazione.md B-88). Codice: app/db/base.py:begin_immediate · app/db/base.py:_register_sqlite_pragmas · app/bets/service.py:place_bet · app/withdrawals/service.py:request_withdrawal · app/rounds/scheduler.py:RoundScheduler._trigger_payout · app/tx/broadcast.py:bump_fee · app/tx/confirmation.py:poll_once · app/tx/reconcile.py:reconcile_once · app/deposits/service.py:credit_confirmed_utxos · app/auth/routes.py:register · Test: tests/unit/test_sqlite_transactions.py

Migrazioni

Politica

• [DAT-53] Lo schema si cambia solo con una revisione Alembic in migrations/versions/. Si modifica app/db/models.py, poi si genera la revisione con alembic revision --autogenerate -m "..." e la si rilegge e corregge a mano prima del commit. La catena è lineare, senza rami. migrations/env.py prende l'URL da settings.database_url e i metadati da Base.metadata, e non abilita render_as_batch: le modifiche a colonne esistenti su SQLite vanno scritte a mano dentro op.batch_alter_table. Codice: migrations/env.py · app/db/models.py

• [DAT-54] Le migrazioni si applicano solo all'avvio del container: il comando del Dockerfile controlla che esista la master key, esegue alembic upgrade head e solo se riesce avvia uvicorn. Una migrazione che fallisce impedisce quindi al server di partire invece di lasciarlo girare su uno schema a metà. alembic upgrade head non va mai lanciato a mano. Codice: Dockerfile

• [DAT-55] Una colonna NOT NULL aggiunta a una tabella che può avere già righe deve dare un valore a quelle righe nella migrazione stessa, in uno di due modi: un server_default temporaneo, rimosso subito dopo in modo che le righe nuove prendano il default dell'ORM (round_config, users.token_version); oppure un backfill esplicito calcolato dai dati (last_broadcast_at copiato da broadcast_at; rounds.duration_seconds/cooldown_seconds copiati da round_config, così il round in corso non cambia durata con la migrazione). Codice: migrations/versions/53cc70d16e63_add_operational_params_to_round_config.py · migrations/versions/861e76aaf34c_add_last_broadcast_at_to_pending_.py · migrations/versions/283844a44b4a_snapshot_round_timing_onto_the_round_.py

• [DAT-56] Gli indici su espressioni (ix_rounds_single_active, ix_users_username_lower) vanno scritti a mano nella migrazione: l'autogenerate su SQLite non li sa confrontare e li ignora con un avviso. Inoltre op.batch_alter_table su SQLite ricrea la tabella e perde senza errori gli indici su espressioni, perché SQLAlchemy non li legge dallo schema. Una migrazione che usi batch_alter_table su rounds o users deve quindi, nella stessa upgrade(), eliminare e ricreare esplicitamente l'indice relativo con SQL grezzo, dopo il blocco batch_alter_table. Le migrazioni di B-73/B-81/B-95 (e quella di B-80, già scritta) aggiungono colonne a rounds: se aggiungono solo colonne con op.add_column (che su SQLite non ricrea la tabella) l'indice resta; se usano batch_alter_table, devono ricrearlo. Il test di [DAT-75] è ciò che rende questa regola verificabile. Codice: migrations/versions/8a1c4e7b2d90_add_failure_reason_and_single_active_round_index.py · migrations/versions/c1d4a97b5e10_case_insensitive_usernames.py

• [DAT-75] Un test esegue alembic upgrade head su un database SQLite vuoto e verifica che, alla fine della catena, esistano ix_rounds_single_active e ix_users_username_lower con la loro definizione (lette da sqlite_master), e che un secondo round attivo e un secondo username uguale a meno delle maiuscole vengano rifiutati con IntegrityError. Il test gira con la suite unitaria, quindi una migrazione che perde uno dei due indici non entra nel repository. Codice: migrations/env.py · Test: tests/unit/test_migrations.py

• [DAT-57] Una migrazione che incontra dati incompatibili con un nuovo vincolo si comporta in uno di due modi. Se esiste una correzione senza ambiguità, la applica e procede. Se correggere significherebbe decidere sulla proprietà dei fondi, si rifiuta, e il server non parte ([DAT-54]). Le due migrazioni di questo tipo sono [DAT-58] e [DAT-59].

• [DAT-58] c1d4a97b5e10 (username senza distinzione di maiuscole, B-57) si rifiuta di procedere se due account esistenti hanno username che differiscono solo per maiuscole/minuscole: solleva un errore che elenca i gruppi in collisione e non crea l'indice. Non sceglie quale account rinominare, perché entrambi possono contenere fondi: l'operatore rinomina a mano un account (e avvisa l'utente), poi riavvia (vedi operativita.md). Codice: migrations/versions/c1d4a97b5e10_case_insensitive_usernames.py

• [DAT-59] 8a1c4e7b2d90 (indice del round attivo unico, B-09), se trova più round attivi, porta a closed tutti tranne il più recente prima di creare l'indice. È la stessa scelta che get_active_round faceva già in silenzio. Codice: migrations/versions/8a1c4e7b2d90_add_failure_reason_and_single_active_round_index.py

I test unitari creano lo schema direttamente dal modello (Base.metadata.create_all), quindi verificano il modello, non la catena di revisioni; [DAT-75] aggiunge il test che esegue la catena su un database vuoto.

Storia dello schema

In ordine di applicazione (ogni riga rivede la precedente):

Revisione	Contenuto
274efdcbfbcc	Schema iniziale: round_config (solo fee_address, bet_amount_sats, updated_at), users, rounds, utxo_events, withdrawals, audit_log, pending_transactions, round_participants
53cc70d16e63	round_config: round_duration_seconds, round_cooldown_seconds, min_amount_sats, fee_rate_sat_vb, rbf_timeout_seconds (valori prima presi da app/config.py)
1db52f3a7c67	round_config.draw_animation_seconds
5f2079b95b33	round_config.paused
6cb50b29f64c	Rimosso round_config.min_amount_sats: il prelievo minimo è il costo della scommessa
8a1c4e7b2d90	pending_transactions.failure_reason; indice ix_rounds_single_active (B-04, B-09)
861e76aaf34c	pending_transactions.last_broadcast_at, con backfill da broadcast_at (B-27)
943dbd74d983	users.token_version (B-34)
9ef6a51509f7	rounds.drawing_started_at (B-36)
87a0c640355c	audit_log.payload_json e pending_transactions.raw_tx_hex da VARCHAR a Text (B-47)
ee8508d98d34	Tabella bug_reports, con un booleano resolved
be71fdac734e	bug_reports.resolved sostituito da status a tre valori (resolved → 'resolved', il resto → 'open')
c1d4a97b5e10	Indice ix_users_username_lower (B-57), [DAT-58]
283844a44b4a	rounds.duration_seconds/cooldown_seconds (B-61), backfill dalla configurazione attuale
477bddae1a84	rounds.pool_address (B-80, [DAT-60]), backfill con l'indirizzo di riserva per i round esistenti — head

Revisioni attese, non ancora scritte (ciascuna nel commit della sua voce, con i backfill di [DAT-55]): rounds (bet_amount_sats, fee_address, draw_confirmations, server_seed, server_seed_hash, participants_hash, draw_height, payout_blocked_at), round_participants (bet_vout, draw_position e il vincolo unico), round_config (deposit_confirmations, draw_confirmations, max_round_pool_sats, paused_reason, con paused_reason = 'operator' per un'istanza già in pausa), pending_transactions.failed_at (backfill da updated_at per le righe failed), e la tabella pending_transaction_txids (backfill: una riga per current_txid e una per replaced_by_txid non nullo di ogni riga esistente; per la seconda il raw_hex non è più disponibile e si scrive la stringa vuota).

Differenze note tra modello e schema migrato

• users.token_version: il modello dichiara server_default="0", ma la migrazione 943dbd74d983 rimuove il default del server dopo il backfill. Un database migrato non ha quindi un default a livello SQL. Non ha effetti pratici, perché ogni inserimento passa dall'ORM che fornisce 0, e l'autogenerate non confronta i default del server, quindi non lo segnala.
• rounds.duration_seconds/cooldown_seconds: server_default presente sia nel modello sia nel database migrato.

Scelte specifiche di SQLite

• Un solo file, un solo processo. SQLite con WAL regge i sei task in background e le richieste HTTP di un singolo processo uvicorn ([DAT-52]). I lock per utente e il canale SSE sono comunque in memoria, quindi più processi non sono supportati in nessun caso (vedi limiti-noti.md).
• Serializzazione degli scrittori. SQLite ha un solo scrittore alla volta per file; un secondo scrittore aspetta il lock fino al busy_timeout ([DAT-52]) e poi riceve OperationalError: database is locked. Non esistono conflitti fra snapshot: un OperationalError è sempre un'attesa scaduta. Le transazioni che spostano denaro prendono il lock con BEGIN IMMEDIATE prima di leggere ([DAT-74]), quindi il compare-and-set di [DAT-16] e le letture che lo precedono stanno nella stessa transazione. Il driver (pysqlite) non apre più le transazioni da solo (isolation_level = None): è l'evento begin del motore a emettere BEGIN o BEGIN IMMEDIATE, così ogni lettura sta dentro una transazione vera (B-88).
• Indice parziale su espressione. ix_rounds_single_active usa sia un'espressione ((1)) sia una clausola WHERE; SQLite e PostgreSQL supportano entrambe, e il modello dichiara la clausola per tutti e due (sqlite_where, postgresql_where). La migrazione la crea invece con SQL grezzo (CREATE UNIQUE INDEX ... ON rounds ((1)) WHERE ...).
• Timestamp senza fuso. SQLite non conserva il fuso orario ([DAT-02]).
• Chiavi esterne non applicate ([DAT-04]).
• Text invece di VARCHAR senza lunghezza per i contenuti di lunghezza arbitraria (raw_tx_hex, payload_json, description), così lo schema resta portabile ad altri database (B-47).
• Portabilità. I pragma sono limitati al dialetto sqlite e l'indice del round attivo è dichiarato anche per PostgreSQL. Un DATABASE_URL PostgreSQL non è però mai stato provato, e il database supportato resta SQLite.