OpenTender: slim the artifact (5.03 GB → ~1 GB) so it can actually deploy

[StephenAbbott]

The problem

The finalised opentender.db (2026-07-13, 503,377 tenders across AT/CZ/DK/EE/FI/FR/LT/LV) is 5.03 GB. That makes the deployment path in the OpenTender design unusable: the adapter downloads the DB from OPENTENDER_S3_URL into /tmp on Render, and a 5 GB pull into ephemeral disk on every cold start is not viable on the free tier — quite apart from the S3 egress and the minutes added to an already-slow cold start.

So the source cannot go live until the artifact shrinks, regardless of the query work in #29.

Where the size actually is

Measured on the artifact:

• ~4.4 GB (≈87%) is the tenders.data column — the raw, complete OpenTender tender record stored as JSON, averaging 8.5 KB per tender.
• The rest (FTS index, body_ids, tenders scalar columns) is ~0.6 GB and is the part actually doing the work.

But the adapter reads only a handful of fields out of that 8.5 KB blob:

Consumer	Fields used
_tender_hit()	persistentId / id, title / titleEnglish, country, buyers[].name, procedureType, ot.integrity, ot.transparency
_walk_bodies() → _bridge_identifier()	bodies + bodyIds[] (type, scope, id)
map_opentender() (bods/mapper.py)	buyers, bidders/winners, lots[], awardDecisionDate, award values

Everything else in the record — full descriptions, CPV code lists, document metadata, publication histories, corrections, raw source payloads — is carried at ~8.5 KB/tender and never read.

Proposed fix: rewrite data in place — no re-ingest

The key point: this does not require re-reading the source NDJSON archives (that ingest takes hours and the UK archive alone would make a full rebuild impractical — see the Notion ticket). A one-off migration can stream the existing DB, project each data blob down to the fields above, and write it back:

for persistent_id, data in SELECT persistent_id, data FROM tenders:
    slim = project(json.loads(data))       # keep only the consumed fields
    UPDATE tenders SET data = ? WHERE persistent_id = ?

then VACUUM INTO to reclaim the space (reuse finalise_db(), which already does checkpoint + integrity_check + VACUUM and returns the SHA-256).

Rough expectation: a projected record should land around 1–2 KB, so ~0.7 GB of JSON instead of 4.4 GB → a ~1–1.5 GB artifact. Still not small, but plausibly deployable, and a further cut is available by dropping titleEnglish or trimming the lots[] array to awarded lots only.

Suggested implementation

1. backend/scripts/slim_opentender.py — takes an existing DB, projects data, VACUUMs, prints the new SHA-256. Idempotent; safe to run on an already-slim DB (projection of a projected record is a no-op).
2. Add the same projection to extract_opentender.py behind a --slim flag (default on?) so future builds are born small and this never recurs.
3. Guard the projection with a test: for a sample tender, assert every field _tender_hit(), _walk_bodies() and map_opentender() touch survives the projection — so slimming can never silently break the BODS mapping. Ideally derive the keep-list from one place rather than duplicating it.
4. Re-measure and record the new size + SHA in docs/sources.md.

Caveats

• Fidelity: the slim artifact is lossy by design. If the raw record is ever wanted (a future /deepen that shows the full tender, say), it won't be there. Worth a decision now: is OpenTender a signal source (who won what, and their identifiers) or an archive? The adapter as written treats it as the former.
• Licence unchanged: still CC-BY-NC-SA-4.0; the NC obligations propagate through /deepen and /export regardless of size.
• Doing this before OpenTender: identifier-first dispatch + gate the name search, before registering the source #29's query work is fine — they're independent. But both must land before the source is registered and deployed.