[Audit] Form-4 insider ingestion + conviction net-direction annotation (edgartools)

[stranske]

Why

Implementation of the Form-4 (insider) slice of design issue #1404 (options/short/Form-4 cross-reference). 13F shows only long equity, so the conviction signal is structurally biased. Insider (Form 3/4/5) activity on the issuers managers hold is the cheapest de-bias signal and needs no new vendor: edgartools (pinned edgartools==5.43.0, requirements.txt:6) is already adopted for 13F (adapters/edgar.py:_parse_13f_with_edgartools, uses edgar.thirteenf) and provides Form 3/4/5 parsing. Verified absent at main @ 705e427: no form.?4/insider anywhere in adapters/, etl/, api/, chains/, schema.sql.

Decision resolved (was open in #1404): do the Form-4/insider slice first (edgartools, no new data feed); short-interest + options are deferred to #1404.

Scope

Ingest recent insider (Form 4) transactions for the issuers of securities managers hold, persist them, and annotate conviction/signals with an insider net-direction. Phase 1 = ingest + store + annotate; do NOT re-derive conviction scoring.

Non-Goals

• Do NOT add short-interest or options here (separate slices under [Design] Options / short-interest / Form-4 cross-reference (de-bias conviction) #1404).
• Do NOT change the conviction_scores computation (etl/conviction_flow.py:compute_conviction_scores) — annotate/join, don't recompute.
• Must degrade gracefully: an issuer whose insider data can't be fetched must NOT drop or block conviction rows (finite/robust per [Audit] Add finite/bounds validation at numeric trust boundaries (NaN/inf) #1299 discipline).

Tasks

• Add adapters/insider.py: fetch + normalize Form 4 insider transactions for an issuer (by CIK/ticker) via edgartools' ownership/Form-4 API (confirm the exact entry point, e.g. edgar ownership module, mirroring how adapters/edgar.py wraps edgar.thirteenf). Preserve a stable row contract (issuer_cik, ticker, insider_name, txn_code, shares, txn_date, acquired_disposed).
• Add insider_transactions table (dialect-aware DDL + Alembic migration + schema.sql, mirroring conviction_scores; add to parity gate [Audit] Add schema.sql<->Alembic parity gate; close gin-index/embedding/nullability drift #1308).
• Add etl/insider_flow.py: resolve held securities → issuer (via holdings.resolved_ticker/resolved_figi from OpenFIGI Add OpenFIGI CUSIP resolution layer #1374, or edgartools company lookup); pull recent Form-4 for those issuers respecting the SEC fair-use governor (adapters/edgar.py _request_with_retry); upsert into insider_transactions.
• Expose an insider net-direction annotation: a derived per-(manager,cusip) "insiders net buying/selling over last N days" available to api/signals.py (routes at api/signals.py:313+) and/or as a field joined to conviction output. Read-only; no conviction recompute.

Acceptance Criteria

• New test (stubbed edgartools): a fake issuer returning 2 insider buys + 1 sell → insider_transactions gets 3 rows and the net-direction annotation for that issuer is "net buy".
• Robustness test: an issuer whose insider fetch raises → conviction rows for that manager are unaffected (no drop, no exception escaping the flow) and the annotation is null/"unknown".
• Dialect parity: insider_transactions present on both SQLite (Alembic) and Postgres (schema.sql) paths (gate [Audit] Add schema.sql<->Alembic parity gate; close gin-index/embedding/nullability drift #1308 green).
• Deliberate-break demonstration: removing the graceful-failure guard makes the robustness test fail (exception escapes / rows dropped); restoring passes.

Implementation Notes

Audit baseline main @ 705e427. Anchors: edgartools adopted (adapters/edgar.py:192 _parse_13f_with_edgartools), governor adapters/edgar.py:_request_with_retry, conviction_scores schema.sql:315 + etl/conviction_flow.py:compute_conviction_scores, resolved identifiers holdings.resolved_ticker/figi (schema.sql:135-137), signals API api/signals.py. First slice of #1404 / epic #1320; short-interest + options remain in #1404.

[stranske-keepalive]

✅ Capability Check: Agent can proceed

Recommendation: PROCEED

✅ Actionable Tasks (9):

• Add adapters/insider.py: implement fetch + normalize for Form 4 insider transactions for an issuer using edgartools, preserving the stable row contract (issuer_cik, ticker, insider_name, txn_code, shares, txn_date, acquired_disposed).
• Add insider_transactions table across code paths: dialect-aware DDL, Alembic migration, and schema.sql, mirroring existing patterns such as conviction_scores.
• Add parity-gate coverage for insider_transactions in the existing schema parity mechanism referenced by gate [Audit] Add schema.sql<->Alembic parity gate; close gin-index/embedding/nullability drift #1308, to the extent this is repository code rather than protected workflow config.
• Add etl/insider_flow.py: resolve held securities to issuer via available holdings fields (resolved_ticker/resolved_figi) and/or existing edgartools company lookup, fetch recent Form 4 data, and upsert into insider_transactions.
• Implement SEC fair-use / retry behavior in the insider ETL by reusing or mirroring the request governor approach already present in adapters/edgar.py.
• Expose a read-only insider net-direction annotation for per-(manager, cusip) usage in api/signals.py and/or as a joined field alongside conviction output, without triggering conviction recomputation.
• Add a stubbed-edgartools test where a fake issuer returns 2 buys and 1 sell, asserting that insider_transactions receives 3 rows and the derived annotation is net buy.
• Add a robustness test where insider fetch raises, asserting conviction rows remain unaffected and the annotation becomes null/unknown rather than failing the flow.
• Add a deliberate-break demonstration test pattern or code path proving that removing the graceful-failure guard causes the robustness test to fail, and restoring the guard makes it pass.

⚠️ Partial Tasks (2):

• Confirm the exact edgartools Form 4 / ownership API entry point to mirror in adapters/insider.py.
  • Limitation: The agent can inspect installed/library usage already present in the repository and implement against known interfaces, but cannot reliably validate live external package behavior beyond what is available locally. If the repository does not pin or already use the required edgartools ownership/Form-4 entry point, final correctness may depend on package-version specifics.
• Ensure gate [Audit] Add schema.sql<->Alembic parity gate; close gin-index/embedding/nullability drift #1308 is green for dialect parity.
  • Limitation: The agent can update repository code, migrations, schema files, and tests, but cannot modify protected workflow files if parity enforcement lives there, and cannot rerun or guarantee external CI success.

👤 Human Actions Needed:

• If edgartools ownership/Form-4 API usage is not already established in the repo, verify the intended package version and exact entry point/interface.
• If parity gate [Audit] Add schema.sql<->Alembic parity gate; close gin-index/embedding/nullability drift #1308 depends on CI/workflow behavior outside normal repository code, a human may need to confirm workflow compatibility or review CI results after the code changes land.
• If there is ambiguity about where the insider net-direction annotation should surface (api/signals.py only vs also joined into conviction output), provide a product-level decision to avoid implementation churn.

---

Auto-generated by capability check

[stranske-automation-bot]

Opened PR #1462 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 #1461: [Audit] Form-4 insider ingestion + conviction net-direction annotation (edgartools)
Keepalive: OFF
Base: main
Head: codex/issue-1461

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

> ## Why
> 
> Implementation of the **Form-4 (insider) slice** of design issue #1404 (options/short/Form-4 cross-reference). 13F shows only long equity, so the `conviction` signal is structurally biased. Insider (Form 3/4/5) activity on the *issuers* managers hold is the cheapest de-bias signal and needs **no new vendor**: `edgartools` (pinned `edgartools==5.43.0`, `requirements.txt:6`) is already adopted for 13F (`adapters/edgar.py:_parse_13f_with_edgartools`, uses `edgar.thirteenf`) and provides Form 3/4/5 parsing. Verified absent at `main` @ `705e427`: no `form.?4`/`insider` anywhere in `adapters/`, `etl/`, `api/`, `chains/`, `schema.sql`.
> 
> Decision resolved (was open in #1404): do the **Form-4/insider slice first** (edgartools, no new data feed); short-interest + options are deferred to #1404.
> 
> ## Scope
> 
> Ingest recent insider (Form 4) transactions for the issuers of securities managers hold, persist them, and annotate conviction/signals with an insider net-direction. Phase 1 = ingest + store + annotate; do NOT re-derive conviction scoring.
> 
> ## Non-Goals
> 
> - Do NOT add short-interest or options here (separate slices under #1404).
> - Do NOT change the `conviction_scores` computation (`etl/conviction_flow.py:compute_conviction_scores`) — annotate/join, don't recompute.
> - Must degrade gracefully: an issuer whose insider data can't be fetched must NOT drop or block conviction rows (finite/robust per #1299 discipline).
> 
> ## Tasks
> 
> - [ ] Add `adapters/insider.py`: fetch + normalize Form 4 insider transactions for an issuer (by CIK/ticker) via edgartools' ownership/Form-4 API (confirm the exact entry point, e.g. `edgar` ownership module, mirroring how `adapters/edgar.py` wraps `edgar.thirteenf`). Preserve a stable row contract (`issuer_cik`, `ticker`, `insider_name`, `txn_code`, `shares`, `txn_date`, `acquired_disposed`).
> - [ ] Add `insider_transactions` table (dialect-aware DDL + Alembic migration + `schema.sql`, mirroring `conviction_scores`; add to parity gate #1308).
> - [ ] Add `etl/insider_flow.py`: resolve held securities → issuer (via `holdings.resolved_ticker`/`resolved_figi` from OpenFIGI #1374, or edgartools company lookup); pull recent Form-4 for those issuers respecting the SEC fair-use governor (`adapters/edgar.py` `_request_with_retry`); upsert into `insider_transactions`.
> - [ ] Expose an insider net-direction annotation: a derived per-(manager,cusip) "insiders net buying/selling over last N days" available to `api/signals.py` (routes at `api/signals.py:313+`) and/or as a field joined to conviction output. Read-only; no conviction recompute.
> 
> ## Acceptance Criteria
> 
> - [ ] **New test (stubbed edgartools):** a fake issuer returning 2 insider buys + 1 sell → `insider_transactions` gets 3 rows and the net-direction annotation for that issuer is "net buy".
> - [ ] **Robustness test:** an issuer whose insider fetch raises → conviction rows for that manager are unaffected (no drop, no exception escaping the flow) and the annotation is null/"unknown".
> - [ ] **Dialect parity:** `insider_transactions` present on both SQLite (Alembic) and Postgres (`schema.sql`) paths (gate #1308 green).
> - [ ] **Deliberate-break demonstration:** removing the graceful-failure guard makes the robustness test fail (exception escapes / rows dropped); restoring passes.
> 
> ## Implementation Notes
> 
> Audit baseline `main` @ `705e427`. Anchors: edgartools adopted (`adapters/edgar.py:192` `_parse_13f_with_edgartools`), governor `adapters/edgar.py:_request_with_retry`, `conviction_scores` `schema.sql:315` + `etl/conviction_flow.py:compute_conviction_scores`, resolved identifiers `holdings.resolved_ticker/figi` (`schema.sql:135-137`), signals API `api/signals.py`. First slice of #1404 / epic #1320; short-interest + options remain in #1404.
>