## Summary
> Intentional rollout hold: this PR sets timeback-raw-sync to schedule.enabled: false and updates the repository's real-pipeline contract to require that state. This prevents an automatic complete production run before the new assessment_results path receives separately authorized targeted validation. On-demand execution remains available. Scheduled operation will be restored only by a separate post-validation repository change.
- make assessment_results use a one-hour-overlap dateLastModified incremental extraction by default
- retain the existing full snapshot only when the caller explicitly sends bulk_mode: "full"
- publish non-empty deltas atomically by sourced_id, preserving target rows absent from the delta
- add immutable manifest v5 evidence and idempotent replay for modified-since extraction
- temporarily set the pipeline schedule to disabled so deployment cannot start the complete pipeline before targeted production validation
Linear: [SURTR-1166](https://linear.app/builder-team/issue/SURTR-1166/add-safe-watermark-incremental-bulk-synchronization-to-timeback-raw)
## Why
The scheduled pipeline currently performs a complete download of mutable OneRoster entities. assessment_results alone has exceeded 15 million rows, and its deep-offset full extraction has repeatedly failed while the source was changing.
This change activates the new mechanism only for assessment_results. The other bulk entities, applications, test_assignments, and all fan-outs retain their current behavior.
## Behavior
For default assessment_results execution, the handler:
1. reads MAX(date_last_modified) from the published clean table;
2. subtracts exactly one hour;
3. sends the resulting immutable dateLastModified >= effective_start filter on every TimeBack request;
4. traverses the filtered result with bounded sourcedId keyset pagination;
5. lands exact response bodies and a checksummed manifest before publication; and
6. atomically replaces only the raw and clean rows whose sourced_id appears in the delta, together with the ledger insert.
TimeBack's filtered totalCount can drift, so it is retained as per-page evidence but is not used as the general continuation condition. Each delta is bounded above at extraction start, and replay verifies that every source row remains within that exact time window. Requests always use offset 0. Follow-up requests use an inclusive sourcedId >= cursor boundary so a distinct ID that compares equal under TimeBack's case-insensitive ordering cannot be skipped between pages. Exact repeated boundary rows are recorded in each receipt and ignored during unique-record replay; distinct casefold-equal IDs are retained. A short terminal response fails closed if totalCount claims omitted rows or if an identical second response cannot be landed as independent confirmation. A full page that cannot advance beyond its boundary also fails closed. Cursor values containing apostrophes or backslashes fail closed.
A valid empty delta inserts only its ledger evidence. It does not create work tables, stage files, run COPY, or mutate either target.
Missing/all-null clean watermarks fail before source extraction and instruct the operator to request explicit full mode. There is no scheduled, weekday, drift-triggered, or error-triggered full fallback.
Replay uses the original manifest, watermark, filter, pages, and extraction identity. It cannot recompute a watermark or turn a delta into a full-table replacement. Existing v1-v4 full/fan-out replay behavior remains unchanged; modified-since evidence uses manifest v5.
## Redshift publication safety
Separate Redshift Data API calls do not share temporary-table sessions, so non-empty deltas use UUID-scoped permanent work tables. Raw and clean staged counts, lineage, null keys, and duplicate keys are checked before the final batch.
The final BatchExecuteStatement contains raw delete/insert, clean delete/insert, and ledger insert as one Redshift transaction. Work tables and temporary S3 objects are cleaned after both success and failure. No DDL or migration is required.
## Development validation completed
The code was executed locally against the live TimeBack API, real S3, and the Redshift dev database. It has not been built or deployed through CDK and has not run in production.
All Redshift calls explicitly used Database=dev with isolated development S3 prefixes. No finance_dw query or write occurred.
- Preflight 4a7d88ec-6b17-4a74-a8f0-33d90697d429 confirmed current_database() = dev and 43 canonical TimeBack tables.
- Missing-watermark query 46fb6aa1-5388-43fa-ba5a-700d94f9abe6 returned SQL NULL; a fail-on-call source sentinel confirmed TimeBack was not contacted and targets were unchanged.
- The initial default dev run surtr1166-dev-default-keyset-20260910t1607 landed 56,093 rows in 29 keyset pages and proved the key-scoped publication path before the later pagination and terminal-completeness hardening. It did not run full partition planning.
- Publication batch 48236f27-4220-4fc2-a28e-40b35c05e867 succeeded. Verification 8396252d-af7f-4b62-a1f2-c4231ba1b6d1 found 56,093 unique changed keys in each target, zero raw/clean lineage mismatches, one ledger row, and a deliberately absent historical fixture preserved.
- Immutable replay batch 2f6ebc6f-d902-42c8-bcc4-53c60414a20b succeeded with a fail-on-call source sentinel. Verification 7b432d40-86f5-4bee-b487-b263594e2ec4 found unchanged target/key counts and one replay ledger row.
- A real zero-row filtered response produced a complete v5 manifest. Empty publication batch 165d0648-00f1-4ac6-992a-f6b2d9b64e18 contained only the ledger insert; verification d41f04d3-cc6a-4d6f-b48f-a2f9465e7a14 found both targets unchanged.
- An invalid sixth statement was injected after the normal five statements in final batch fab60668-249d-47f2-89ff-6b64da4fa375. The batch failed. Verification ccd07e19-7bf9-4f18-8e8d-a0df7523ca82 found zero failed-run raw rows, clean rows, or ledger rows and all prior rows intact, demonstrating transactional rollback.
- Work-table query 88a542ec-e321-4578-9662-d33835131d1c and the isolated staging-prefix listing found no temporary objects remaining.
- The current bounded-window, inclusive-boundary, terminal-confirmation implementation was exercised against live TimeBack with a five-minute scope. Manifest 45edde8519d673a6d5d42cb3c98cbd795b8e006e5e01da07afed63d10342a321 retained 2 source pages containing 2,000 and 322 rows, accounted for overlap [0, 1], landed an identical independent confirmation of the short terminal response, and replayed 2,321 unique records. Publishing that exact manifest to Redshift dev succeeded in batch 3c1017ff-a291-4187-b3c0-5332a738f324; verification 700fd346-48e1-4094-ab04-93fe7edbcada found 2,321 unique raw and clean keys, zero lineage mismatches, the historical fixture preserved, and one ledger row.
The Redshift design was checked against the current AWS documentation for BatchExecuteStatement transaction behavior, Data API SQL NULL fields, DELETE ... USING, transactional COPY, and TRUNCATE commit behavior.
## Test plan
- [x] cd pipelines/runners/timeback-raw-sync && uv run pytest -q — 202 passed
- [x] uv run ruff check src tests scripts
- [x] uv run ruff format --check src tests scripts
- [x] python -m json.tool pipeline.json
- [x] npm test -- --runInBand test/real-pipeline-configs.test.ts — 515 passed after updating the intentional schedule-hold contract
- [x] git diff --check
- [x] previous exact-range and full implementation reviews passed before the inclusive-boundary repair
- [ ] fresh Mercy review of the current head
- [ ] GitHub repository CI
- [ ] CDK production synth/diff before any production release
- [ ] separately authorized targeted production validation after deployment
## Production sequencing
This PR targets main; merging it does not deploy the pipeline. Production promotion is deliberately not part of this PR and is not yet authorized.
Before a main to production release, review the complete release diff because the production workflow deploys all pipeline stacks when pipeline paths change.
When production promotion is authorized, the intended sequence is:
1. deploy the new task definition with the TimeBack schedule disabled;
2. verify the deployed image/task revision and confirm no older execution is active;
3. separately authorize and run only assessment_results on demand — this is a real production publication, not a dry run;
4. verify watermark arithmetic, manifest scope, key uniqueness, historical-row preservation, matching lineage, ledger evidence, and temporary-object cleanup; and
5. restore the repository schedule configuration only after that targeted validation succeeds, allowing a later complete scheduled run.
No production execution is performed by this PR.
## Scope
- No DDL or migration
- No dependencies, Dockerfile, CDK runtime/construct, IAM, database, or schema changes
- One existing CDK real-pipeline configuration test is updated to require the intentional schedule hold
- No activation of the remaining 13 incremental-eligible entities
- No changes to applications, test_assignments, fan-outs, or the activity_facts window
- No automatic full fallback or automatic reconciliation
## Rollback
Revert the deployment or disable the assessment_results modified-since policy while retaining the generic support code. A completed delta leaves the table complete because keys absent from the delta are preserved. Explicit full mode remains available as a deliberate operator action; it is not invoked automatically during rollback.
