Define backend write-concurrency contracts and staged index runs

[marco0560]

Problem

Issue #55 covers parallelizing analyzer work while keeping backend writes serial. That is the correct first boundary, but it leaves a second performance question open: whether Codira can support concurrent backend writes after analysis results are available.

The current backend write contract is centered on one mutable IndexWriteSession for one repository index pass. That shape works for in-process SQLite, DuckDB, and the in-memory contract backend, but it does not expose a safe or portable concurrency model. It also does not map cleanly to future server-backed storage such as PostgreSQL, MySQL, or MariaDB, where independent connections and transactions can make parallel writes practical.

This issue proposes a backend concurrency contract that can support both:

• current in-process/file-backed backends, which may decline concurrent writes
• future independent-process/server DB backends, which may support concurrent writer transactions

Correctness and deterministic output remain more important than throughput.

Contract updates

Add a write-concurrency capability contract

Add an explicit backend concurrency declaration. The goal is to make concurrency a backend capability, not a hidden property of a storage engine.

Suggested dataclass:

@dataclass(frozen=True)
class BackendWriteConcurrencyDeclaration:
    backend_name: str
    backend_version: str
    supports_concurrent_file_writes: bool
    supports_staged_index_runs: bool
    supports_atomic_run_activation: bool
    supports_concurrent_readers_during_index: bool
    max_recommended_writers: int
    default_writer_strategy: Literal[
        "serial",
        "in_process_serial",
        "connection_pool",
        "staged_run",
    ]
    declaration_status: Literal["declared", "undeclared", "degraded"]
    notes: tuple[str, ...] = ()

Add an optional protocol:

@runtime_checkable
class WriteConcurrencyDeclaringBackend(Protocol):
    def backend_write_concurrency_declaration(
        self,
    ) -> BackendWriteConcurrencyDeclaration: ...

Default behavior for undeclared backends:

• supports_concurrent_file_writes = False
• supports_staged_index_runs = False
• supports_atomic_run_activation = False
• supports_concurrent_readers_during_index = False
• default_writer_strategy = "serial"
• max_recommended_writers = 1

Preserve a single portable contract

Prefer one contract that covers both in-process and server DB backends. Backends should be allowed to implement only the serial subset.

The common contract should distinguish:

• analysis concurrency: analyzer-side, covered by issue Parallelize full indexing with explicit analyzer concurrency contracts #55
• write concurrency: backend-side, this issue
• finalization: must remain deterministic and may remain serial even for concurrent-write backends

Add staged index-run semantics

Concurrent writes are safest if the backend supports staging by index run.

Proposed model:

1. begin_index_run(root, mode) creates a run identity.
2. Per-file writes are scoped to index_run_id.
3. Concurrent writers write into run-scoped tables or rows.
4. Finalization validates completeness and duplicate constraints.
5. Activation atomically marks the run active.
6. Cleanup removes obsolete run data after successful activation.

This avoids clearing the active index before the replacement index is complete.

Suggested contract additions:

@dataclass(frozen=True)
class BackendIndexRun:
    run_id: str
    root: Path
    mode: Literal["full", "incremental"]

@dataclass(frozen=True)
class BackendConcurrentPersistRequest:
    root: Path
    run_id: str
    file_metadata: FileMetadataSnapshot
    analysis: AnalysisResult
    embedding_backend: EmbeddingBackendSpec | None = None
    previous_embeddings: Mapping[str, object] | None = None

class ConcurrentIndexWriteBackend(Protocol):
    def begin_staged_index_run(self, root: Path, *, full: bool) -> BackendIndexRun: ...
    def persist_analysis_for_run(
        self,
        request: BackendConcurrentPersistRequest,
    ) -> tuple[int, int]: ...
    def finalize_staged_index_run(
        self,
        run: BackendIndexRun,
        request: BackendRuntimeInventoryRequest,
    ) -> None: ...
    def abort_staged_index_run(self, run: BackendIndexRun) -> None: ...

The existing IndexWriteSession can remain as the serial baseline. New concurrent-capable backends can implement the staged contract in addition to the existing one.

ID allocation and natural keys

Concurrent backend writes should not depend on session-local integer ID allocation unless the backend can guarantee deterministic allocation under concurrency.

Preferred strategies:

• use stable natural keys for file-owned rows where possible
• use backend-generated IDs only inside run-scoped tables when final output does not depend on ID order
• derive deterministic keys from stable IDs and file identity
• postpone derived graph row construction until finalization when needed

Deterministic finalization contract

Even if per-file writes are concurrent, finalization must be deterministic.

Finalization should own:

• duplicate stable-ID validation across the run
• derived call/reference/include index rebuild
• runtime inventory persistence
• analyzer/backend inventory persistence
• activation of the staged run
• deletion of stale active rows
• cleanup of abandoned run rows

Configuration updates

Add backend write-concurrency configuration separate from analyzer concurrency.

Suggested config:

[index.backend_concurrency]
enabled = false
strategy = "serial"
max_writers = 1
min_files = 128
transaction_scope = "file"
staged_runs = "auto"
reader_isolation = "preserve_active"

Field semantics:

• enabled: default false initially.
• strategy: one of "serial", "auto", "connection_pool", "staged_run".
• max_writers: 1 means serial; values greater than 1 require backend support.
• min_files: minimum file count before concurrent writes are considered.
• transaction_scope: one of "file", "batch", "run".
• staged_runs: one of "off", "auto", "required".
• reader_isolation: "preserve_active" means readers continue seeing the old active index until activation.

CLI overrides:

codira index --backend-writers N
codira index --backend-concurrency serial|auto|connection-pool|staged-run

Recommended behavior:

• --backend-writers 1 forces serial persistence.
• --backend-concurrency serial uses the existing IndexWriteSession path.
• --backend-concurrency auto uses concurrent writes only when the active backend declares support.
• --backend-concurrency connection-pool fails fast unless the backend declares supports_concurrent_file_writes.
• --backend-concurrency staged-run fails fast unless the backend declares staged run support and atomic activation.

Environment overrides, if desired:

CODIRA_BACKEND_WRITERS=N
CODIRA_BACKEND_CONCURRENCY=serial|auto|connection-pool|staged-run

Persistent config surfaces to update:

• config schema
• default config rendering
• codira config init --full
• codira config validate --json
• effective config docs
• plugin backend configuration docs

Current backend implementation changes

SQLite backend

Recommended initial declaration:

• supports_concurrent_file_writes = False
• supports_staged_index_runs = False
• supports_atomic_run_activation = False
• supports_concurrent_readers_during_index = False
• default_writer_strategy = "serial"
• max_recommended_writers = 1

Technical reasons:

• current _SQLiteIndexWriteSession owns one shared sqlite3.Connection
• session state includes completion flags, _embedding_backend, and _pending_embedding_rows
• per-file shared-connection persistence uses a fixed savepoint name
• commit flushes session-level pending embeddings once
• full rebuild currently clears/replaces active index state rather than staging a new run

Possible future SQLite improvements:

• retain serial writes as the default
• optionally add a staged-run model in the same SQLite file
• use one writer connection only; SQLite generally serializes writes anyway
• allow concurrent readers only if active-run isolation is implemented

SQLite should not be the first target for backend write parallelism.

DuckDB backend

Recommended initial declaration:

• supports_concurrent_file_writes = False
• supports_staged_index_runs = False
• supports_atomic_run_activation = False
• supports_concurrent_readers_during_index = False
• default_writer_strategy = "serial"
• max_recommended_writers = 1

Technical reasons:

• current full rebuild path rolls back, closes the connection, unlinks the DB/WAL files, reinitializes the DB, opens a new connection, and starts a transaction
• current session owns an ID allocator
• current session owns structural row buffers
• current session owns pending embedding/import/docstring/reference/call buffers
• DuckDB shared-connection path does not use SQLite-style savepoints and has special transaction handling

Possible future DuckDB improvements:

• avoid file deletion during full rebuild by using staged run tables
• avoid session-local ID allocation for concurrent rows
• move structural/index rebuild into deterministic finalization
• evaluate whether multiple writer connections are actually beneficial for the DuckDB driver and workload

DuckDB may benefit more from staged-run correctness improvements than from concurrent writes.

Memory backend

Recommended initial declaration:

• supports_concurrent_file_writes = False
• supports_staged_index_runs = False
• supports_atomic_run_activation = False
• supports_concurrent_readers_during_index = False
• default_writer_strategy = "serial"
• max_recommended_writers = 1

Technical reasons:

• current memory backend is root-scoped mutable Python state
• no locking or copy-on-write isolation exists
• it is used for deterministic contract validation, not production concurrency

Possible future improvements:

• implement a locked variant only if needed for contract tests
• use staged immutable snapshots for deterministic comparison tests
• keep production concurrency validation focused on real backend plugins

Future independent-process backend caveats

Server DB backends such as PostgreSQL, MySQL, and MariaDB can make concurrent writes practical, but they do not remove the need for a strict Codira contract.

PostgreSQL

Likely strongest fit for concurrent backend writes:

• robust transactional DDL/DML behavior
• row-level locking
• INSERT ... ON CONFLICT
• advisory locks if needed for one active index run per root
• good support for staging tables and atomic activation

Caveats:

• choose isolation level explicitly
• avoid deadlocks from inconsistent row ordering
• validate deterministic final results independent of writer completion order
• handle connection pool sizing and server backpressure

MySQL / MariaDB

Potentially feasible with InnoDB, but contract must account for engine differences.

Caveats:

• transaction behavior depends on storage engine
• INSERT ... ON DUPLICATE KEY UPDATE semantics differ from PostgreSQL upsert
• gap locks and isolation behavior may surprise concurrent file writes
• DDL and metadata operations may not behave like PostgreSQL
• final activation and cleanup must be tested under the target engine

Shared requirements for server DB backends

Server DB backends must define:

• root/repository identity mapping
• one active index run per root, or explicit multi-run arbitration
• connection pool ownership
• transaction scope
• retry policy for serialization failures/deadlocks
• isolation level
• run activation semantics
• cleanup of abandoned runs
• reader visibility during indexing
• migration/current-schema policy

Implementation strategy

Phase 1: Add declarations and fail-closed configuration

• Add backend write-concurrency declaration dataclass/protocol.
• Export declarations through codira caps --json.
• Add config parsing and validation.
• Keep all current backends declaring serial-only.
• Add CLI options that fail closed when unsupported.

This phase changes contracts and documentation without changing persistence behavior.

Phase 2: Refactor index persistence into strategy objects

Introduce a core persistence strategy boundary:

• serial session strategy: current behavior
• staged concurrent strategy: future behavior

The serial strategy should remain byte-for-byte behaviorally equivalent.

Phase 3: Add staged-run contract tests with a fake backend

Before modifying SQLite or DuckDB, create a deterministic fake backend implementing staged concurrent writes.

Tests should prove:

• writes can complete in arbitrary order
• final results are deterministic
• failed file writes are isolated
• aborted runs do not change active query results
• activation is atomic from the query surface

Phase 4: Consider a real backend target

Choose one implementation target after benchmarks show DB persistence is a bottleneck.

Recommended order:

1. fake staged backend for contract tests
2. optional SQLite staged-run prototype for correctness semantics, not speed
3. PostgreSQL plugin prototype if real concurrent write speed is the goal
4. DuckDB staged-run cleanup if full-rebuild correctness/reader isolation is the goal

Phase 5: Benchmark and tune

Benchmark serial persistence vs concurrent persistence with:

• max_writers = 1
• max_writers = 2
• max_writers = 4
• auto-selected writer count

Measure:

• analysis time
• per-file persistence time
• embedding persistence time
• derived index rebuild time
• final activation time
• cleanup time
• query availability during indexing

Verification

Static verification

Add contract checks for declarations:

• unsupported backend concurrency options fail before mutation
• caps --json reports serial-only current backends
• config validation rejects invalid strategy/max writer combinations
• staged-run fields are present only when supported or explicitly degraded

Dynamic verification

Required properties:

• serial and concurrent strategies produce identical normalized query results
• writer completion order does not affect final rows
• duplicate stable IDs are detected deterministically
• per-file write failure does not corrupt the active index
• aborted staged runs do not affect queries
• active run switches atomically
• cleanup removes old staged rows only after successful activation
• concurrent readers either see old active index or fail with an explicit documented mode, never partial data

Performance verification

Add benchmarks or benchmark scripts that report:

• serial write time
• concurrent write time
• finalization time
• speedup ratio
• writer saturation point
• DB lock/deadlock/retry counts where applicable

Performance tests should not replace correctness tests.

Tests to add

Unit tests

• backend concurrency declaration defaults
• backend declaration export in caps --json
• config parsing for [index.backend_concurrency]
• CLI override precedence over config
• invalid max_writers, strategy, transaction_scope, and staged_runs
• fail-closed behavior for unsupported current backends
• serial strategy still calls existing IndexWriteSession

Contract tests

• fake staged backend accepts out-of-order file writes
• fake staged backend finalizes deterministic results
• fake staged backend abort preserves prior active state
• fake staged backend rejects activation with missing or duplicate rows
• reader queries during a staged run see only active data

SQLite tests

• serial-only declaration
• --backend-concurrency auto stays serial
• --backend-concurrency connection-pool fails fast
• --backend-writers 2 fails fast unless strategy is serial-compatible
• existing index tests continue passing unchanged

DuckDB tests

• serial-only declaration
• full rebuild behavior remains serial
• unsupported concurrent mode fails before DB file deletion/reinitialization
• existing DuckDB package tests continue passing unchanged

Memory backend tests

• serial-only declaration
• contract tests confirm no concurrent write support is implied
• existing memory backend tests continue passing unchanged

Future PostgreSQL/MySQL/MariaDB tests

For any future server backend:

• multi-connection concurrent per-file writes
• transaction retry on serialization/deadlock errors
• staged run activation
• reader isolation during indexing
• cleanup of abandoned runs
• deterministic serial-vs-concurrent query result equivalence
• engine-specific upsert behavior
• connection pool exhaustion behavior

Documentation updates

Update developer and user documentation:

• docs/configuration.md: document [index.backend_concurrency] and CLI/env precedence.
• backend plugin author docs: document write-concurrency declarations and staged-run requirements.
• capability contract docs/schema: document backend write-concurrency fields in caps --json.
• architecture docs or ADR: explain serial IndexWriteSession vs staged concurrent backend writes.
• testing/process docs: document serial-vs-concurrent equivalence requirements.
• backend package READMEs: declare current SQLite/DuckDB serial-only behavior.

Docs must state:

• analyzer concurrency and backend write concurrency are separate concerns
• current SQLite, DuckDB, and memory backends are serial-write only
• server DB backends can support concurrent writes only with staged run semantics and deterministic finalization
• concurrent backend writes must not expose partial indexes to readers
• performance tuning must not weaken deterministic output

Validation commands

Required validation for a contract/config-only PR:

uv run codira caps --json
uv run codira config init --full --output <tmp-config>
uv run codira config validate --path <tmp-config> --json
uv run pytest -q tests/test_contracts.py tests/test_incremental_indexing.py tests/test_memory_backend.py
uv run pytest -q packages/codira-backend-sqlite/tests/test_sqlite_backend_package.py
uv run pytest -q packages/codira-backend-duckdb/tests/test_duckdb_backend_package.py
uv run pre-commit run --all-files

Required validation for any backend behavior change:

uv run codira index --full --backend-concurrency serial
uv run codira index --full --backend-concurrency auto --backend-writers 1
uv run pytest -q
uv run pre-commit run --all-files

For future server DB plugins, add backend-specific integration tests with a disposable database service.

Acceptance criteria

• Backend write-concurrency support is explicit in contracts and caps --json.
• Current SQLite, DuckDB, and memory backends declare serial-only support.
• Unsupported concurrent backend modes fail before mutating backend state.
• Persistent config and CLI controls exist for backend write concurrency.
• Serial backend behavior remains unchanged.
• A staged-run contract is documented and testable.
• Tests prove deterministic finalization under out-of-order concurrent writes using a fake backend before any production backend adopts concurrency.
• Documentation covers current backend limitations and future server DB caveats.
• Validation includes config, capabilities, current backend package tests, memory backend contract tests, pre-commit, and full pytest.

Open questions

• Should staged-run support become a required capability for any backend that declares concurrent writes?
• Should Codira allow concurrent writes without preserving active-reader visibility, or should reader isolation be mandatory?
• Should server DB plugins use root-scoped advisory locks to ensure only one active index run per repository root?
• Should max_writers be configured globally, per backend, or both?
• Should concurrent backend writes be enabled only after analyzer-side parallelism from issue Parallelize full indexing with explicit analyzer concurrency contracts #55 is complete and benchmarked?
• Should derived graph indexes be rebuilt from staged raw rows serially, or can derived rebuilds also become backend-specific concurrent operations later?

[marco0560]

During this contract change, also resolve the current backend storage seam implementation debt instead of preserving it as a permanent Semgrep exception.

Current state:

• SQLite does not currently avoid Semgrep exceptions for this problem.
• The SQLite backend uses the same containment strategy as DuckDB: centralize the remaining core storage dependency in one backend-owned seam.
• For SQLite, that seam is packages/codira-backend-sqlite/src/codira_backend_sqlite/sqlite_storage.py.
• For DuckDB, the comparable seam is packages/codira-backend-duckdb/src/codira_backend_duckdb/repo_storage.py.

SQLite details:

• sqlite_storage.py exposes package-local helpers such as get_db_path() and init_db().
• The runtime backend imports those package-local helpers instead of importing codira.storage throughout the backend implementation.
• The seam still imports selected core storage helpers: _read_metadata_file, _write_metadata_file, get_codira_dir, and get_metadata_path.
• That is why codira.plugins.no-core-storage-import currently needs an allowlist entry for sqlite_storage.py.
• SQLite also has separate, expected allowlist entries for direct sqlite3 imports in __init__.py, sqlite_storage.py, and sqlite_support.py. Those are backend-implementation imports and should remain valid while the SQLite backend exists.

DuckDB comparison:

• DuckDB localizes the same path/metadata dependency in repo_storage.py.
• DuckDB currently imports selected codira.storage helpers there, rather than spreading the dependency through the package.
• The DuckDB seam is narrower than SQLite today: it mainly handles repository storage path and metadata path resolution.
• The SQLite seam does more: DB path selection, metadata read/write, and bootstrap/schema initialization.

Desired outcome for this issue:

• The new backend write-concurrency/staged-run contract should define how storage paths, metadata paths, index-run identity, and activation state are passed to or owned by backend plugins.
• Backends should not need to import private or semi-private helpers from codira.storage to bootstrap themselves.
• Prefer one clean public contract that works for both current in-process backends and future server-backed plugins.
• Either move the shared path/metadata helpers into an explicit backend-facing public API, or have core resolve those values and pass them into backend lifecycle calls.
• Avoid creating a second generation of allowlisted backend seams around the new concurrency contract.

Semgrep cleanup reminder:

• After the contract refactor, update the Semgrep allowlists so the seam is cleaner.
• Remove the codira.plugins.no-core-storage-import allowlist entries for backend storage seam files if backends no longer import codira.storage.
• Keep only the exceptions that are inherent to the backend implementation, such as SQLite files importing sqlite3.
• Re-run the repository Semgrep validation and verify that the allowlist footprint is smaller, not merely moved.

Acceptance criteria to add:

• SQLite and DuckDB no longer import codira.storage from package-local seam files, or the remaining dependency is through an explicitly documented public backend contract.
• Semgrep allowlists are updated to reflect the new contract boundary.
• The current SQLite/DuckDB path and metadata behavior remains unchanged under serial indexing.
• Tests cover the new backend lifecycle inputs so future backends do not need private core storage imports for path, metadata, or staged-run setup.