Identity row for a product route. Scoped to (productId, vendorId, matcherHash, programId) for live rows — the partial unique index
product_route_matcher_hash_unique enforces it, with a NULL program keyed
as 0. Mutable on currentVersionId, vendorId, matcherHash, programId,
deletedAt, status, and priority — every change to the matcher, vendor
or target inserts a new product_route_version row and re-points
currentVersionId; the sync_route_current_version_id trigger snaps
vendorId, matcherHash and programId from the new version atomically.
currentVersionId is NOT NULL — a route never exists without a version (a
version-less route is silently dropped by ProductRouteService's inner
join). The service/seed placeholder dance inserts the identity row with
currentVersionId = 0, then inserts the version row; the AFTER-INSERT
sync_route_current_version_id trigger snaps the pointer into place before
commit, when the deferrable composite FK is checked.
Identity row for a product route. Scoped to
(productId, vendorId, matcherHash, programId)for live rows — the partial unique indexproduct_route_matcher_hash_uniqueenforces it, with a NULL program keyed as 0. Mutable oncurrentVersionId,vendorId,matcherHash,programId,deletedAt,status, andpriority— every change to the matcher, vendor or target inserts a newproduct_route_versionrow and re-pointscurrentVersionId; thesync_route_current_version_idtrigger snapsvendorId,matcherHashandprogramIdfrom the new version atomically.currentVersionIdis NOT NULL — a route never exists without a version (a version-less route is silently dropped byProductRouteService's inner join). The service/seed placeholder dance inserts the identity row withcurrentVersionId = 0, then inserts the version row; the AFTER-INSERTsync_route_current_version_idtrigger snaps the pointer into place before commit, when the deferrable composite FK is checked.