---
phase: 13-scraping-adapters-cross-source-dedup
plan: 01
subsystem: database
tags: [prisma, postgresql, crypto, dedup, sha256]

requires:
  - phase: 10-doe-opendata-ingestion
    provides: Tender model (sourcePortal, sourceNoticeId, ocid, dedupKey, cpvDivisions, contentHash), TenderNormalizerService, tender.types.ts contract
provides:
  - "TenderSource Prisma model (1:n Tender, @@unique[sourcePortal, sourceNoticeId], onDelete Cascade)"
  - "Tender.fingerprint nullable column + index, additive alongside the unchanged dedupKey"
  - "pure tenderFingerprint() NULL-tolerant dedup-key function (title+buyer dominant, CPV division, value-bucket, deadline-day)"
  - "2851 pre-existing DÖE tenders backfilled with one TenderSource row + a computed fingerprint each"
  - "SourceType widened to open union ('doe-opendata' | 'ai-netserver' | 'cosinex-dtvp')"
affects: [13-02-source-registry, 13-03-dedup-resolver, 13-04-netserver-adapter, 13-05-cosinex-adapter, 13-06-multi-source-display]

tech-stack:
  added: []
  patterns:
    - "Pure fingerprint function (no I/O, no Prisma import) computed once and reused by both the one-time backfill script and (in Plan 13-03) the live dedup resolver"
    - "Two-step migration ordering for additive schema + data backfill: (1) table/column DDL, (2) SQL data copy, (3) unique constraint — avoids constraint violations from pre-existing data"
    - "One-time TS backfill scripts run via the compiled dist/ output (standard extensionless imports), not as raw .ts via node's native type-stripping — keeps tsc --noEmit clean project-wide"

key-files:
  created:
    - apps/api/src/tenders/tender-fingerprint.ts
    - apps/api/src/tenders/tender-fingerprint.spec.ts
    - apps/api/src/tenders/backfill-tender-source.ts
    - apps/api/prisma/migrations/20260723120000_add_tender_source/migration.sql
  modified:
    - apps/api/prisma/schema.prisma
    - apps/api/src/tenders/tender.types.ts

key-decisions:
  - "fingerprint canonical string is [buyer, title, cpvDivisionKey, deadlineKey, valueBucket].join('|') -> sha256 hex, exactly per 13-RESEARCH Pattern 4 — deterministic O(1) lookup, no similarity threshold"
  - "dedupKey @unique on Tender is left untouched (SCHEMA-02 upsert target); fingerprint is a separate, additive, nullable column"
  - "One-time backfill script uses standard (extensionless) TS imports and documents running the compiled dist/ output, rather than node's raw .ts execution — Node 24's native type-stripping treats ESM-syntax .ts files as ESM requiring explicit .ts/.js extensions, which tsc rejects under moduleResolution:node; compiling first keeps both runtime execution and tsc --noEmit clean"

patterns-established:
  - "Fuzzy-dedup fingerprint: pure function, NULL segments collapse to empty string rather than excluding the record, title always included as the collision guard"

requirements-completed: [SCHEMA-03]

coverage:
  - id: D1
    description: "tenderFingerprint() is NULL-tolerant (title+buyer+CPV match with value/deadline both NULL), collision-resistant (different title -> different hash), umlaut-normalizing, order-independent on CPV divisions, magnitude-bucketed on value, and returns a stable sha256 hex string"
    requirement: SCHEMA-03
    verification:
      - kind: unit
        ref: "apps/api/src/tenders/tender-fingerprint.spec.ts (8 tests, all pass)"
        status: pass
    human_judgment: false
  - id: D2
    description: "TenderSource model (1:n, @@unique[sourcePortal, sourceNoticeId]) and Tender.fingerprint exist in schema.prisma; migration applied locally; every pre-existing Tender has exactly one TenderSource row backfilled from its current sourcePortal/sourceNoticeId/sourceUrl/ocid"
    requirement: SCHEMA-03
    verification:
      - kind: integration
        ref: "npx prisma validate && npx prisma generate (clean)"
        status: pass
      - kind: manual_procedural
        ref: "docker compose exec -T db psql -U tessera -d tessera -c 'SELECT count(*) FROM \"TenderSource\"' -> 2851 == SELECT count(*) FROM \"Tender\" -> 2851"
        status: pass
    human_judgment: false
  - id: D3
    description: "Tender.fingerprint backfilled for all pre-existing rows using the Task-1 tenderFingerprint() function, with Decimal->number conversion for estimatedValue"
    requirement: SCHEMA-03
    verification:
      - kind: manual_procedural
        ref: "backfill-tender-source.ts run via compiled dist/ output — console output 'fingerprint set on 2851/2851 tenders', re-verified idempotent on second run; SELECT count(*) FROM \"Tender\" WHERE fingerprint IS NOT NULL -> 2851"
        status: pass
    human_judgment: false
  - id: D4
    description: "SourceType is an open union ('doe-opendata' | 'ai-netserver' | 'cosinex-dtvp') and NormalizedTenderFields carries an optional fingerprint field, without breaking the existing normalizer contract"
    requirement: SCHEMA-03
    verification:
      - kind: unit
        ref: "cd apps/api && npx tsc --noEmit -p tsconfig.json (clean)"
        status: pass
      - kind: unit
        ref: "pnpm --filter @tessera/api test (full suite, 234/234 pass)"
        status: pass
    human_judgment: false

duration: 35min
completed: 2026-07-23
status: complete
---

# Phase 13 Plan 01: Fingerprint + TenderSource Datenkern Summary

**NULL-tolerante `tenderFingerprint()`-Funktion, additives `TenderSource`-1:n-Modell + `Tender.fingerprint`, und Backfill aller 2851 Bestands-DÖE-Tender — der dependency-freie SCHEMA-03-Datenkern für Cross-Source-Dedup.**

## Performance

- **Duration:** 35 min
- **Started:** 2026-07-23T08:33:00Z
- **Completed:** 2026-07-23T08:39:00Z
- **Tasks:** 3
- **Files modified:** 6 (2 created new spec/source pairs collapsed to 4 created + 2 modified)

## Accomplishments
- Pure, NULL-tolerante `tenderFingerprint()` (title+buyer dominant, CPV-Division, value-bucket, deadline-day, sha256) — TDD RED→GREEN, 8/8 Tests grün.
- `TenderSource`-Modell (1:n zu `Tender`, `@@unique([sourcePortal, sourceNoticeId])`, `onDelete: Cascade`) + additive nullable `Tender.fingerprint`-Spalte + Index, `dedupKey` unverändert.
- Handgeschriebene Migration `20260723120000_add_tender_source` in strikter 3-Schritt-Reihenfolge (DDL → Backfill-INSERT → Unique-Constraint), lokal gegen die `tessera`-Dev-DB angewendet.
- Alle 2851 Bestands-DÖE-Tender: je eine `TenderSource`-Zeile (verifiziert `count(*) = 2851 = 2851`) + berechneter `fingerprint` (verifiziert `2851/2851`, idempotent re-run bestätigt).
- `SourceType` zur offenen Union erweitert, `NormalizedTenderFields.fingerprint?` ergänzt — `tsc --noEmit` und volle API-Testsuite (234 Tests) grün.

## Task Commits

Each task was committed atomically:

1. **Task 1a: RED — failing tenderFingerprint test** - `063ba5b` (test)
2. **Task 1b: GREEN — implement tenderFingerprint** - `c6cac69` (feat)
3. **Task 2: TenderSource model + migration + backfill** - `447fb74` (feat)
4. **Task 3: SourceType union + NormalizedTenderFields.fingerprint** - `c2a6021` (feat)

_TDD task (Task 1) produced two commits (test → feat) per protocol; no refactor commit was needed._

## Files Created/Modified
- `apps/api/src/tenders/tender-fingerprint.ts` - pure NULL-tolerant sha256 dedup-fingerprint function
- `apps/api/src/tenders/tender-fingerprint.spec.ts` - 8 unit tests (NULL-tolerance, collision guard, umlauts, CPV order-independence, value bucketing, determinism)
- `apps/api/src/tenders/backfill-tender-source.ts` - one-time script computing `Tender.fingerprint` for all rows
- `apps/api/prisma/migrations/20260723120000_add_tender_source/migration.sql` - table+column DDL, TenderSource backfill INSERT, unique constraint
- `apps/api/prisma/schema.prisma` - `model TenderSource` + `Tender.fingerprint`/`sources`/`@@index([fingerprint])`
- `apps/api/src/tenders/tender.types.ts` - `SourceType` open union, `NormalizedTenderFields.fingerprint?`

## Decisions Made
- **Fingerprint algorithm exactly per 13-RESEARCH.md Pattern 4** (title+buyer dominant, CPV division not full code, value order-of-magnitude bucket, deadline day-grain, sha256 hex) — deterministic O(1) lookup, no similarity-threshold matching (avoids O(n) scans against the 2851+ row backlog per ingest).
- **`dedupKey @unique` left untouched** — it remains the SCHEMA-02 DÖE upsert target; `fingerprint` is a fully additive, nullable column, no migration risk to existing ingestion.
- **Backfill script runs via compiled `dist/` output, not raw `.ts` via node's native type-stripping.** Node 24 treats a `.ts` file containing `import`/`export` syntax as ESM and requires explicit `.ts`/`.js` extensions for relative imports; but the project's `tsconfig.json` (`module: commonjs`, `moduleResolution: node`) rejects explicit `.ts` extensions in import specifiers (`TS5097`). Using standard extensionless imports (tsc-clean) and documenting `pnpm --filter @tessera/api build && node dist/tenders/backfill-tender-source.js` as the run command resolves both constraints — confirmed working end-to-end (2851/2851, idempotent re-run).

## Deviations from Plan

### Auto-fixed Issues

**1. [Rule 3 - Blocking] Backfill script import style adjusted for tsc/runtime compatibility**
- **Found during:** Task 2 (backfill-tender-source.ts)
- **Issue:** The plan didn't specify how the one-time TS backfill script would actually be executed. An initial version imported `./tender-fingerprint.ts` with an explicit extension to satisfy Node 24's native TS execution — but that broke `npx tsc --noEmit` (Task 3's verification command) with `TS5097: An import path can only end with a '.ts' extension when 'allowImportingTsExtensions' is enabled`.
- **Fix:** Switched to standard extensionless imports (tsc-compatible) and ran the script via `nest build` + `node dist/tenders/backfill-tender-source.js` instead of raw `.ts` execution. Documented this invocation in the file's header comment.
- **Files modified:** apps/api/src/tenders/backfill-tender-source.ts
- **Verification:** `npx tsc --noEmit -p tsconfig.json` clean; compiled script re-run confirmed idempotent (`2851/2851` both times).
- **Committed in:** 447fb74 (Task 2 commit)

---

**Total deviations:** 1 auto-fixed (1 blocking — script invocation mechanics)
**Impact on plan:** No scope creep; fix was purely about how the already-planned one-time script gets executed so it doesn't regress the project's typecheck verification.

## Issues Encountered
- `apps/api/prisma/migrations/20260722180000_ldap_tls_reject_unauthorized` (from a prior phase) had not yet been applied to this local dev DB. `prisma migrate deploy` applied it together with the new `20260723120000_add_tender_source` migration in the same run — expected catch-up, not a regression caused by this plan.
- `DATABASE_URL` is not present in `apps/api/.env` on the host (only used inside the Docker network); per project MEMORY (`project_local_db_migrations`), it must be exported manually with the `db` container's ephemeral bridge IP (`docker inspect ... tessera-ctl-db-1`) for any host-side Prisma CLI invocation. Followed the documented workflow.

## User Setup Required

None - no external service configuration required. All changes applied to the local dev DB only (no test/prod server touched, per MEMORY constraints).

## Next Phase Readiness
- The dependency-free SCHEMA-03 data core (D-01) is fully in place and tested: `TenderSource`, `Tender.fingerprint`, and `tenderFingerprint()` are ready for Plan 13-02 (Source-Registry + Denylist) and Plan 13-03 (3-tier dedup resolver) to build on directly.
- `SourceType`'s open union unblocks Plan 13-04 (NetServer adapter) and Plan 13-05 (cosinex adapter) without further type-contract changes.
- No blockers. `dedupKey`'s eventual consolidation with `fingerprint` remains explicitly deferred (13-RESEARCH Open Question 3) — not in scope for this milestone.

---
*Phase: 13-scraping-adapters-cross-source-dedup*
*Completed: 2026-07-23*

## Self-Check: PASSED

All created files verified present on disk; all 4 task commit hashes verified in git log.