Operator guide for day-1 SQLite storage, Status triage, calibration, create caps, keyword ownership, alerts, cache re-runs, Google Sheets view, SharePoint activation, and rollback.
Audience: operators who review opportunities and manage the daily schedule.
Engineering owns: config/keywords.yaml, pipeline code, GitHub workflow YAML.
First-time plug-in (keys, Grok, Teams, function list, roadmap): PLUG_AND_PLAY.md.
What this app is (processor role, triggers, flow): PROCESSOR_OVERVIEW.md.
LLM / data rules: AGENTS.md, DATA_UPDATE_DIRECTIVES, AS_BUILT.
Related: README.md, google_sheets_setup.md, provision_sharepoint_list.md, daily_sync.ps1, BACKLOG.md.
1. run --write → SQLite system of record (create-only)
2. sync-sheets → optional full-replace of Google tab "Ingest"
3. interpret-rank → optional Grok plain-English + fit rank
(writes data/rankings/*; full-replaces Sheet tab "Ranked"
when GOOGLE_SHEET_ID set — use --no-sync-sheets to skip)
4. export-csv → optional Excel snapshot
5. Human Status triage → SQLite / export / Sheet Review tab (not Ingest if auto-synced)
Use Ranked tab for capture prioritization; do not treat fit_score
as store RelevanceScore
Windows automation sample: daily_sync.ps1 (Task Scheduler).
Not built yet: website-type keyword packs, auto keyword suggestions — see docs/BACKLOG.md.
Ingest is create-only: new rows land with Status = New. Humans own Status and Notes after create. The pipeline never overwrites existing rows.
| Status | Meaning |
|---|---|
New |
Just ingested; needs triage |
Reviewing |
Under evaluation |
Relevant |
Fits our capabilities |
Bidding |
Active pursuit |
Discarded |
Not a fit |
# Default path under DATA_DIR (typically data/export-opportunities.csv)
python -m opportunity_ingest export-csv
# Explicit path
python -m opportunity_ingest export-csv --out data/export-opportunities.csvOpen the CSV in Excel or Power BI. Sort by RelevanceScore (desc) and filter Status = New (or exclude Discarded).
export-csvis supported forSTORAGE_BACKEND=sqliteonly. SharePoint review uses the list UI after activation.
pip install -e ".[sheets]"
python -m opportunity_ingest sync-sheets- Setup: google_sheets_setup.md
- Tab
Ingest: opportunity rows from SQLite (full replace). - Tab
Ranked: Grok ranking (full replace viainterpret-rank/sync-rank-sheets). - Tab
Review: human-only if you create it — pipeline does not write it.
Requires pip install -e ".[ai]", root .env XAI_API_KEY, and eng-owned config/objectives.yaml.
python -m opportunity_ingest interpret-rank --status New --limit 20
# Offline reports only:
python -m opportunity_ingest interpret-rank --limit 20 --no-sync-sheets
# Re-push last ranking JSON to Ranked tab (no Grok call):
python -m opportunity_ingest sync-rank-sheets- Local files:
data/rankings/interpret-*.md(and.json). - Does not change SQLite Status, Notes, or RelevanceScore.
- Ingest tab is full-replaced every sync — put manual notes on a separate Review tab
- SQLite remains system of record; Sheets is a derived view
- Normative rules: DATA_UPDATE_DIRECTIVES.md §3
Default DB: data/contract_opportunities.db (or SQLITE_PATH / {DATA_DIR}/contract_opportunities.db).
# Queue: non-discarded, highest score first
sqlite3 data/contract_opportunities.db "
SELECT OpportunityID, Title, ClosingDate, RelevanceScore, Status, Link
FROM contract_opportunities
WHERE Status != 'Discarded'
ORDER BY RelevanceScore DESC, ClosingDate ASC;
"
# Only New
sqlite3 data/contract_opportunities.db "
SELECT OpportunityID, Title, ClosingDate, RelevanceScore, Link
FROM contract_opportunities
WHERE Status = 'New'
ORDER BY RelevanceScore DESC;
"# Mark reviewing
sqlite3 data/contract_opportunities.db "
UPDATE contract_opportunities
SET Status = 'Reviewing', Notes = 'Assigned to capture team'
WHERE OpportunityID = 'YOUR-REF-NUMBER';
"
# Discard noise
sqlite3 data/contract_opportunities.db "
UPDATE contract_opportunities
SET Status = 'Discarded', Notes = 'Out of scope'
WHERE OpportunityID = 'YOUR-REF-NUMBER';
"Prefer updating by OpportunityID (unique). Avoid bulk Status changes unless you intend to.
Scheduled runs use .github/workflows/daily-canadabuys-ingest.yml (Daily CanadaBuys Opportunity Ingest). Each run uploads a recovery artifact (data/*.db, state/, logs/); cache of data/ + state/ is best-effort continuity only.
To triage remotely: download the latest run artifact, then open the DB or run:
# Point at the downloaded DB
set SQLITE_PATH=path/to/downloaded/contract_opportunities.db # Windows cmd
# export SQLITE_PATH=... # Unix
python -m opportunity_ingest export-csv --out data/export-from-artifact.csvCanadaBuys notices can amend after first publication (closing date, text, etc.). This pipeline:
- Dedupes on OpportunityID and Link
- Creates only; never updates existing rows
- Therefore stored ClosingDate / Title / Description can go stale if the source amends the same notice
Operator rule: for any opportunity still in play (Reviewing / Relevant / Bidding), open the Link on CanadaBuys for the latest closing date and notice text. Do not treat the DB ClosingDate as authoritative after first create.
Same OpportunityID reappearing on a later ingest is skipped as duplicate — amendments will not refresh the row automatically.
Before relying on the daily schedule at full volume, calibrate against live data.
# Live download (default dry-run — no store reads, no writes)
python -m opportunity_ingest run
# Offline sample (two-step)
python -m opportunity_ingest download-sample
# Default path: data/sample-openTenderNotice.csv
python -m opportunity_ingest run --csv data/sample-openTenderNotice.csv
# Or any local open-tender CSV / fixture
python -m opportunity_ingest run --csv tests/fixtures/open_tender_sample.csvRecord from the CLI summary line (printed after every run):
| Metric | Meaning |
|---|---|
parsed |
Rows read from the CSV |
filtered |
Keyword hits (filtered_count) — primary volume signal for MAX_CREATE policy |
mapped |
Successfully mapped candidates (map errors are separate) |
would_create |
On dry-run: candidates that would be created under the attempt budget. Default dry-run does not load store keys, so every mapped candidate is treated as new (intra-run dups still skipped). Not zero just because the store was unused. |
skipped_dup |
Duplicate OpportunityID/Link (intra-run, or vs loaded keys) |
skipped_max |
Eligible new candidates left unattempted because the attempt budget was exhausted |
added |
Actual creates — only increments on --write (stays 0 on dry-run) |
Dry-run with existing store keys (still no writes; store must be available):
# Live feed + load keys from configured store
python -m opportunity_ingest run --with-existing
# Offline sample + load keys
python -m opportunity_ingest run --csv data/sample-openTenderNotice.csv --with-existingWith --with-existing, would_create / skipped_dup reflect dedupe against load_existing_keys() — use this to estimate true new volume vs a populated store. --with-existing is dry-run only (CLI rejects it with --write).
Links are stored as full plain-text URLs and are never silent-truncated. Day-1 mapping does not hard-skip long Links (multi-line TEXT / multi-line SharePoint field); still measure max(len(link)) so ops/eng know live URL size.
After download-sample (or any open-tender CSV):
python -c "import csv; from pathlib import Path; p=Path('data/sample-openTenderNotice.csv');
rows=list(csv.DictReader(p.open(encoding='utf-8-sig', newline='')));
lens=[]
for row in rows:
link=(row.get('noticeURL-URLavis-eng') or row.get('noticeURL-URLavis-fra') or '').strip()
if link: lens.append(len(link))
print(f'urls={len(lens)} max_len={max(lens) if lens else 0}')"Optional after a smoke write + export (stored Link column):
python -m opportunity_ingest export-csv --out data/export-opportunities.csv
python -c "import csv; from pathlib import Path; p=Path('data/export-opportunities.csv');
lens=[len((r.get('Link') or '').strip()) for r in csv.DictReader(p.open(encoding='utf-8-sig', newline='')) if (r.get('Link') or '').strip()];
print(f'stored={len(lens)} max_len={max(lens) if lens else 0}')"If max length is unexpectedly large or tooling breaks on long URLs, open a GitHub issue for engineering — not an ops-only keyword tweak.
python -m opportunity_ingest run --write --max-create 10
python -m opportunity_ingest export-csv --out data/export-smoke.csv
python -m opportunity_ingest check-storeConfirm:
- New rows appear with
Status = New - Duplicates on re-run are skipped
skipped_maxincreases if more than 10 new candidates exist
Enable / leave enabled the daily workflow (.github/workflows/daily-canadabuys-ingest.yml) with:
| Setting | Day-1 value |
|---|---|
STORAGE_BACKEND |
sqlite |
INGEST_MAX_CREATE (GitHub variable) |
50 |
TEAMS_WEBHOOK_URL |
set (strongly recommended) |
Use workflow_dispatch max_create / dry_run inputs for controlled re-runs during calibration.
MAX_CREATE / INGEST_MAX_CREATE is a create-attempt budget per run (success or failure both consume an attempt). 0 means unlimited. Unattempted new candidates increment skipped_max_create_count.
| Stage | Cap | When |
|---|---|---|
| Default | 50 | Always start here for scheduled runs |
| Raise | 100 | Typical filtered volume < 30/day (after calibration, multiple quiet-but-stable days) |
| Unlimited | 0 | Only after 7 consecutive dry-runs each showing < 50 filtered candidates/day |
Do not uncap (0) on a single quiet day. Prefer raising 50 → 100 first.
- GitHub Actions variable:
INGEST_MAX_CREATE(repo or environment variable). - Local / one-off:
MAX_CREATEenv or CLI--max-create N. - Workflow dispatch: optional
max_createinput overrides the variable for that run.
After any raise, watch skipped_max on the next few scheduled runs. Persistent non-zero skipped_max with healthy error counts means the cap is binding — either raise per policy or triage keyword noise.
| Role | Responsibility |
|---|---|
| Ops | File a GitHub issue describing false positives/negatives (examples: OpportunityID, title snippet, expected behavior). |
| Engineering | Owns config/keywords.yaml PRs, review, merge, and release to the schedule. |
Ops must not commit keyword edits on shared branches without eng review. Keyword floods are mitigated by eng ownership plus MAX_CREATE.
Suggested issue template fields:
- Sample titles / refs that should match or should not
- Whether this is precision (too much noise) or recall (missed tenders)
- Urgency (blocking bid deadline?)
- File:
state/zero_new_streak.json(override withSTATE_PATH) - Updated only on successful
--writeruns (not dry-run) - Counts UTC calendar days with zero new creates; same-day re-runs do not double-count
- Threshold:
ZERO_NEW_STREAK_THRESHOLD(default 3)
When streak ≥ threshold after a write with added == 0, Python posts a Teams alert (notify_reason=zero_new_streak) and exits 0.
| Condition | Exit | Notify |
|---|---|---|
| Hard fail (download/parse/config/store health) | 1 | Yes |
Create error_count ≥ PARTIAL_ERROR_EXIT_THRESHOLD (default 5) |
1 | Yes |
| Zero-new streak ≥ threshold | 0 | Yes |
| Soft partial errors below threshold | 0 | No |
| Successful adds | 0 | No |
Primary notify is Python via TEAMS_WEBHOOK_URL. Actions may backup-notify only if the job failed and Python did not set notified=true (avoids double-notify for handled failures).
- Open the failing Actions run URL (included when
GITHUB_RUN_URLis set). - Check run JSON under
logs/run-*.jsonand workflow logs. - For zero-new streak: confirm CanadaBuys feed is live, keywords still sensible, and store is not accidentally full of false dups — then file eng issue if needed.
- Smoke-test webhook once before go-live (POST Adaptive Card via Workflows template).
Workflow: .github/workflows/daily-canadabuys-ingest.yml.
Daily job restores/saves data/ (SQLite) and state/ via GitHub Actions cache:
- Cache key prefix:
canadabuys-ingest- - Key includes
github.run_idandgithub.run_attempt - Restore-keys fall back to prior
canadabuys-ingest-${{ runner.os }}-entries - Cache save is best-effort (
continue-on-error) so a failed save on re-run does not fail the whole job - Per-run artifact (
data/*.db,state/,logs/) is the recovery path if cache is missing or stale
GitHub Actions cache keys are immutable once written. A job re-run gets a new run_attempt. Using run_id + run_attempt avoids “cache already exists” failures that would block persistence of updated SQLite/streak state.
- Prefer workflow_dispatch (optional
max_create/dry_runinputs) for intentional re-runs during incidents. - After a re-run, confirm the latest artifact reflects new rows (
export-csvorcheck-storeagainst the downloaded DB). - If cache restore yields an empty/old DB, restore from a recent successful run’s uploaded artifact or a known-good local backup — see Rollback.
Day-1 production path is SQLite. SharePoint is a pluggable OpportunityStore backend: set STORAGE_BACKEND=sharepoint and the Azure/SharePoint secrets below when Entra, site, and list are ready. Until then, leave STORAGE_BACKEND=sqlite (no Azure secrets required).
-
Complete scripts/provision_sharepoint_list.md (list creation, site/list IDs, Entra app,
Sites.Selectedgrant):- Create list Contract Opportunities (UI source of truth; multi-line plain-text Link — not Hyperlink column)
- Resolve site/list IDs
- Entra app +
Sites.Selected+ admin consent - Chicken-and-egg site permission grant (
roles: ["write"]) to the ingest app
-
Set GitHub secrets (or environment secrets):
Secret Purpose AZURE_TENANT_IDEntra tenant AZURE_CLIENT_IDApp id AZURE_CLIENT_SECRETApp secret SHAREPOINT_SITE_IDGraph site id SHAREPOINT_LIST_IDList GUID -
Set
STORAGE_BACKEND=sharepointon the daily workflow env (and local.envfor smoke tests). KeepINGEST_MAX_CREATE/ soft cap policy from §4. -
Smoke (with secrets present and backend flipped):
python -m opportunity_ingest check-store python -m opportunity_ingest run --write --max-create 5
check-storeperforms backend health + sample key load against Graph whenSTORAGE_BACKEND=sharepoint. -
Optional one-time migration: export SQLite CSV → manual import or scripted creates. No dual-write in Phase 1 — switch backends; do not write both.
-
Human Status triage after flip: use SharePoint list views.
export-csvremains the sqlite review path.
Do not require SharePoint for day-1 go-live.
- GitHub Actions → workflow Daily CanadaBuys Opportunity Ingest → ⋯ → Disable workflow
(or remove/comment theschedule:cron and merge). - Optionally cancel any in-progress run.
- Download a known-good
data/artifact or restore from backup ofcontract_opportunities.db. - Replace the active DB path (
DATA_DIR/SQLITE_PATH). - Re-enable schedule only after
check-storeand a dry-run look healthy.
If SharePoint activation misbehaves:
- Set
STORAGE_BACKEND=sqliteagain on the workflow. - Confirm Azure secrets are no longer required for the job.
- Investigate SP list/permissions offline via the provisioning runbook — do not leave the schedule on a broken backend.
Limit blast radius without disabling the workflow:
- Dispatch with
dry_run=true(no writes), or - Dispatch with a low
max_createoverride (10 or 25), or temporarily lower repo variableINGEST_MAX_CREATEto 10 / 25.
Never use 0 for containment. MAX_CREATE / INGEST_MAX_CREATE=0 means unlimited create attempts (see §4). Unlimited is only appropriate after the steady-state policy criteria, not during an incident.
python -m opportunity_ingest run [--write | --dry-run] [--csv PATH] [--max-create N] [--with-existing]
python -m opportunity_ingest download-sample [--out PATH]
python -m opportunity_ingest check-store
python -m opportunity_ingest export-csv [--out PATH]
python -m opportunity_ingest sync-sheets [--sheet-id ID] [--tab NAME]
python -m opportunity_ingest interpret-rank [--status S] [--limit N] [--sync-sheets|--no-sync-sheets]
python -m opportunity_ingest sync-rank-sheets [--from-json PATH] [--rank-tab NAME]
Only --write on run persists opportunities. Default (and --dry-run) never writes store rows. DRY_RUN env never enables write alone.
interpret-rank / sheet commands never UPDATE opportunity rows.
| Name | Default | Notes |
|---|---|---|
STORAGE_BACKEND |
sqlite |
sharepoint when activated |
DATA_DIR |
data |
SQLite + exports + rankings |
MAX_CREATE / INGEST_MAX_CREATE |
50 |
Attempt budget; 0 = unlimited |
ZERO_NEW_STREAK_THRESHOLD |
3 |
Teams after N zero-new UTC days |
PARTIAL_ERROR_EXIT_THRESHOLD |
5 |
Exit 1 + notify |
TEAMS_WEBHOOK_URL |
— | Ops alerts (fail/streak); also match fallback |
TEAMS_MATCH_WEBHOOK_URL |
— | Optional dedicated capture channel |
TEAMS_MATCH_SCORE_THRESHOLD |
40 |
Ping when RelevanceScore or Grok fit ≥ N (Teams + Slack) |
TEAMS_MATCH_NOTIFY_ENABLED |
true |
Master switch for Teams match pings |
SLACK_BOT_TOKEN |
— | CLI/Bolt xoxb- bot token (preferred) |
SLACK_CHANNEL_ID |
— | Channel for chat.postMessage |
SLACK_APP_TOKEN |
— | Optional xapp- (Socket Mode only) |
SLACK_WEBHOOK_URL |
— | Legacy Incoming Webhook fallback |
SLACK_MATCH_NOTIFY_ENABLED |
true |
Master switch for Slack match pings |
KEYWORDS_PATH |
config/keywords.yaml |
Eng-owned ingest filter |
OBJECTIVES_PATH |
config/objectives.yaml |
Eng-owned Grok frame |
XAI_API_KEY |
— | Required for interpret-rank |
GOOGLE_SHEET_ID |
— | Ingest + Ranked sheet |
GOOGLE_SHEET_TAB |
Ingest |
Opportunity full-replace |
GOOGLE_SHEET_RANK_TAB |
Ranked |
Grok full-replace |
STATE_PATH |
state/zero_new_streak.json |
Streak JSON |
End of ops runbook.