epjedZddlZddlZddlZddlmZmZddlmZddl m Z m Z m Z m Z mZmZmZmZmZmZmZejdZGddZdS) uSchema creation, column reconciliation, and FTS DDL management for SessionDB. Mixin contract: this is a plain mixin class consumed by ``hermes_state.SessionDB``. It defines no ``__init__`` and no state of its own; methods access the host's attributes (``self._conn``, ``self.db_path``, ``self._execute_write`` and other SessionDB methods) established by ``SessionDB.__init__``. It must never import hermes_state (cycle) — shared module-level constants live in hermes_state_common. N)DictOptional)get_hermes_home) DEFERRED_INDEX_SQLFTS_CJK_STALE_KEYFTS_SQLFTS_STORAGE_VERSIONFTS_TRIGRAM_SQLLEGACY_FTS_SQLLEGACY_FTS_TRIGRAM_SQL SCHEMA_SQLSCHEMA_VERSION _FTS_TRIGGERS_ephemeral_child_sql hermes_statec *eZdZdZdejddfdZdejdefdZe dejde fdZ e de e defd Zdejde fd Zdejdefd Zdejddfd Ze d ddejdeddfdZe d ddejdeddfdZdejde de efdZe de dee ee e fffdZdejddfdZdejddfdZdejddfdZdZdejddfdZdS)SessionSchemaMixinu>See module docstring — mixin for SessionDB (Schema cluster).cursorreturnNc |d}n#tj$rYdSwxYw|D]}t |tjr|dn|d}t |tjr|dn|d}|||}|d||fdS)zEMove inline prompt snapshots into the shared content-addressed table.zFSELECT id, system_prompt FROM sessions WHERE system_prompt IS NOT NULLNidr system_promptzMUPDATE sessions SET system_prompt_hash = ?, system_prompt = NULL WHERE id = ?)executefetchallsqlite3OperationalError isinstanceRow_store_system_prompt)selfrrowsrow session_idprompt prompt_hashs 9/home/thesage/.hermes/hermes-agent/hermes_state_schema.py_dedupe_legacy_system_promptsz0SessionSchemaMixin._dedupe_legacy_system_prompts's >>2hjj D'    FF   C&0gk&B&BNTAJ-7W[-I-IUS))sSTvF33FFCCK NNj)       s '*==c |d|ddS#tj$r6}||s||Yd}~dSd}~wwxYw)Nz:CREATE VIRTUAL TABLE temp._hermes_fts5_probe USING fts5(x)z"DROP TABLE temp._hermes_fts5_probeTF)rrr_is_fts5_unavailable_error_warn_fts5_unavailable)r!rexcs r'_sqlite_supports_fts5z(SessionSchemaMixin._sqlite_supports_fts5<s  NNW X X X NN? @ @ @4'   22377   ' ' , , ,55555  s*.A3+A..A3cddtD}|d|dt}t t |t js|dn|dS)N,c3K|]}dVdS?N.0_s r' z8SessionSchemaMixin._fts_trigger_count..Is";;;;;;;;zGSELECT COUNT(*) FROM sqlite_master WHERE type = 'trigger' AND name IN ()r)joinrrfetchoneintrrr)r placeholdersr#s r'_fts_trigger_countz%SessionSchemaMixin._fts_trigger_countGsxx;;];;;;; nn C3? C C C    (** C!=!=I3q663q6JJJr8sqlc|sdSd|}d|vrdSd|vS)z?True when trigger SQL is missing AFTER UPDATE OF (still broad).F zAFTER UPDATE OF zAFTER UPDATE ON )r:splitupper)r?compacts r'#_fts_update_trigger_needs_narrowingz6SessionSchemaMixin._fts_update_trigger_needs_narrowingRsQ 5((399;;''--//  ( (5!W,,r8c||}d}|st|dr|dz }dd|D}|d|d|}g}|D]}t |t js|dn|d }t |t js|d n|d } || r| ||sdS|D]}|d ||r9| |d t| |dtn| |d t| |dtd|vr ||n>#t $r1||t$dwxYw||s/||t$dt$dt/|t/|S)aReplace broad AFTER UPDATE FTS triggers with AFTER UPDATE OF variants. ``CREATE TRIGGER IF NOT EXISTS`` will not replace an existing broad trigger, so installs that already created ``AFTER UPDATE ON messages`` would keep firing on every messages row touch (status/compaction writes included). Inspect ``sqlite_master``, drop any still-broad UPDATE triggers, and re-apply the current DDL constants. No FTS rebuild: content correctness was already gated by WHEN clauses on modern installs; OF only skips unnecessary trigger evaluation. Returns the number of triggers dropped (0 when already converged). )messages_fts_updatemessages_fts_trigram_update_ensure_fts_cjk_schemamessages_fts_cjk_updatez, c3K|]}dVdSr1r3r4s r'r7zHSessionSchemaMixin._migrate_broad_fts_update_triggers..ws" ; ; ; ; ; ; ; ;r8zHSELECT name, sql FROM sqlite_master WHERE type = 'trigger' AND name IN (r9rnamerr?zDROP TRIGGER IF EXISTS messages_ftsmessages_fts_trigramrKz2CJK FTS re-ensure after UPDATE OF migration failedzeCJK FTS UPDATE trigger missing or still broad after UPDATE OF migration; marked stale and unavailablezPMigrated %d broad FTS UPDATE trigger(s) to AFTER UPDATE OF (no rebuild required))_db_has_legacy_inline_ftshasattrr:rrrrrrEappend_ensure_fts_schemar r rr rI Exception)_quarantine_cjk_after_update_of_migrationlogger exception_cjk_update_trigger_is_narrowedwarninginfolen) r!r legacy_layout update_namesr=r"to_dropr#rMr?s r'"_migrate_broad_fts_update_triggersz5SessionSchemaMixin._migrate_broad_fts_update_triggers_s"66v>>   9/G!H!H 9 8 8Lyy ; ;l ; ; ;;; ~~ C3? C C C    (**  % %C!+C!=!=N3q663v;D *3 < <L#a&&#e*C77<< %t$$$ 1 = =D NN;T;; < < < <    # #FNN K K K  # #.0F      # #FNG D D D  # #.   )G33//7777 BB6JJJ$$L  ;;FCCBB6JJJNNL   $ LL   7||s F..;G)c|dd}|sdSt|tjs|dn|d}|| S)z>True when messages_fts_cjk_update exists with AFTER UPDATE OF.zASELECT sql FROM sqlite_master WHERE type = 'trigger' AND name = ?rJFrr?)rr;rrrrE)r!rr#r?s r'rXz2SessionSchemaMixin._cjk_update_trigger_is_narrowedssnn 2 (   (**  5&sGK88Hc!ffc%j;;C@@@@r8c0d|_ |td|n,#t$rtddYnwxYw |dd S#t$r tddYd SwxYw) aFail-closed after dropping CJK UPDATE during OF migration. Clears availability, persists ``fts_cjk_stale``, and drops any residual broad/partial CJK UPDATE trigger so a later open cannot ``CREATE TRIGGER IF NOT EXISTS`` a gap without rebuild. F1rz*Could not persist CJK FTS stale breadcrumbT)exc_infoz.DROP TRIGGER IF EXISTS messages_fts_cjk_updatez;Could not drop residual CJK UPDATE trigger after quarantineN)_fts_cjk_availableset_metarrTrVdebugr)r!rs r'rUz._col')#t}==L3s883t9 Lr8c*g|]}|ddS)rrMr3r5rrs r' z?SessionSchemaMixin._heal_gateway_routing_pk..s7    DAv     r8c36K|]}|dd|VdS)rrNr3rs r'r7z>SessionSchemaMixin._heal_gateway_routing_pk..s655qDDAt$4$45555555r8c|ddS)Nrrr3)rrs r'z=SessionSchemaMixin._heal_gateway_routing_pk..sdd1a..r8)keyscope session_keyz]gateway_routing has legacy primary key %r; rebuilding with composite (scope, session_key) keyz?ALTER TABLE gateway_routing RENAME TO gateway_routing_legacy_pkzCREATE TABLE gateway_routing ( scope TEXT NOT NULL DEFAULT '', session_key TEXT NOT NULL, entry_json TEXT NOT NULL, updated_at REAL NOT NULL, PRIMARY KEY (scope, session_key) )zINSERT OR REPLACE INTO gateway_routing (scope, session_key, entry_json, updated_at) SELECT COALESCE(scope, ''), session_key, entry_json, updated_at FROM gateway_routing_legacy_pk ORDER BY updated_at ASCz$DROP TABLE gateway_routing_legacy_pk)rrrrsortedrVrZ)r!rr"pk_colsrs @r'_heal_gateway_routing_pkz+SessionSchemaMixin._heal_gateway_routing_pk{sr. >>6hjj D'    FF   F M M M    5555D555....    w . . . F  1     M          E   =>>>>>s '+>>c |d}n#tj$rYdSwxYw|sdSdfd|D}d|vrdStdt ||d |d|d |d |d |d |d n7#tj$r%}td|Yd}~nd}~wwxYw|ddS#|dwxYw)u:Rebuild ``session_model_usage`` when its PRIMARY KEY lacks ``task``. Installs whose ``state.db`` reached ``schema_version >= 22`` before the ``task`` dimension was added carry a 5-column PRIMARY KEY ``(session_id, model, billing_provider, billing_base_url, billing_mode)``. ``_reconcile_columns()`` ADDs the ``task`` column as a bare nullable, but SQLite cannot ALTER a primary key, so the shipped composite 6-column key never lands. The version-gated v22 rebuild is unreachable on those installs (``current_version < 22`` is already false), so every upsert in ``_record_model_usage()`` fails with "ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint" — aborting the enclosing write transaction and silently zeroing all token *and* cost accounting (#73823). Idempotent; runs unconditionally on every open, same pattern as :meth:`_heal_gateway_routing_pk` above. On healthy databases the PRAGMA check short-circuits and this is a no-op. z(PRAGMA table_info("session_model_usage")NcZt|ttfr||n||Srrrs r'rz=SessionSchemaMixin._heal_session_model_usage_pk.._colrr8cDh|]}|dd|ddS)rrrrMr3rs r' zBSessionSchemaMixin._heal_session_model_usage_pk..sJ   #$Q40@0@ DAv     r8taskzdsession_model_usage has legacy primary key %r (missing task); rebuilding with composite 6-column keyzPRAGMA foreign_keys=OFFzGALTER TABLE session_model_usage RENAME TO session_model_usage_legacy_pka_CREATE TABLE session_model_usage ( session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE, model TEXT NOT NULL, billing_provider TEXT NOT NULL DEFAULT '', billing_base_url TEXT NOT NULL DEFAULT '', billing_mode TEXT NOT NULL DEFAULT '', task TEXT NOT NULL DEFAULT '', api_call_count INTEGER NOT NULL DEFAULT 0, input_tokens INTEGER NOT NULL DEFAULT 0, output_tokens INTEGER NOT NULL DEFAULT 0, cache_read_tokens INTEGER NOT NULL DEFAULT 0, cache_write_tokens INTEGER NOT NULL DEFAULT 0, reasoning_tokens INTEGER NOT NULL DEFAULT 0, estimated_cost_usd REAL NOT NULL DEFAULT 0, actual_cost_usd REAL NOT NULL DEFAULT 0, cost_status TEXT, cost_source TEXT, first_seen REAL, last_seen REAL, PRIMARY KEY (session_id, model, billing_provider, billing_base_url, billing_mode, task) )aINSERT OR IGNORE INTO session_model_usage ( session_id, model, billing_provider, billing_base_url, billing_mode, task, api_call_count, input_tokens, output_tokens, cache_read_tokens, cache_write_tokens, reasoning_tokens, estimated_cost_usd, actual_cost_usd, cost_status, cost_source, first_seen, last_seen ) SELECT session_id, model, COALESCE(billing_provider, ''), COALESCE(billing_base_url, ''), COALESCE(billing_mode, ''), COALESCE(task, ''), api_call_count, input_tokens, output_tokens, cache_read_tokens, cache_write_tokens, reasoning_tokens, estimated_cost_usd, actual_cost_usd, cost_status, cost_source, first_seen, last_seen FROM session_model_usage_legacy_pkz(DROP TABLE session_model_usage_legacy_pk]CREATE INDEX IF NOT EXISTS idx_session_model_usage_session ON session_model_usage(session_id)VCREATE INDEX IF NOT EXISTS idx_session_model_usage_model ON session_model_usage(model)z'session_model_usage PK heal skipped: %szPRAGMA foreign_keys=ON)rrrrrVrZrrg)r!rr"rr,rs @r'_heal_session_model_usage_pkz/SessionSchemaMixin._heal_session_model_usage_pks& >>:hjj D'    FF   F M M M    (,    W   F  5 7OO    0111? 5 NN:    NN   6 NN9   & NNE F F F NN5    NN0    ' I I I LLBC H H H H H H H H I NN3 4 4 4 4 4FNN3 4 4 4 4s='+>>A>DE'E (EE'E  E''E>ct|j}|t|||||| |dn7#tj $r%}t d|Yd}~nd}~wwxYw|t |dn#tj $rYnwxYw| |}d}|s|||d|}||dt fnvt#|tjr|dn|d }|d krct d krX|rT||d }|d ur5||d t*r|d n d }n|d }nd }|dkr t dkr |dkrQ |dt-d|dn#tj $rYnwxYw|dkrI ||n2#t0$r%}t d|Yd}~nd}~wwxYw|dkr, |dn#tj $rYnwxYw|dkr |dd }|s~|d|d|d|d|d|dn7#tj $r%}t d |Yd}~nd}~wwxYw|dkr/|r-||r|d!d"|#|d$kr|||r||s{|d%T||s?||s*|d&t=t>|#|t kr |r|r|d't fd(} || n#tj $r~ |d)t!d*|j"|| n/#tj#$rt$d+YnwxYwYntj $rYnwxYw|ro||r|%|tMtNk} ||d,tP|_)|j)r<||d tT} | |_+| r|,|| -n|%|tMtNk} ||d,tZ|_)|j)rQ||d t*} | |_+| r|.|| -|/|ta|d.d r|1||j2dS)/aCreate tables and FTS if they don't exist, reconcile columns. Schema management follows the declarative reconciliation pattern (Beets, sqlite-utils): SCHEMA_SQL is the single source of truth. On existing databases, _reconcile_columns() diffs live columns against SCHEMA_SQL and ADDs any missing ones. This eliminates the version-gated migration chain for column additions, making it impossible for reordered or inserted migrations to skip columns. The schema_version table is retained for future data migrations (transforming existing rows) which cannot be handled declaratively. zCREATE INDEX IF NOT EXISTS idx_messages_platform_msg_id ON messages(session_id, platform_message_id) WHERE platform_message_id IS NOT NULLz/idx_messages_platform_msg_id create skipped: %sNz3UPDATE messages SET active = 1 WHERE active IS NULLTz*SELECT version FROM schema_version LIMIT 1z/INSERT INTO schema_version (version) VALUES (?)versionr rOFzkINSERT INTO messages_fts_trigram(rowid, content) SELECT id, content FROM messages WHERE content IS NOT NULL zUPDATE sessions SET model_config = json_set(COALESCE(model_config, '{}'), '$._delegate_from', parent_session_id) WHERE parent_session_id IS NOT NULL AND json_extract(COALESCE(model_config, '{}'), '$._delegate_from') IS NULL AND sessionsaUPDATE sessions SET model_config = json_set(COALESCE(model_config, '{}'), '$._delegate_from', '__orphaned__') WHERE parent_session_id IS NULL AND json_extract(COALESCE(model_config, '{}'), '$._delegate_from') IS NULL AND json_extract(COALESCE(model_config, '{}'), '$._branched_from') IS NULL AND title IS NULL AND message_count <= 25 AND EXISTS (SELECT 1 FROM messages m WHERE m.session_id = sessions.id AND m.role = 'tool') AND NOT EXISTS (SELECT 1 FROM sessions ch WHERE ch.parent_session_id = sessions.id)z)v18 gateway metadata backfill skipped: %saINSERT OR IGNORE INTO session_model_usage ( session_id, model, billing_provider, billing_base_url, billing_mode, api_call_count, input_tokens, output_tokens, cache_read_tokens, cache_write_tokens, reasoning_tokens, estimated_cost_usd, actual_cost_usd, cost_status, cost_source, first_seen, last_seen ) SELECT id, COALESCE(model, 'unknown'), COALESCE(billing_provider, ''), COALESCE(billing_base_url, ''), COALESCE(billing_mode, ''), COALESCE(api_call_count, 0), COALESCE(input_tokens, 0), COALESCE(output_tokens, 0), COALESCE(cache_read_tokens, 0), COALESCE(cache_write_tokens, 0), COALESCE(reasoning_tokens, 0), COALESCE(estimated_cost_usd, 0), COALESCE(actual_cost_usd, 0), cost_status, cost_source, started_at, COALESCE(ended_at, started_at) FROM sessions WHERE COALESCE(input_tokens, 0) + COALESCE(output_tokens, 0) + COALESCE(cache_read_tokens, 0) + COALESCE(cache_write_tokens, 0) + COALESCE(reasoning_tokens, 0) > 0z\SELECT COUNT(*) FROM pragma_table_info('session_model_usage') WHERE name = 'task' AND pk > 0zAALTER TABLE session_model_usage RENAME TO session_model_usage_v21aCREATE TABLE session_model_usage ( session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE, model TEXT NOT NULL, billing_provider TEXT NOT NULL DEFAULT '', billing_base_url TEXT NOT NULL DEFAULT '', billing_mode TEXT NOT NULL DEFAULT '', task TEXT NOT NULL DEFAULT '', api_call_count INTEGER NOT NULL DEFAULT 0, input_tokens INTEGER NOT NULL DEFAULT 0, output_tokens INTEGER NOT NULL DEFAULT 0, cache_read_tokens INTEGER NOT NULL DEFAULT 0, cache_write_tokens INTEGER NOT NULL DEFAULT 0, reasoning_tokens INTEGER NOT NULL DEFAULT 0, estimated_cost_usd REAL NOT NULL DEFAULT 0, actual_cost_usd REAL NOT NULL DEFAULT 0, cost_status TEXT, cost_source TEXT, first_seen REAL, last_seen REAL, PRIMARY KEY (session_id, model, billing_provider, billing_base_url, billing_mode, task) )aINSERT INTO session_model_usage ( session_id, model, billing_provider, billing_base_url, billing_mode, task, api_call_count, input_tokens, output_tokens, cache_read_tokens, cache_write_tokens, reasoning_tokens, estimated_cost_usd, actual_cost_usd, cost_status, cost_source, first_seen, last_seen ) SELECT session_id, model, billing_provider, billing_base_url, billing_mode, '', api_call_count, input_tokens, output_tokens, cache_read_tokens, cache_write_tokens, reasoning_tokens, estimated_cost_usd, actual_cost_usd, cost_status, cost_source, first_seen, last_seen FROM session_model_usage_v21z"DROP TABLE session_model_usage_v21rrz+v22 session_model_usage rebuild skipped: %sfts_optimize_availablerbrczESELECT 1 FROM state_meta WHERE key = 'fts_rebuild_high_water' LIMIT 1fts_storage_versionz%UPDATE schema_version SET version = ?zfCREATE UNIQUE INDEX IF NOT EXISTS idx_sessions_title_unique ON sessions(title) WHERE title IS NOT NULLagUPDATE sessions AS older SET title = NULL WHERE title IS NOT NULL AND EXISTS ( SELECT 1 FROM sessions AS newer WHERE newer.title = older.title AND newer.rowid > older.rowid )zFCleared %d duplicate session title(s) while restoring the unique indexzICould not repair duplicate session titles; unique title index not createdrNrh _fts_enabled)3_connrrr rrrrrrrVrgrr-_drop_fts_triggersr;rrrrvrSr r-_backfill_gateway_metadata_from_sessions_jsonrTrPrfr(_has_fts_trash'_fts_external_index_empty_with_messagesrtr IntegrityErrorrYrowcountErrorrWr>r[rr rr _trigram_availablerorrmrIgetattrr_commit) r!rr,fts5_availablefts_migrations_completer#current_version_fts_trigram_exists legacy_pktitle_index_sqltriggers_need_repairtrigram_enableds r' _init_schemazSessionSchemaMixin._init_schema<s> ""$$Z((( ''' %%f--- ))&111  Q NN8     ' Q Q Q LLJC P P P P P P P P Q /000  NNE    '    D 33F;;"& ,  # #F + + + CDDDoo ; NNA!     1;3 0L0LXc)nnRUVWRXO ##"(<(<"4*.*?*? 6++'+e3322"$:O<#NN!] 7<33,427/.3+##(;(;##NNB 4J?? BBNN T    /D## SFFvNNNN SSS LL!LcRRRRRRRR S ##!NNH>/D##7U &9!!hjj!$I%0'jkkk$. ?'KLLLA</UUULL!NPSTTTTTTTTU##:"Pd&D&DV&L&LPMM":CMOOO## 226::: 66v>> NNC(**%++F33 % DDVLL % )3/B+C+CF .00+1"1;#% 9   NN? + + + +%       \O////=     5  '    D  9 @--f55' 8++F33c-6H6HH%%)$;$;NN%%!$&*&=&= 68N''O/>D++88"O9 ++F33c-6H6HH%%)$;$;NG%%!$ 8&*&=&= 6''O/>D++11",;2 //777t^U33 @77??? s4B B>B99B>C22DD*:I%%I76I7J K!KKK&&K87K8B-N00O$?OO$7T V*A U('V*()VV*VV*V*)V*c,tdz dz }|sdSt|dd5}tj|}dddn #1swxYwYt |t sdS|D]~\}}t| dst |t s>| d}|sV| d }| d | d p|t |t r|pi d nd| d t |t r|pi dnd| dt |t rtj |nd| ds| drdndt|fdS)uvOne-time v18 backfill of gateway metadata from sessions.json. Existing gateway sessions predate the display_name / origin_json / expiry_finalized columns; copy what sessions.json knows so consumers can switch to state.db without losing pre-migration sessions. Only fills NULL columns — never overwrites data written by newer code. rz sessions.jsonNrzutf-8)encodingr6r$originaiUPDATE sessions SET session_key = COALESCE(session_key, ?), chat_id = COALESCE(chat_id, ?), chat_type = COALESCE(chat_type, ?), thread_id = COALESCE(thread_id, ?), display_name = COALESCE(display_name, ?), origin_json = COALESCE(origin_json, ?), expiry_finalized = CASE WHEN COALESCE(expiry_finalized, 0) = 0 AND ? = 1 THEN 1 ELSE expiry_finalized END WHERE id = ?rchat_id chat_type thread_id display_nameexpiry_finalizedmemory_flushedrr) rexistsopenjsonloadrdictrrt startswithgetrdumps) r!r sessions_filefdatarentryr$rs r'rz@SessionSchemaMixin._backfill_gateway_metadata_from_sessions_jsonsB())J6H ##%%  F -w 7 7 7 19Q<rrtrEr_rXrUrmrorvrrrrrrrr3r8r'rr$sHHGNt* GN t    K7>KcKKK\K -# -4 - - -\ -SSCSSSSj Agn A A A A An 4!%          \ ,!%          \ Bw~38TX>"(#($sDcN7J2K(((\(T**D****XG?w~G?$G?G?G?G?Rv57>v5dv5v5v5v5pJJJX/n/ //////r8r)rloggingrrtypingrrhermes_constantsrhermes_state_commonrrrr r r r r rrr getLoggerrVrr3r8r'rs( !!!!!!!!,,,,,,                            > * *SSSSSSSSSSr8