Identity row for a product route. Scoped to (productId, vendorId, matcherHash) for live rows — the partial unique index
product_route_matcher_hash_unique enforces it. Mutable on
currentVersionId, vendorId, matcherHash, deletedAt, status, and
priority — every change to the matcher or vendor inserts a new
product_route_version row and re-points currentVersionId; the
sync_route_current_version_id trigger snaps vendorId and matcherHash
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)for live rows — the partial unique indexproduct_route_matcher_hash_uniqueenforces it. Mutable oncurrentVersionId,vendorId,matcherHash,deletedAt,status, andpriority— every change to the matcher or vendor inserts a newproduct_route_versionrow and re-pointscurrentVersionId; thesync_route_current_version_idtrigger snapsvendorIdandmatcherHashfrom 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.