fix(memory): reject a second base-table pair in one AdvancedSQLiteSession database - #4441

Both Codex points were real. Fixed in f68b409.

Identifier casing. This was a regression my patch introduced. SQLite resolves table names case-insensitively, but PRAGMA foreign_key_list reports the CREATE-time spelling, so reopening a file as foosessions after creating it as FooSessions hit the mismatch branch and raised on a configuration that is the same pair. Comparison is case-folded now, and test_structure_tables_accept_equivalent_identifier_casing covers it. That test fails with ValueError against the previous commit.

Split layout. Verification now checks turn_usage as well as message_structure, so a file whose two structure tables were created for different pairs is rejected instead of being accepted by whichever one happens to match. It also runs inside the transaction that creates the layout: _init_structure_tables now opens with BEGIN IMMEDIATE, which takes the database write lock before the first CREATE, and Python's sqlite3 keeps DDL inside the open transaction, so create-and-verify commits as one unit and two processes cannot each create half the layout for a different pair.

Checked by hand on top of the existing test: recased pair accepted with correct history, a file where only turn_usage carries the owner rejected through the turn_usage check, and the original two-pair case still rejected with the first session's history intact.

.agents/skills/code-change-verification/scripts/run.sh passes: format, lint, typecheck and the full test run.

Thanks, that framing helped. Reset around the single contract in 6517db9.

One contract. _claim_structure_tables is now the only validation and both constructor paths run it:

• create_tables=True creates and claims inside one BEGIN IMMEDIATE transaction, so the layout is created, checked and committed as a unit.
• create_tables=False accepts only an already-existing complete layout owned by the configured pair. An incomplete or unclaimed layout is rejected, which closes the case you flagged: a no-create session could previously open a file before any pair owned it and then have another pair claim the structure tables underneath it.

Both owner-bearing tables, message_structure and turn_usage, must be present and agree. branch_reservations and session_clear_generations carry no foreign keys, but once one pair owns the file they cannot be shared across pairs either.

Identifier equality. Now delegated to SQLite via SELECT ? = ? COLLATE NOCASE rather than Python casefold(). You were right that this mattered: casefold() equates ßsessions and sssessions, which SQLite keeps as two distinct tables, so the second pair got through and the corruption came back.

Tests, all three cases:

• test_no_create_session_rejects_a_database_without_an_owner
• test_structure_tables_accept_equivalent_identifier_casing for ASCII recasing, and test_structure_tables_reject_distinct_non_ascii_identifiers for the non-ASCII pair, which asserts the casefold() collision up front so the reason the test exists is visible
• test_concurrent_structure_table_claims_leave_one_coherent_owner, two processes claiming a fresh file with different pairs, asserting exactly one claim and that both owner-bearing tables end up naming that winner

Four of the five fail against main. The fifth is the ASCII recasing test, which guards against over-rejection rather than against main.

One note on how that concurrency test is written, since it looks over-specified otherwise. It pre-creates the file with WAL enabled before the race. Switching journal mode needs an exclusive lock, so two processes opening a brand-new file collide in SQLiteSession.__init__ at sqlite_session.py:107 (PRAGMA journal_mode=WAL) before either reaches the structure tables. On main, with the same base-table pair and none of this validation involved, that reproduces 6 out of 6 times with OperationalError: database is locked. That is pre-existing and outside this change, so I settled WAL up front to keep the test about the claim. Happy to open a separate issue for it if it is worth tracking.

Behavior change worth confirming: AdvancedSQLiteSession(session_id=..., create_tables=False) against a database with no structure tables now raises at construction instead of at the first add_items(). On main that configuration already fails, with OperationalError: no such table: message_structure, and every usage in docs/sessions/ and examples/memory/ passes create_tables=True, so I do not think anything working breaks. Say the word if you would rather it stayed lazy.

.agents/skills/code-change-verification/scripts/run.sh passes.

Fair point, and my change is what made that state reachable, so it should clean up after itself. Fixed in the latest commit.

I took the rollback option rather than preflighting before super().__init__(). Preflighting needs the effective sessions_table/messages_table before the base class has resolved them, which would mean duplicating its default names here as a second source of truth. Instead __init__ snapshots sqlite_master before the base schema is created, and a rejected claim drops only the names that snapshot did not contain. A pair that already owned tables in the file keeps them.

test_rejected_construction_leaves_no_schema_behind asserts the file's schema is byte-for-byte the same set before and after a rejected construction, and that the owner's history still reads correctly. It fails against the previous commit.

.agents/skills/code-change-verification/scripts/run.sh passes.

Resolved by #4454, which includes you as a co-author.