Codebase intelligence for AI and humans: code health scores, auto-generated docs, git analytics, dead code detection, and architectural decisions via MCP.
Python aicode-complexitycode-healthcode-intelligencecode-qualitydead-codedeveloper-toolsdocumentationgit-analyticsmcpopen-sourcepythonrefactoringstatic-analysistechnical-debt
"""Regression tests for schema reconciliation in ``init_db``.
Covers the bug where a user who indexed a repo with an older repowise version
would hit ``no such column: decision_records.verification`` after upgrading,
because ``Base.metadata.create_all`` does not ALTER existing tables. The fix
reconciles columns + indexes against the model on every ``init_db`` call.
These tests pin the contract generically — anything a future additive
migration adds to the model (column, index, table) must be picked up by
``init_db`` without per-migration code in ``database.py``.
"""
from __future__ import annotations
import sqlite3
from pathlib import Path
from typing import Any
import pytest
from sqlalchemy import Boolean, Column
from sqlalchemy.dialects import postgresql, sqlite
from repowise.core.persistence import create_engine, init_db
from repowise.core.persistence.models import Base
@pytest.mark.parametrize(
("dialect", "value", "expected"),
[
(sqlite.dialect(), False, '"pinned" BOOLEAN DEFAULT 0 NOT NULL'),
(sqlite.dialect(), True, '"pinned" BOOLEAN DEFAULT 1 NOT NULL'),
(postgresql.dialect(), False, '"pinned" BOOLEAN DEFAULT false NOT NULL'),
(postgresql.dialect(), True, '"pinned" BOOLEAN DEFAULT true NOT NULL'),
],
)
def test_python_boolean_defaults_use_dialect_literals(
dialect: object,
value: bool,
expected: str,
) -> None:
"""Legacy-column DDL must be accepted by both supported databases."""
from repowise.core.persistence.database import _add_column_ddl
column = Column("pinned", Boolean, nullable=False, default=value)
assert _add_column_ddl(column, dialect) == expected
def _table_columns(db_path: Path, table: str) -> set[str]:
conn = sqlite3.connect(db_path)
try:
return {row[1] for row in conn.execute(f'PRAGMA table_info("{table}")')}
finally:
conn.close()
def _table_indexes(db_path: Path, table: str) -> set[str]:
conn = sqlite3.connect(db_path)
try:
return {row[1] for row in conn.execute(f'PRAGMA index_list("{table}")')}
finally:
conn.close()
def _table_exists(db_path: Path, table: str) -> bool:
conn = sqlite3.connect(db_path)
try:
row = conn.execute(
"SELECT name FROM sqlite_master WHERE type='table' AND name=?", (table,)
).fetchone()
return row is not None
finally:
conn.close()
def _execute(db_path: Path, sql: str, params: tuple[Any, ...] = ()) -> None:
conn = sqlite3.connect(db_path)
try:
conn.execute(sql, params)
conn.commit()
finally:
conn.close()
def _fetchall(db_path: Path, sql: str) -> list[tuple[Any, ...]]:
conn = sqlite3.connect(db_path)
try:
return list(conn.execute(sql))
finally:
conn.close()
@pytest.mark.asyncio
async def test_fresh_db_has_all_model_columns(tmp_path: Path) -> None:
"""A brand-new DB should mirror Base.metadata exactly — no drift between
what the ORM expects and what create_all built."""
db_path = tmp_path / "wiki.db"
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
for table in Base.metadata.tables.values():
assert _table_exists(db_path, table.name), f"missing table: {table.name}"
db_cols = _table_columns(db_path, table.name)
for column in table.columns:
assert column.name in db_cols, f"{table.name}.{column.name} missing after fresh init_db"
@pytest.mark.asyncio
async def test_legacy_db_missing_column_is_reconciled(tmp_path: Path) -> None:
"""The actual bug scenario: simulate a pre-migration DB by dropping the
``verification`` column from a fresh ``decision_records`` table, then run
``init_db`` again and verify it was added back."""
db_path = tmp_path / "wiki.db"
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
# Simulate a legacy DB by dropping the column SQLAlchemy uses today.
# SQLite ≥ 3.35 supports DROP COLUMN; on older builds the test is skipped.
try:
_execute(db_path, 'ALTER TABLE "decision_records" DROP COLUMN "verification"')
except sqlite3.OperationalError as exc:
pytest.skip(f"SQLite build doesn't support DROP COLUMN: {exc}")
assert "verification" not in _table_columns(db_path, "decision_records")
# Re-run init_db — reconciliation must restore the missing column.
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
assert "verification" in _table_columns(db_path, "decision_records"), (
"init_db did not reconcile missing column"
)
@pytest.mark.asyncio
async def test_reconciler_preserves_existing_row_data(tmp_path: Path) -> None:
"""Reconciling a missing column on a populated table must not lose
pre-existing rows — the ALTER TABLE ADD COLUMN should back-fill with
the model's server_default."""
db_path = tmp_path / "wiki.db"
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
# Drop verification and seed a row that has no value for it.
try:
_execute(db_path, 'ALTER TABLE "decision_records" DROP COLUMN "verification"')
except sqlite3.OperationalError as exc:
pytest.skip(f"SQLite build doesn't support DROP COLUMN: {exc}")
# Cover every NOT NULL column on decision_records — we're bypassing the
# ORM (which would apply Python defaults), so the raw INSERT must list
# every column the model marks not-null. Keep this in sync with models.py
# if new NOT NULL columns are added without server_default.
_execute(
db_path,
"""
INSERT INTO decision_records
(id, repository_id, title, status, kind, context, decision, rationale,
alternatives_json, consequences_json, affected_files_json,
affected_modules_json, tags_json, evidence_commits_json,
source, confidence, staleness_score, created_at, updated_at)
VALUES
('rec-1', 1, 't', 'active', 'architectural', '', 'd', '',
'[]', '[]', '[]',
'[]', '[]', '[]',
'inline', 0.5, 0.0, '2026-01-01', '2026-01-01')
""",
)
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
rows = _fetchall(db_path, "SELECT id, verification FROM decision_records")
assert len(rows) == 1
assert rows[0][0] == "rec-1"
# ``verification`` has server_default="unverified" — back-filled rows get it.
assert rows[0][1] == "unverified"
@pytest.mark.asyncio
async def test_legacy_db_missing_table_is_created(tmp_path: Path) -> None:
"""Tables added in a later release (e.g. ``decision_evidence``) must
appear on an upgraded DB — covered by create_all but pinned here so the
reconciliation contract is regression-tested end-to-end."""
db_path = tmp_path / "wiki.db"
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
_execute(db_path, 'DROP TABLE IF EXISTS "decision_evidence"')
assert not _table_exists(db_path, "decision_evidence")
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
assert _table_exists(db_path, "decision_evidence")
@pytest.mark.asyncio
async def test_legacy_db_missing_index_is_recreated(tmp_path: Path) -> None:
"""Indexes declared on the model that were dropped from a legacy DB
must be re-created — proves the reconciler covers index drift too."""
db_path = tmp_path / "wiki.db"
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
# Pick any model-declared index to drop.
candidate_table = None
candidate_index = None
for table in Base.metadata.tables.values():
for index in table.indexes:
if index.name and not index.name.startswith("sqlite_"):
candidate_table = table.name
candidate_index = index.name
break
if candidate_index:
break
assert candidate_index, "no model-declared index found in metadata"
_execute(db_path, f'DROP INDEX IF EXISTS "{candidate_index}"')
assert candidate_index not in _table_indexes(db_path, candidate_table)
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
assert candidate_index in _table_indexes(db_path, candidate_table), (
f"reconciler did not re-create index {candidate_index}"
)
@pytest.mark.asyncio
async def test_init_db_is_idempotent(tmp_path: Path) -> None:
"""Running init_db twice in a row must not raise, and the second call
must not duplicate columns or indexes."""
db_path = tmp_path / "wiki.db"
for _ in range(2):
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
cols = _table_columns(db_path, "decision_records")
# Each name appears exactly once in PRAGMA table_info — set semantics
# would hide duplicates, so explicitly query the raw count too.
rows = _fetchall(db_path, 'SELECT name FROM pragma_table_info("decision_records")')
assert len(rows) == len(cols), "duplicate column names after re-running init_db"
def _backfillable_column(table: Any) -> Any:
"""The first non-PK column on *table* the reconciler can safely back-fill."""
for column in table.columns:
if column.primary_key:
continue
if (
column.nullable
or column.server_default is not None
or (
column.default is not None
and getattr(column.default, "arg", None) is not None
and not callable(getattr(column.default, "arg", None))
)
):
return column
return None
def _two_drop_targets() -> tuple[str, str, str, str]:
"""Two (table, column) pairs, the second ordered after the first.
``Base.metadata.tables`` preserves model-definition order, which is the
order ``_reconcile_schema`` walks. Picking a victim and a target after it
is what makes "a failure on the victim did not strand the rest" testable.
"""
found: list[tuple[str, str]] = []
for table in Base.metadata.tables.values():
column = _backfillable_column(table)
if column is not None:
found.append((table.name, column.name))
if len(found) == 2:
break
assert len(found) == 2, "metadata has fewer than two back-fillable tables"
return found[0][0], found[0][1], found[1][0], found[1][1]
@pytest.mark.asyncio
async def test_failed_statement_does_not_strand_later_tables(
tmp_path: Path, monkeypatch: pytest.MonkeyPatch
) -> None:
"""A statement that fails mid-loop must not abort the whole reconcile.
SQLite DDL autocommits statement by statement, so an abort leaves the
store half-migrated permanently: every table ordered after the victim is
unreachable on that call and on every later one. The reconciler therefore
continues past a failure and reports it at the end, which is what lets a
repeated call converge.
"""
from repowise.core.persistence import database as db_module
victim_table, victim_column, later_table, later_column = _two_drop_targets()
db_path = tmp_path / "wiki.db"
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
try:
_execute(db_path, f'ALTER TABLE "{victim_table}" DROP COLUMN "{victim_column}"')
_execute(db_path, f'ALTER TABLE "{later_table}" DROP COLUMN "{later_column}"')
except sqlite3.OperationalError as exc:
pytest.skip(f"SQLite build doesn't support DROP COLUMN: {exc}")
real_add_column_ddl = db_module._add_column_ddl
def _poisoned(column: Any, dialect: Any) -> str:
if column.name == victim_column and column.table.name == victim_table:
# Deliberately unparseable, so the ALTER fails the way a real
# unsupported type or a locked table would.
return f'"{column.name}" NOT_A_REAL_TYPE DEFAULT'
return real_add_column_ddl(column, dialect)
monkeypatch.setattr(db_module, "_add_column_ddl", _poisoned)
# The failure is still reported: write paths must not silently succeed.
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
with pytest.raises(Exception): # noqa: B017 - the driver's own error type
try:
await init_db(engine)
finally:
await engine.dispose()
assert victim_column not in _table_columns(db_path, victim_table)
assert later_column in _table_columns(db_path, later_table), (
"a failure on an earlier table stranded every table after it"
)
@pytest.mark.asyncio
async def test_second_reconcile_finishes_what_the_first_could_not(
tmp_path: Path, monkeypatch: pytest.MonkeyPatch
) -> None:
"""Repeated calls converge, even while the failing statement keeps failing.
The failure that strands a reconcile is usually not transient, so "call it
again" has to make progress on its own. Only once the cause clears does the
victim itself get picked up.
"""
from repowise.core.persistence import database as db_module
victim_table, victim_column, later_table, later_column = _two_drop_targets()
db_path = tmp_path / "wiki.db"
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
try:
_execute(db_path, f'ALTER TABLE "{victim_table}" DROP COLUMN "{victim_column}"')
_execute(db_path, f'ALTER TABLE "{later_table}" DROP COLUMN "{later_column}"')
except sqlite3.OperationalError as exc:
pytest.skip(f"SQLite build doesn't support DROP COLUMN: {exc}")
real_add_column_ddl = db_module._add_column_ddl
def _poisoned(column: Any, dialect: Any) -> str:
if column.name == victim_column and column.table.name == victim_table:
return f'"{column.name}" NOT_A_REAL_TYPE DEFAULT'
return real_add_column_ddl(column, dialect)
monkeypatch.setattr(db_module, "_add_column_ddl", _poisoned)
for _ in range(2):
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
with pytest.raises(Exception): # noqa: B017
try:
await init_db(engine)
finally:
await engine.dispose()
# This is the D7 measurement verbatim: "after a SECOND reconcile, later
# table fixed?" used to be False forever.
assert later_column in _table_columns(db_path, later_table)
assert victim_column not in _table_columns(db_path, victim_table)
# Whatever made the statement fail is gone (a lock released, a driver
# upgraded); the next call must pick the victim back up.
monkeypatch.setattr(db_module, "_add_column_ddl", real_add_column_ddl)
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
assert victim_column in _table_columns(db_path, victim_table)
assert later_column in _table_columns(db_path, later_table)
@pytest.mark.asyncio
async def test_reconciler_handles_arbitrary_new_column(tmp_path: Path) -> None:
"""Forward-compat contract: simulate a *future* migration by dropping any
server_default'd column and verifying init_db adds it back, regardless of
which table or column it is. Drives the test off Base.metadata rather
than hard-coding ``verification`` so the contract is generic."""
db_path = tmp_path / "wiki.db"
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
# Find any non-PK column the reconciler can safely back-fill: either it
# has a server_default, or it has a static Python default the reconciler
# synthesizes a DEFAULT clause from, or it's nullable. Anything else is
# genuinely unsafe to back-fill onto a populated table.
target_table = None
target_column = None
for table in Base.metadata.tables.values():
for column in table.columns:
if column.primary_key:
continue
backfillable = (
column.nullable
or column.server_default is not None
or (
column.default is not None
and getattr(column.default, "arg", None) is not None
and not callable(getattr(column.default, "arg", None))
)
)
if not backfillable:
continue
target_table = table.name
target_column = column.name
break
if target_column:
break
assert target_column, "metadata has no back-fillable non-PK column"
try:
_execute(db_path, f'ALTER TABLE "{target_table}" DROP COLUMN "{target_column}"')
except sqlite3.OperationalError as exc:
pytest.skip(f"SQLite build doesn't support DROP COLUMN: {exc}")
assert target_column not in _table_columns(db_path, target_table)
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
assert target_column in _table_columns(db_path, target_table)
@pytest.mark.asyncio
async def test_adding_owner_line_pct_moves_stale_blame_line_shares(tmp_path: Path) -> None:
"""Before ``primary_owner_line_pct`` existed, blame stored the owner's line
share as ``primary_owner_commit_pct``. Upgrading must not leave that value
labelled a commit share on files no update re-walks."""
import json
from repowise.core.persistence import create_session_factory, crud, get_session
db_path = tmp_path / "wiki.db"
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
authors = json.dumps(
[{"name": "Bob", "commit_count": 3}, {"name": "Ada", "commit_count": 1}]
)
rows = {
# blame owner Ada: 70% of lines, 1 of 4 commits
"blame.py": ("Ada", 0.7),
# no blame: the top committer and their commit share, already right
"commits.py": ("Bob", 0.75),
# blame owner with no counted commits
"old.py": ("Cy", 0.6),
}
try:
await init_db(engine)
async with get_session(create_session_factory(engine)) as session:
repo = await crud.upsert_repository(session, name="r", local_path=str(tmp_path))
await crud.upsert_git_metadata_bulk(
session,
repo.id,
[
{
"file_path": path,
"commit_count_total": 4,
"top_authors_json": authors,
"primary_owner_name": owner,
"primary_owner_commit_pct": pct,
}
for path, (owner, pct) in rows.items()
],
)
finally:
await engine.dispose()
try:
_execute(db_path, 'ALTER TABLE "git_metadata" DROP COLUMN "primary_owner_line_pct"')
except sqlite3.OperationalError as exc:
pytest.skip(f"SQLite build doesn't support DROP COLUMN: {exc}")
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
got = {
path: (commit_pct, line_pct)
for path, commit_pct, line_pct in _fetchall(
db_path,
"SELECT file_path, primary_owner_commit_pct, primary_owner_line_pct "
"FROM git_metadata",
)
}
assert got == {
"blame.py": (0.25, 0.7),
"commits.py": (0.75, None),
"old.py": (None, 0.6),
}
async def test_adding_owner_line_pct_with_nothing_to_move_succeeds(tmp_path: Path) -> None:
"""The data step returns how many rows it moved. Zero is the common case
(every row already right, or no git history at all) and must not be taken
for a statement to execute."""
db_path = tmp_path / "wiki.db"
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
try:
_execute(db_path, 'ALTER TABLE "git_metadata" DROP COLUMN "primary_owner_line_pct"')
except sqlite3.OperationalError as exc:
pytest.skip(f"SQLite build doesn't support DROP COLUMN: {exc}")
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
assert "primary_owner_line_pct" in _table_columns(db_path, "git_metadata")
def test_postgres_reconcile_ignores_pgvector_type_warning(monkeypatch: pytest.MonkeyPatch) -> None:
import warnings
from types import SimpleNamespace
from sqlalchemy.exc import SAWarning
from repowise.core.persistence import database as db_module
table = Base.metadata.tables["wiki_pages"]
class Inspector:
def get_table_names(self) -> list[str]:
return ["wiki_pages"]
def get_columns(self, _name: str) -> list[dict[str, Any]]:
warnings.warn("Did not recognize type 'vector' of column 'embedding'", SAWarning, stacklevel=2)
return [{"name": c.name, "type": c.type, "nullable": c.nullable} for c in table.columns]
def get_indexes(self, _name: str) -> list[dict[str, Any]]:
return [{"name": i.name} for i in table.indexes]
monkeypatch.setattr(db_module, "inspect", lambda _conn: Inspector())
connection = SimpleNamespace(dialect=postgresql.dialect(), execute=lambda _stmt: None)
with warnings.catch_warnings():
warnings.simplefilter("error")
db_module._reconcile_schema(connection)
async def _init(db_path: Path) -> None:
engine = create_engine(f"sqlite+aiosqlite:///{db_path}")
try:
await init_db(engine)
finally:
await engine.dispose()
def _stamp_state(db_path: Path) -> tuple[int, int]:
(user_version,) = _fetchall(db_path, "PRAGMA user_version")[0]
(cookie,) = _fetchall(db_path, "PRAGMA schema_version")[0]
return user_version, cookie
@pytest.mark.asyncio
async def test_stamped_store_skips_the_reconcile(
tmp_path: Path, monkeypatch: pytest.MonkeyPatch
) -> None:
"""A store carrying the current stamp opens without reflecting a table."""
from repowise.core.persistence import database as db_module
db_path = tmp_path / "wiki.db"
await _init(db_path)
user_version, cookie = _stamp_state(db_path)
assert user_version == db_module.schema_stamp(cookie)
def _walked(_connection: Any) -> None:
raise AssertionError("reconciled a store already on the current schema")
monkeypatch.setattr(db_module, "_reconcile_schema", _walked)
await _init(db_path)
@pytest.mark.asyncio
async def test_store_stamped_by_other_models_is_reconciled(tmp_path: Path) -> None:
"""A stamp another release wrote (an upgrade, a downgrade) reconciles and restamps."""
from repowise.core.persistence import database as db_module
db_path = tmp_path / "wiki.db"
await _init(db_path)
try:
_execute(db_path, 'ALTER TABLE "decision_records" DROP COLUMN "verification"')
except sqlite3.OperationalError as exc:
pytest.skip(f"SQLite build doesn't support DROP COLUMN: {exc}")
# Stamped after the drop, as a release whose models lack the column would.
_, cookie = _stamp_state(db_path)
_execute(db_path, f"PRAGMA user_version = {db_module.schema_stamp(cookie) % 1000 + 1}")
await _init(db_path)
assert "verification" in _table_columns(db_path, "decision_records")
user_version, cookie = _stamp_state(db_path)
assert user_version == db_module.schema_stamp(cookie)
@pytest.mark.asyncio
async def test_a_locked_store_opens_without_waiting_for_its_stamp(tmp_path: Path) -> None:
"""Stamping is the one write a current store's open adds; it must not block."""
import time
db_path = tmp_path / "wiki.db"
await _init(db_path)
_execute(db_path, "PRAGMA user_version = 0") # current schema, never stamped
writer = sqlite3.connect(db_path, isolation_level=None)
try:
writer.execute("BEGIN IMMEDIATE") # another update holding the write lock
started = time.monotonic()
await _init(db_path)
assert time.monotonic() - started < 10
finally:
writer.execute("ROLLBACK")
writer.close()
assert _stamp_state(db_path)[0] == 0