[Audit] Consolidate duplicated dialect/DB/chain helpers into shared modules

[stranske]

Why

The dual-dialect DB layer is implemented by per-module copies of the same helpers instead of shared functions, so every new flow re-implements (and can drift on) dialect handling. Verified copies at e5764a5:

• _placeholder(conn) / _is_sqlite(conn) (pick ? vs %s, isinstance(conn, sqlite3.Connection)) duplicated across 12 modules: etl/{activism_detection,activism_flow,conviction_flow,daily_diff_flow,digest_flow,edgar_flow,evaluation_flow,ingest_flow,news_flow}.py, api/{signals,chat,activism}.py.
• _columns / _get_columns (SQLite PRAGMA/pragma_table_info vs PG information_schema.columns) in etl/digest_flow.py:84, etl/edgar_flow.py:62, api/search.py:47.
• api_usage table DDL in etl/evaluation_flow.py:101, llm/cost_tracking.py:25, adapters/base.py:83/102.
• Chain helpers: _extract_json_text (chains/filing_summary.py:169, chains/holdings_analysis.py:155), _cursor_rows_to_dicts (same two), _guard_context/_acquire_connection (chains/rag_search.py, chains/nl_query.py), _normalize_cik (api/managers.py:245, scripts/seed_universe.py:23, scripts/resolve_aliases.py:29).

Est. ~210 LOC of duplication; the risk is divergence (a fix applied to one copy and not the others) — the same defect class behind several other audit findings.

Scope

Consolidate the duplicated dialect/DB/chain helpers into shared locations (adapters/base.py for DB; chains/utils.py for chain helpers; a utils normalizer for CIK) and update call sites to import them.

Non-Goals

• Do NOT change runtime behavior — this is a behavior-preserving consolidation.
• Do NOT alter the check_dialect_portability.py gate semantics.

Tasks

• Add is_sqlite(conn), get_placeholder(conn), get_table_columns(conn, table), and public ensure_api_usage_schema(conn) to adapters/base.py; replace the 12+3+3 call sites.
• Add extract_json_text() and rows_to_dicts() to chains/utils.py; replace the chain copies. Centralize _guard_context/_acquire_connection.
• Add a shared normalize_cik() and replace the 3 copies.

Acceptance Criteria

• Existing suite stays green; no behavior change.
• A grep gate (or test) asserts there is exactly one definition of each consolidated helper (no per-module re-definitions reintroduced).
• Deliberate-break demonstration: re-adding a local _placeholder copy in any module trips the single-definition gate.

Implementation Notes

Audit baseline: main @ e5764a5 on 2026-06-28. Call sites enumerated by the duplication sweep (Code/Audits/Manager-Database/2026-06-28-02-duplication-quality.md).

[stranske-keepalive]

✅ Capability Check: Agent can proceed

Recommendation: PROCEED

✅ Actionable Tasks (8):

• Add is_sqlite(conn), get_placeholder(conn), get_table_columns(conn, table), and public ensure_api_usage_schema(conn) to adapters/base.py.
• Replace the 12+3+3 existing call sites with the consolidated helpers in adapters/base.py.
• Add extract_json_text() and rows_to_dicts() to chains/utils.py.
• Replace duplicated chain-local copies with the shared helpers from chains/utils.py.
• Centralize _guard_context and _acquire_connection if the target locations and intended shared behavior are already clear from the codebase.
• Add a shared normalize_cik() helper and replace the 3 duplicated copies.
• Add a grep-based test or repository test that asserts exactly one definition of each consolidated helper exists.
• Add a deliberate-break regression test demonstrating that reintroducing a local _placeholder helper causes the single-definition gate to fail, if this can be expressed as a normal repository test/script.

⚠️ Partial Tasks (4):

• Ensure Existing suite stays green; no behavior change.
  • Limitation: An agent can refactor carefully and run available local tests, but cannot guarantee full suite status in all CI environments or prove zero behavior change beyond the test coverage present.
• Centralize _guard_context/_acquire_connection.
  • Limitation: If multiple valid consolidation targets or public/private API choices exist, the agent can implement a reasonable refactor but may need human confirmation on the intended ownership/module boundary.
• Implement the single-definition grep gate.
  • Limitation: If the requirement specifically expects enforcement through CI workflow wiring or protected automation files, the agent may be unable to update .github/workflows/*.yml; it can still add a test/script within the repo.
• Demonstrate the deliberate-break case.
  • Limitation: An agent can add a test or script that would fail when a duplicate helper is reintroduced, but it cannot literally perform and retain a breaking change in the final branch; the demonstration may need to be documented or encoded as a test expectation.

👤 Human Actions Needed:

• Confirm the intended canonical module/location for _guard_context and _acquire_connection if not obvious from current architecture.
• Decide whether the single-definition gate should live as a normal test/script or be enforced in CI workflow configuration.
• If CI workflow changes are required for enforcement, a human must update protected workflow files.
• Review whether any helper naming/API visibility changes could affect downstream consumers despite the stated no-behavior-change goal.

---

Auto-generated by capability check

[stranske]

Merged PR #1361 and applied verify:compare. Reopening this source issue for the required post-merge verifier sequence; it should stay open until a durable verifier/provider-comparison PASS or an evidence-backed disposition is recorded.

[stranske-automation-bot]

Opened PR #1365 to engage Codex.

Keepalive mode: OFF

Next steps for the PR author:

• Comment @codex start (auto-posted when possible).
  Ask Codex to draft the plan.
• After Codex replies with the checklist, post the execution command below.
  This begins delivery and enables keepalive.

Execution command (copy into a standalone PR comment):

@codex ## Keepalive Next Task

Your objective is to satisfy the **Acceptance Criteria** by completing each **Task** within the defined **Scope**.

**This round you MUST:**
1. Implement actual code or test changes that advance at least one incomplete task toward acceptance.
2. Commit meaningful source code (.py, .yml, .js, etc.)—not just status/docs updates.
3. Mark a task checkbox complete ONLY after verifying the implementation works.
4. Focus on the FIRST unchecked task unless blocked, then move to the next.

**Guidelines:**
- Keep edits scoped to the current task rather than reshaping the entire PR.
- Use repository instructions, conventions, and tests to validate work.
- Prefer small, reviewable commits; leave clear notes when follow-up is required.
- Do NOT work on unrelated improvements until all PR tasks are complete.

## Pre-Commit Formatting Gate (Black)

Before you commit or push any Python (`.py`) changes, you MUST:
1. Run Black to format the relevant files (line length 100).
2. Verify formatting passes CI by running:
   `black --check --line-length 100 --exclude '(\.workflows-lib|node_modules)' .`
3. If the check fails, do NOT commit/push; format again until it passes.

**COVERAGE TASKS - SPECIAL RULES:**
If a task mentions "coverage" or a percentage target (e.g., "≥95%", "to 95%"), you MUST:
1. After adding tests, run TARGETED coverage verification to avoid timeouts:
   - For a specific script like `scripts/foo.py`, run:
     `pytest tests/scripts/test_foo.py --cov=scripts/foo --cov-report=term-missing -m "not slow"`
   - If no matching test file exists, run:
     `pytest tests/ --cov=scripts/foo --cov-report=term-missing -m "not slow" -x`
2. Find the specific script in the coverage output table
3. Verify the `Cover` column shows the target percentage or higher
4. Only mark the task complete if the actual coverage meets the target
5. If coverage is below target, add more tests until it meets the target

IMPORTANT: Always use `-m "not slow"` to skip slow integration tests that may timeout.
IMPORTANT: Use targeted `--cov=scripts/specific_module` instead of `--cov=scripts` for faster feedback.

A coverage task is NOT complete just because you added tests. It is complete ONLY when the coverage command output confirms the target is met.

**The Tasks and Acceptance Criteria are provided in the appendix below.** Work through them in order.

Issue context (optional: copy into the PR description):

### Source Issue #1313: [Audit] Consolidate duplicated dialect/DB/chain helpers into shared modules
Keepalive: OFF
Base: main
Head: codex/issue-1313

Source: https://github.com/stranske/Manager-Database/issues/1313

> ## Why
> 
> The dual-dialect DB layer is implemented by **per-module copies** of the same helpers instead of shared functions, so every new flow re-implements (and can drift on) dialect handling. Verified copies at `e5764a5`:
> 
> - `_placeholder(conn)` / `_is_sqlite(conn)` (pick `?` vs `%s`, `isinstance(conn, sqlite3.Connection)`) duplicated across **12** modules: `etl/{activism_detection,activism_flow,conviction_flow,daily_diff_flow,digest_flow,edgar_flow,evaluation_flow,ingest_flow,news_flow}.py`, `api/{signals,chat,activism}.py`.
> - `_columns` / `_get_columns` (SQLite `PRAGMA`/`pragma_table_info` vs PG `information_schema.columns`) in `etl/digest_flow.py:84`, `etl/edgar_flow.py:62`, `api/search.py:47`.
> - `api_usage` table DDL in `etl/evaluation_flow.py:101`, `llm/cost_tracking.py:25`, `adapters/base.py:83/102`.
> - Chain helpers: `_extract_json_text` (`chains/filing_summary.py:169`, `chains/holdings_analysis.py:155`), `_cursor_rows_to_dicts` (same two), `_guard_context`/`_acquire_connection` (`chains/rag_search.py`, `chains/nl_query.py`), `_normalize_cik` (`api/managers.py:245`, `scripts/seed_universe.py:23`, `scripts/resolve_aliases.py:29`).
> 
> Est. ~210 LOC of duplication; the risk is divergence (a fix applied to one copy and not the others) — the same defect class behind several other audit findings.
> 
> ## Scope
> 
> Consolidate the duplicated dialect/DB/chain helpers into shared locations (`adapters/base.py` for DB; `chains/utils.py` for chain helpers; a `utils` normalizer for CIK) and update call sites to import them.
> 
> ## Non-Goals
> 
> - Do NOT change runtime behavior — this is a behavior-preserving consolidation.
> - Do NOT alter the `check_dialect_portability.py` gate semantics.
> 
> ## Tasks
> 
> - [ ] Add `is_sqlite(conn)`, `get_placeholder(conn)`, `get_table_columns(conn, table)`, and public `ensure_api_usage_schema(conn)` to `adapters/base.py`; replace the 12+3+3 call sites.
> - [ ] Add `extract_json_text()` and `rows_to_dicts()` to `chains/utils.py`; replace the chain copies. Centralize `_guard_context`/`_acquire_connection`.
> - [ ] Add a shared `normalize_cik()` and replace the 3 copies.
> 
> ## Acceptance Criteria
> 
> - [ ] Existing suite stays green; no behavior change.
> - [ ] A grep gate (or test) asserts there is exactly one definition of each consolidated helper (no per-module re-definitions reintroduced).
> - [ ] **Deliberate-break demonstration:** re-adding a local `_placeholder` copy in any module trips the single-definition gate.
> 
> ## Implementation Notes
> 
> Audit baseline: `main` @ `e5764a5` on 2026-06-28. Call sites enumerated by the duplication sweep (`Code/Audits/Manager-Database/2026-06-28-02-duplication-quality.md`).
> 

[stranske]

Closer verifier disposition: merged PR #1361 has verify:compare, the post-merge verifier check completed successfully at 2026-07-01T21:32:40Z, and the durable Provider Comparison Report on #1361 reports OpenAI PASS and Anthropic PASS for the shared dialect/DB/chain helper consolidation. No unresolved review threads or follow-up debt remain, so this issue is complete.