alembic/ already ships with env.py (target_metadata = Base.metadata)
and versions/0001_initial_schema.py covering all 26 tables. Production
deploys run `alembic upgrade head`; this commit gates the existing
`Base.metadata.create_all(_engine)` in db.py behind PRY_ALEMBIC_AUTO.
Default: PRY_ALEMBIC_AUTO=1 (auto-create, current behavior — tests pass).
Set PRY_ALEMBIC_AUTO=0 in production env so schema management lives in
migrations/ only and no surprise DDL happens at import time.
Tests: db tests pass under default; verified opt-out path is wired.
Replaces the 12 ad-hoc JSON file stores (quality, intel, monitors,
sessions, accounts, agency, etc.) with a single SQLAlchemy-backed
database. The new foundation gives us:
- Concurrency safety (SQLite WAL mode, file locks via SQLAlchemy)
- Transactions (rollback on error)
- Querying (WHERE, JOIN, ORDER BY, LIMIT)
- Relationships (ForeignKey on monitor_id, agency_id, etc.)
- Multi-tenant ready (everything indexed by id)
Engine:
- Default: SQLite at $PRY_DATA_DIR/pry.db (zero-config)
- Production: set PRY_DATABASE_URL=postgresql://... (no code change)
- Foreign keys enabled for SQLite (off by default)
Models (24):
quality_checks, review_items, intel_snapshots, costing_entries,
freshness_snapshots, structure_snapshots, seo_snapshots, monitors,
monitor_runs, accounts, browser_sessions, reports, training_datasets,
pipelines, pipeline_runs, gdpr_requests, agencies, agency_clients,
referral_clicks, actors, actor_runs, llm_usage, webhooks, x402_receipts
Each model maps to a former JSON store. Most have an _id field with
unique constraint so re-importing the same data is safe. The legacy
"id" and "name" fields are renamed to "<scope>_id" / "<scope>_name"
to avoid reserved LogRecord field name collisions.
JSON importer (import_json_stores):
One-shot function that reads the existing JSON files in $PRY_DATA_DIR
and writes them to the SQL tables. Returns a {store: count} dict.
Idempotent: re-running with the same data is safe.
Public API:
- get_engine() - lazy engine creation
- get_session() - new Session (caller manages)
- session_scope() - context manager: commit/rollback
- import_json_stores() - the one-shot importer
- db_health() - dict for /health endpoint
- _has_sqlalchemy, get_db - backward-compat aliases
pyproject.toml: added sqlalchemy>=2.0.0 and aiosqlite>=0.19.0
Tests: 7/7 in tests/test_db.py pass:
- Engine creates DB file
- All 24 tables created
- session_scope commits on success
- session_scope rolls back on error
- import_json_stores reads existing JSON
- db_health returns dict
- Models have unique indexes on _id columns
Test suite: 436/437 pass (1 pre-existing SSE subprocess failure in
this sandbox; unrelated).
Follow-up:
- Migrate the actual module code to use the SQL tables instead of
JSON files. Each module (quality.py, intelligence.py, monitors.py,
etc.) needs a SQL-backed replacement. Estimated 4-6 hours.
- Add Alembic for schema migrations instead of create_all().
- Add Postgres-specific tuning when PRY_DATABASE_URL is set.
Per CONVENTIONS.md Part 2 ("Never bare except") and CONVENTIONS.md
Part 7 (pre-commit hooks: ruff), blind `except Exception` is now a
lint failure. Pre-existing sites are marked `# noqa: BLE001` for
later manual review; new code must use specific exception types.
Changes:
- pyproject.toml: added "BLE" to ruff lint select. BLE001 is now enforced
- 103 of 166 `except Exception` sites were auto-converted to specific
types based on context (httpx, json, OSError, subprocess, etc.)
- 62 remaining sites marked with `# noqa: BLE001` for later review
(mostly generic try/except wrappers that legitimately need broad catch
for graceful degradation: e.g. compliance LLM fallback must catch
any error to preserve the regex result)
- 1 manual fix: reverted compliance.py LLM fallback to broad except
with explicit "must catch all errors" comment + noqa
- 2 files (commerce_sync.py, crm_sync.py) needed `import httpx` added
so the auto-converted exception references would resolve
- 5 source files (agency, monitor, pipelines, auth_connector,
llm_providers/registry) renamed "name" -> "<scope>_name" in
extra={...} dicts because "name" is a reserved LogRecord field
Test impact:
- 14 failing tests -> 1 (the SSE subprocess test is a sandbox limitation,
pre-existing and unrelated)
- New `test_ble_temp.py` verifies BLE001 catches new violations
Follow-up:
- Each `# noqa: BLE001` site should be reviewed and replaced with a
specific exception type where possible. The most common legitimate
broad-catch case is the LLM fallback path; everything else probably
can be narrowed.
Squashed from chore/license-relicense. Full message preserved in the
original branch commit bb77eb5. See ADR-0002 for the decision rationale.
Refs: ADR-0002, commit bb77eb5
