dcc-rpg/docs/database-schema.md

Database Schema

File: data/dcc_rpg.db (auto-creato)
Engine: SQLite 3 con WAL mode
Connessione: Thread-local via threading.local(), check_same_thread=False

Tabelle

campaigns — Campagne di gioco

CREATE TABLE campaigns (
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    name              TEXT NOT NULL,
    adventure         TEXT,
    turn              INTEGER NOT NULL DEFAULT 0,
    current_area_id   TEXT DEFAULT '',
    current_area_name TEXT DEFAULT '',
    created_at        TEXT NOT NULL DEFAULT (datetime('now'))
);

characters — Personaggi giocanti

CREATE TABLE characters (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    campaign_id   INTEGER NOT NULL REFERENCES campaigns(id),
    name          TEXT NOT NULL,
    level         INTEGER NOT NULL DEFAULT 0,
    char_class    TEXT,
    alignment     TEXT CHECK(alignment IN ('Legge','Caos','Neutralità')),
    occupation    TEXT,
    str           INTEGER NOT NULL DEFAULT 10,
    agi           INTEGER NOT NULL DEFAULT 10,
    sta           INTEGER NOT NULL DEFAULT 10,
    per           INTEGER NOT NULL DEFAULT 10,
    int           INTEGER NOT NULL DEFAULT 10,
    lck           INTEGER NOT NULL DEFAULT 10,
    hp_current    INTEGER NOT NULL DEFAULT 1,
    hp_max        INTEGER NOT NULL DEFAULT 1,
    ac            INTEGER NOT NULL DEFAULT 10,
    armor_type    TEXT DEFAULT 'none',
    luck_current  INTEGER NOT NULL DEFAULT 10,
    xp            INTEGER NOT NULL DEFAULT 0,
    weapon        TEXT,
    equipment     TEXT DEFAULT '[]',
    status        TEXT NOT NULL DEFAULT 'alive'
        CHECK(status IN ('alive','unconscious','dead'))
);
CREATE INDEX idx_char_campaign ON characters(campaign_id);

adventure_state — Stato esplorazione aree

CREATE TABLE adventure_state (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    campaign_id   INTEGER NOT NULL REFERENCES campaigns(id),
    area_id       TEXT NOT NULL,
    visited       INTEGER NOT NULL DEFAULT 0,
    state         TEXT DEFAULT '{}'
);
CREATE UNIQUE INDEX idx_area_campaign ON adventure_state(campaign_id, area_id);

Il campo state contiene JSON con lo stato di enigmi/trappole risolte per l'area.

combat_state — Stato combattimento (PF persistenti mostri)

CREATE TABLE combat_state (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    campaign_id   INTEGER NOT NULL REFERENCES campaigns(id),
    monster_id    TEXT NOT NULL,
    monster_name  TEXT NOT NULL DEFAULT '',
    current_hp    INTEGER NOT NULL,
    max_hp        INTEGER NOT NULL,
    status        TEXT NOT NULL DEFAULT 'active'
        CHECK(status IN ('active', 'defeated', 'fled')),
    UNIQUE(campaign_id, monster_id)
);

character_items — Inventario PG

CREATE TABLE character_items (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    character_id  INTEGER NOT NULL REFERENCES characters(id),
    item_def_id   TEXT,
    name          TEXT NOT NULL,
    quantity      INTEGER DEFAULT 1,
    equipped      INTEGER DEFAULT 0,
    properties    TEXT DEFAULT '{}'
);

quests — Missioni attive/completate/fallite

CREATE TABLE quests (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    campaign_id   INTEGER NOT NULL REFERENCES campaigns(id),
    name          TEXT NOT NULL,
    description   TEXT,
    status        TEXT NOT NULL DEFAULT 'active'
        CHECK(status IN ('active','completed','failed'))
);

turn_log — Log azioni e risultati

CREATE TABLE turn_log (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    campaign_id   INTEGER NOT NULL REFERENCES campaigns(id),
    turn          INTEGER NOT NULL,
    character_id  INTEGER REFERENCES characters(id),
    action        TEXT,
    result        TEXT,
    timestamp     TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX idx_turnlog_campaign ON turn_log(campaign_id, turn);

Tabelle di sistema: critici e fumble

CREATE TABLE crit_fumble_tables (
    id          TEXT PRIMARY KEY,        -- "fumble", "crit_I"..."crit_V"
    name        TEXT NOT NULL,
    description TEXT,
    table_type  TEXT NOT NULL CHECK(table_type IN ('crit', 'fumble')),
    die         TEXT,
    modifier    TEXT
);

CREATE TABLE crit_fumble_entries (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    table_id    TEXT NOT NULL REFERENCES crit_fumble_tables(id),
    roll_min    INTEGER,
    roll_max    INTEGER,
    result_text TEXT NOT NULL
);
CREATE INDEX idx_entries_table ON crit_fumble_entries(table_id);

CREATE TABLE fumble_armor_die (
    armor_type  TEXT PRIMARY KEY,        -- "none", "light", "medium", "heavy"
    die         TEXT NOT NULL
);

Migrazioni

Le migrazioni sono gestite tramite _migrate_schema() chiamata a ogni avvio. Ogni migrazione è un ALTER TABLE avvolto in try/except (se la colonna esiste già, skippa).

Migrazioni attuali:

migrations = [
    "ALTER TABLE campaigns ADD COLUMN current_area_id TEXT DEFAULT ''",
    "ALTER TABLE campaigns ADD COLUMN current_area_name TEXT DEFAULT ''",
]

Popolazione Iniziale

populate_from_json() carica i dati da data/crit_fumble_tables.json:

• 6 tabelle (fumble + crit_I → crit_V)
• 112 entries totali
• 4 mappature armatura→dado fumble

seed_adventure_state() e seed_quests_from_adventure() caricano aree e quest da avventura.json.

CRUD Pubbliche (db.py)

Funzione	Descrizione
init_db()	Schema + migrazioni + popolamento
lookup_crit_fumble(table_id, roll, luck_mod=0)	Cerca risultato crit/fumble con modificatore
get_fumble_die(armor_type)	Dado fumble per tipo armatura
create_campaign(name, adventure)	Nuova campagna
get_campaign(id)	Campagna by ID
create_character(campaign_id, name, class, occupation)	Nuovo PG
get_characters(campaign_id)	Tutti i PG di una campagna
get_character(id)	PG by ID
update_character(id, **kwargs)	Update qualsiasi campo
advance_turn(campaign_id)	Incrementa turno
log_turn(...)	Logga azione
get_log(campaign_id, limit)	Ultimi N turni
set_area_state(...)	Marca area visitata/stato
set_puzzle_state(...)	Stato enigma (solved/unsolved)
is_puzzle_solved(...)	Check enigma
get_area_state(campaign_id)	Stato tutte le aree
set_current_area(...)	Imposta area corrente PG
get_current_area(campaign_id)	Area corrente PG
set_combat_state(...)	Inizializza mostro in combattimento
update_combat_hp(...)	Danno mostro, status defeated
get_combat_state(campaign_id)	Stato combattimento
add_character_item(...)	Aggiunge oggetto (stacking)
remove_character_item(...)	Rimuove oggetto
get_character_items(...)	Inventario PG
equip_character_item(...)	Equipaggia/Unequipaggia
get_all_areas()	Tutte le aree da JSON
get_area_by_id(id)	Area specifica
get_monster_by_id(id)	Stat block mostro
get_monsters_in_area(area_id)	Mostri in area con stats
format_area_context(area_id)	Testo area per prompt LLM
format_all_areas_context()	Tutte le aree per prompt iniziale