T1-2: Implement DuckDBFileBackend

[JesuFemi-O]

Milestone: #82
Depends on: #89

Context

No structured metadata backend exists. observability.py writes to .tycoon/metadata.duckdb via raw SQL strings. The new backend must live alongside it without conflict.

Goal

Create src/tycoon/metadata_backends/__init__.py (empty) and src/tycoon/metadata_backends/duckdb_file.py.

DuckDBFileBackend is a context manager class:

class DuckDBFileBackend:
    def __init__(self, path: Path, read_only: bool = False) -> None: ...
    def __enter__(self) -> "DuckDBFileBackend": ...  # opens connection, ensures schema
    def __exit__(self, *args: object) -> None: ...   # closes connection

    def append_event(self, event: BaseEvent) -> None:
        # INSERT INTO events (event_id, event_type, source_id, runtime_id, timestamp, payload)
        # payload is event.model_dump_json()

    def query_events(self, filter: EventFilter | None = None) -> list[BaseEvent]:
        # SELECT payload FROM events WHERE ... ORDER BY timestamp ASC
        # Deserialize each row via TypeAdapter(Event).validate_json(row["payload"])

    def upsert_snapshot(self, kind: str, key: str, blob: dict) -> None:
        # INSERT OR REPLACE INTO snapshots (kind, key, blob, updated_at)

    def read_snapshot(self, kind: str, key: str) -> dict | None:
        # SELECT blob FROM snapshots WHERE kind=? AND key=?

Schema (created on __enter__ via CREATE TABLE IF NOT EXISTS):

CREATE TABLE IF NOT EXISTS events (
    event_id    TEXT PRIMARY KEY,
    event_type  TEXT NOT NULL,
    source_id   TEXT NOT NULL,
    runtime_id  TEXT NOT NULL,
    timestamp   TIMESTAMPTZ NOT NULL,
    payload     TEXT NOT NULL   -- full JSON
);

CREATE TABLE IF NOT EXISTS snapshots (
    kind        TEXT NOT NULL,
    key         TEXT NOT NULL,
    blob        TEXT NOT NULL,  -- JSON
    updated_at  TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (kind, key)
);

Important: Do NOT use duckdb.connect() on the same file path that dlt uses for the raw warehouse (config.raw_db). The metadata file is always .tycoon/metadata.duckdb — a separate file from the raw warehouse. This avoids DuckDB's intra-process double-open rejection.

Acceptance

• Running the contract tests from T1-6: MetadataBackend contract tests (parametrized) #99 against DuckDBFileBackend passes
• The file is created at .tycoon/metadata.duckdb on first __enter__
• A second instantiation against the same file does not corrupt data

[JesuFemi-O]

Implemented in PR #136 (merged 2026-07-15). DuckDBFileBackend lives in src/tycoon/metadata_backends/duckdb_file.py with schema creation, append_event, query_events (with EventFilter), upsert_snapshot, read_snapshot, indexes on timestamp/source_id/event_type, and read-only mode support.