Purpose of the change

Fixes #1544, #1546, and #1549 by construction: the segment store's per-tenant partitioned layout is replaced with shared tables and an incarnation-carrying tenant registry, on every dialect. Design record: design/segment_store_shared_tables.md.

Also fixes #1557, #1558, and #1559: #1462's UTC-normalization fixes are folded in for every SQL store (segment, episode, cluster), so no store ships with the SQLite timestamp corruption.

Why an overhaul instead of the earlier targeted fixes

This PR began as three targeted repairs to the partitioned layout (detach-before-drop for #1544, a management lock for the create/delete deadlock, child-table DML for #1546), each verified on its own. Further investigation showed the layout itself was the root cause and could not be fully repaired:

• PostgreSQL's FK integrity triggers still addressed the partitioned parents internally, so writes and deletes kept a per-backend lock spike proportional to partition count even after every client statement was fixed (10 -> 166 -> 10 relation locks across the trigger's generic-plan attempt).
• Writer-vs-lifecycle-DDL deadlocks through the shared parents survived every lock discipline: 41-83 deadlocks per 20-second churn on upstream and on every intermediate fix, in two cycle shapes captured from the server log, one of them a lock-upgrade cycle inherent to dropping a child that carries a cloned FK.
• Scaling requirements ruled out every per-tenant-table variant: tenant creation must stay cheap at 1e5-1e6 tenants (a per-tenant table costs ~5 catalog relations plus disk files), with 1e4-1e7 rows per tenant.

Description

• Shared tables everywhere. The ORM models are the physical schema on every dialect. PostgreSQL partitioning, per-tenant DDL, detach machinery, the store-wide management lock, and the per-partition entity cache are removed. With no partitions there is no generic-plan lock fan-out, and with no lifecycle DDL there is nothing for the deadlock class to live in: the churn smoke that measured 41-83 deadlocks/20s on every partitioned build measures zero, with ~60x more write throughput and ~200x more lifecycle throughput.
• Incarnation-scoped tenant identity. segment_store_pt is the tenant registry: logical key (primary key) plus an incarnation, a random unique-constrained UUID. Data rows carry the incarnation alone, so a data query cannot be built without resolving the registry, and referencing the wrong tenant is structurally impossible. A deleted-and-recreated tenant never sees its predecessor's rows, even mid-purge. A colliding mint is rejected rather than left to probability: the unique constraint rejects a collision with a live incarnation, and the mint transaction re-checks the purge queue after its insert so an incarnation whose garbage is still awaiting purge is re-minted (race-free with the existing tables: the check runs after the registry insert, and no new queue entry for the minted value can appear before commit, since the only registry row carrying it is uncommitted). Any integrity rejection with no committed row under the key is likewise retried with a fresh incarnation, up to _MAX_MINT_ATTEMPTS; a persistent cause surfaces as SegmentStoreAttemptsExhaustedError with the database error chained. Rejections are warning-logged at the detection site, since a genuine collision is astronomically unlikely and the log is the signal for broken randomness or a persistent database error even when the re-mint heals it.
• O(1) deletion plus a purge queue. create_partition is a row insert (microseconds, no DDL). delete_partition takes FOR UPDATE on the registry row (waiting out writers' FOR SHARE pins), enqueues the incarnation on segment_store_gc (logical key kept for forensics), and deletes the row: O(1) at any tenant size, the pool-model contract of "unreachable immediately, erased asynchronously".
• Purge as an ABC capability. purge_deleted_partitions() -> bool is part of the SegmentStore interface. The caller's whole protocol is "call until False"; sizing is not a call argument, because callers cannot know engine-appropriate bounds. Deployments set them once at construction: purge_max_segments (default 10000) and purge_max_partitions (default 100, its own bound because retiring an empty entry costs ~0.95 ms, ~200x a segment row, and empty partitions are cheap to mass-create-and-delete). Each call is one transaction (measured ~147k-312k rows/s), committing its progress or nothing. Links follow by ON DELETE CASCADE, 50-68% faster than manual link deletion at one and four links per segment; cascade deletion saturates ~3M link rows/s (380k segments/s at 1 link/segment, 46k at 64), so heavily linked partitions purge in sub-second calls. Link fan-out is set by the deriver, which the store cannot reject after derivation. Before retiring an entry the call also reclaims any link rows that escaped referential integrity, in batches drawn from the same budget and logged as a warning; a link row deletes ~3x cheaper than a segment row (1.0 vs 3.3 us/row, batched), so the shared budget bounds the call without assuming a ratio. Entries are claimed one at a time, oldest first (enqueue stamped by the database clock, indexed), with FOR UPDATE SKIP LOCKED, so a call neither materializes nor locks the rest of the backlog and concurrent purgers from any process share the queue; only the claiming call touches a dead incarnation's rows, so reclamation is deadlock-free by construction. On SQLite, which drops locking clauses, purgers serialize on the write lock at the DELETE: two may claim the same entry, and the second finds no rows and re-retires it. The store never schedules purging; implementations whose deletes reclaim physically return False. The delete path does not purge: an inline drain was tried, first of the global queue and then scoped to the deleted key, and removed, since prompt physical erasure is not a promise the store can keep on every dialect (on SQLite any writer past the busy timeout fails) and the sweeper reclaims within its interval; a deployment must run the sweeper, and the ABC says so.
• Fencing (Segment store partition handles are not fenced to a partition incarnation: stale handles silently operate on a recreated partition #1549). Writes pin the registry row under FOR SHARE with an incarnation predicate. Reads add the same predicate to their data statement as an EXISTS, so one statement checks liveness and reads; a read that finds no rows issues the registry check on its own, to tell an empty partition from a stale handle, and a windowed read ends with one registry read because its statements take separate snapshots. On SQLite the driver defers BEGIN until the first write, so a SELECT-only fence checks nothing; BEGIN IMMEDIATE would mean taking over transaction management of the caller's shared engine, so the fence is a self-checking registry-row UPDATE (same write lock, scoped to the transaction, match count as the staleness check), and deletion opens its transaction the same way. A handle that outlives its partition or its incarnation raises SegmentStorePartitionHandleStaleError on every dialect; SQLite previously had no stale-handle detection at all. Measured cost: none on reads that find rows, one registry round trip on reads that find none.
• Foreign keys. The segment table's FK to the registry is removed on purpose (registry and data rows decouple so deletion is O(1)); the link table's FK to the segment table and its cascade remain, enforced by a single-row indexed check with no partition fan-out. On SQLite, enforcement is per-connection state registered at ENGINE creation (enable_sqlite_foreign_keys, applied by the DatabaseManager to its engines): a listener added later misses connections the shared engine already pooled, a pre-existing bug surfaced in review round 4. The store states the requirement on its engine parameter and does not verify it, which is the reference practice for SQLite foreign keys; an engine without the pragma leaves link rows that outlive their segments until the partition is dropped, when the purge reclaims them with a warning. The SQLite vector stores carry the same latent pattern; filed as SQLite per-connection state (foreign keys, sqlite-vec extension) is registered per-store on caller-supplied engines #1568.
• Folded in: Fix timezone-aware datetime roundtrip on SQLite #1462's SQL-store fixes (fixes [Bug]: Segment store corrupts non-UTC timezone-aware timestamps on SQLite (write path + filter bounds) #1557, [Bug]: Episode store corrupts non-UTC timezone-aware created_at on SQLite (write path + time-range bounds) #1558, [Bug]: Cluster store corrupts non-UTC timezone-aware timestamps on SQLite (last_ts, pending created_at) #1559). App-supplied timezone-aware datetimes are normalized to UTC before persisting in every SQL store: segment timestamps, episode created_at and its start_time/end_time bounds, cluster last_ts and pending created_at. Without this, SQLite (whose DateTime(timezone=True) discards tzinfo) read non-UTC values back shifted by their offset. Datetime filter values are normalized to UTC-aware instants at Comparison/In construction, the filter language's contract stated on FilterExpr (a value denotes an instant; naive means UTC), so every consumer receives instants and compilers only choose a representation; the SQL column leaf binds values as-is, and remaining per-backend normalizations are idempotent defenses, removable separately. Two consumers change by design: the Neo4j compiler's datetime.timestamp() read a naive value in the server's local zone and now receives instants, with its ISO-string branch parsing under the same rule (pinned under a non-UTC process zone); the in-memory short-term evaluator tags a naive stored metadata datetime as UTC at comparison time, since stored user data is not rewritten. The episode store's bounds spell the convention inline as two explicit steps, ensure_tz_aware(...).astimezone(UTC), kept unwrapped so the naive-means-UTC decision stays visible at each site. PostgreSQL is unaffected. On SQLite, rows the episode and cluster stores previously wrote from non-UTC-offset datetimes hold the local wall clock as if it were UTC and read back shifted before and after this change alike; the offset was never stored, so only new writes are correct, and a range query spanning the upgrade mixes the two conventions.

Measurements

Layout comparison and scaled runs are in the design doc. Headlines (pgvector:pg16, one harness for all arms): tenant creation 0.006 ms vs 6-12 ms of per-tenant DDL; ingest and read parity or better at 40x2k and 1M-pair scales including a 500k-row tenant; O(1) delete at any size; churn 0 deadlocks vs 41-83.

Store-API ABAB against upstream main (3 interleaved rounds, one PG instance, medians, at the revision before the read fence was folded into the data statement): ingest +42% (11.1k vs 7.8k pairs/s), delete_segments +20%, event/derivative lookups 10-17% faster, windowed context expansion 8% faster (5.9 vs 6.4 ms), lifecycle create+open+delete 4.4x faster (2.9 vs 12.8 ms/cycle); seed context reads +0.19 ms (1.30 vs 1.11 ms) from the fence's separate registry round trip, since removed. Read-path ABAB of that fold (5 interleaved rounds, medians): seed context reads 1.17 vs 1.42 ms, event lookups 1.04 vs 1.61 ms, derivative lookups 1.05 vs 1.33 ms, windowed context expansion 8.74 vs 9.67 ms (8 rounds x 600 reps, paired median -0.91 ms); reads that find nothing unchanged. Server-side (EXPLAIN ANALYZE, best of 30) the EXISTS conjunct plans as a one-time InitPlan costing ~3 us per statement, so the client-side gain is the round trip. Purge drains ~216k rows/s; the batched-delete pattern was benchmarked on a 100k-row incarnation with the cascade in place: uuid IN (SELECT ... LIMIT n) 252k rows/s vs a PostgreSQL-only ctid batch at 238k and an unbatched single-DELETE ceiling at 295k, so the portable pattern is also the fastest batched one. Claim and mint-check statements measure 215/156 us.

Testing

• The behavioral suite runs unchanged against the new layout on both dialects: segment store file 82 in the -m "not integration" lane, 86 in the -m integration lane; full server suite 1926 passed, 3 skipped. Concurrency coverage runs on both dialects wherever the property exists on both: lifecycle churn, racing deletions (single-enqueue assertion), overlapping segment deletes, the mint-vs-deletion collision race, and O(1) deletion via recorded SQL.
• Purge: test_purge_reclaims_oldest_garbage_first (FIFO, explicit stamps), test_purge_queue_stamps_enqueue_time_from_database_clock, test_purge_bounds_entries_processed_per_call, test_purge_batches_links_that_escaped_integrity (orphans staged through a second SQLite engine without the pragma; fails against the unbounded form), test_purge_skips_entries_claimed_by_concurrent_purger (fails with SKIP LOCKED ablated), test_concurrent_purges_reclaim_everything (both dialects), test_purge_claims_queue_entries_incrementally (fails against the claim-all form), test_purge_reclaims_only_dead_incarnations, test_purge_bound_comes_from_params, test_default_purge_bound_comes_from_params.
• Incarnations: test_incarnation_with_garbage_left_is_never_reused (queue re-check) and test_incarnation_colliding_with_live_partition_is_never_reused (unique constraint) force a collision on both creation paths and both dialects, each failing with its guard ablated; test_mint_detects_collision_with_concurrent_deletion pins the insert-then-check ordering with a staged concurrent deletion (swapping the statements fails only this test); test_persistent_integrity_error_surfaces_with_cause pins the bounded re-mint with the driver error chained, both dialects; test_persistent_mint_failure_raises_instead_of_looping pins the attempt cap on both creation paths.
• Fencing: test_stale_handle_raises_after_delete / ..._after_recreate (both dialects), test_reads_check_liveness_inside_the_data_statement, test_windowed_read_raises_when_partition_dies_between_statements (both dialects; fails without the closing registry read), test_recreated_partition_is_isolated_from_old_rows, test_delete_partition_touches_only_registry_and_queue (recorded SQL), test_write_pin_blocks_partition_delete, the SQLite fence test (a write racing delete-plus-purge can no longer orphan rows; the two SQLite race tests carry started-events so a loaded box cannot pass them vacuously).
• Lock-necessity tests (integration lane, under a second): each locking property is pinned by staging the interleaving it serializes (blocked-ness observed via pg_stat_activity, no grace sleeps). Verified by per-lock ablation on both generations: removing a lock from the new store fails exactly its targeted tests, and the pre-overhaul store fails the churn and concurrent-delete tests with all its locks intact (reproducible DeadlockDetectedError in the two cycle shapes on Episodic search reads lock every segment-store partition once prepared statements go generic (Postgres lock-table exhaustion, 500s under load) #1546). The churn test also caught the _open_or_create_partition race, now a retry loop bounded by _MAX_MINT_ATTEMPTS.
• Timestamps: Fix timezone-aware datetime roundtrip on SQLite #1462's regression tests for the episode and cluster stores are ported; test_timestamp_roundtrips_with_timezone and test_timestamp_filter_compares_instants_not_wall_clocks ([Bug]: Segment store corrupts non-UTC timezone-aware timestamps on SQLite (write path + filter bounds) #1557) are parametrized over non-UTC zones on both dialects; test_older_than_compares_instants_not_wall_clocks covers the semantic history bound; the filter-parser tests pin construction-time normalization; test_iso_string_coerces_as_utc_instant pins the Neo4j string branch under a non-UTC process zone (skipped where time.tzset is unavailable); test_datetime_metadata_filters_tag_naive_stored_values pins the in-memory evaluator. Each fails without its fix.
• Erasure and scheduling: LongTermMemory deletes the collection and the partition and returns; the partition is unreachable at once and its rows are reclaimed by the sweeper within its interval. Its handles are nulled so later use raises (test_event_backend_unusable_after_drop_session_partition), and the wiring test asserts the delete path never purges. The resource manager runs one background purge task per store (bounded calls a short pause apart while the store reports a backlog, one idle call per interval otherwise; failures logged and retried; cancelled on close); the loop is a module-level coroutine so a pending task does not pin the manager (test_purge_task_does_not_pin_the_manager), and get_segment_store after close() raises ResourceManagerClosedError. Both purge bounds are validated at construction; the server uses the defaults, and config plumbing is future work.
• Other review-round fixes, each with a test that fails pre-fix: unloadable codec config commits no registry row (both creation paths); trailing-newline partition keys rejected (re.fullmatch), and the session-id-to-partition-key mapping validates with the store's own validator instead of a drifted copy; the StaticPool guard raises ValueError (test_static_pool_engine_is_rejected); pre-3.35 SQLite runtimes are rejected at construction (test_old_sqlite_runtime_is_rejected); empty-input add_segments short-circuits like delete_segments. Stale-handle handling above the store (API status mapping, cross-replica cache eviction) is filed as Stale segment-store handles need cache eviction and an API status mapping #1571.
• Retained: test_delete_partition_keeps_other_partitions_cascading and test_delete_partition_keeps_foreign_key_enforced now hold trivially, since no DDL can damage the constraint.
• ruff format --check, ruff check, and ty check clean (zero added diagnostics).

Compatibility

No migration from the partitioned layout is provided: the event backend is opt-in and pre-GA, and existing databases recreate their schema (see the design doc). The partition-key contract ([a-z0-9_], max 32 bytes) is unchanged.

Type of change

• Bug fix (non-breaking change which fixes an issue)
• Breaking change (schema layout of the opt-in event backend)

🤖 Generated with Claude Code

https://claude.ai/code/session_01MpFwBnZ6SxdMe3pSuMTCHD

https://claude.ai/code/session_01MbYdqGZsuws6Z2WHYfCCR5