2ce03d21d9
- Add per-cycle correlation ID ([cyc:xxxxxxxx]) threaded through Python logs and SQL audit tables for end-to-end traceability of any divergence. - New ProductionDataBaseSync.SyncApplyRunLog table + @CycleID on usp_SyncApply for per-table apply auditing (pending/merged/deleted/applied, dead/error counts, duration). Audit write isolated in its own TRY/CATCH outside txn. - Capture/cleanup phases now emit full detail: Insert->Delete downgrade warning with record_id/log_id, dedup_skipped as apply-stall signal, per-file summary, and access log-id ranges on cleanup. - Queue health check surfaces error/dead rows with recent samples instead of silent accumulation (previously the top cause of data divergence). - sql_writer uses INSERT...SELECT...WHERE NOT EXISTS for observable dedup; idle cycles lowered to DEBUG with periodic heartbeat. - Backward compatible: old proc callers still work (CycleID nullable; legacy coarse-grained logging with one-time notice). Excluded from this commit: CODE.md, build_code_doc.py (doc generation).
76 lines
3.7 KiB
Transact-SQL
76 lines
3.7 KiB
Transact-SQL
-- SyncApplyRunLog: per-invocation, per-target-table audit of what usp_SyncApply
|
|
-- actually did. This closes the biggest observability gap in the pipeline: the
|
|
-- apply phase used to be a black box ("apply done") -- queue rows could flip
|
|
-- to 'error' or 'dead' with no trace in the service log, and there was no
|
|
-- record of how many rows a MERGE/DELETE touched at any point in time. With
|
|
-- this table, "what did apply do to table X around time T, and did it fail?"
|
|
-- is a single indexed query, and CycleID joins each row back to the exact
|
|
-- [cyc:xxxxxxxx] lines in the Python service log.
|
|
--
|
|
-- Written by usp_SyncApply (sql/02_sync_apply.sql) as a best-effort insert per
|
|
-- (invocation, target table). Idle cycles write nothing (the proc's cursor
|
|
-- only visits tables that have pending rows), so growth tracks real change
|
|
-- traffic, not poll frequency. Retention: unmanaged by default; if it ever
|
|
-- grows large, purge by StartedAt, e.g.
|
|
-- DELETE FROM ProductionDataBaseSync.SyncApplyRunLog
|
|
-- WHERE StartedAt < DATEADD(day, -90, SYSDATETIME());
|
|
--
|
|
-- Lives under the ProductionDataBaseSync schema (run 00_schema.sql first).
|
|
-- Idempotent: safe to re-run. Deploy alongside the updated 02_sync_apply.sql;
|
|
-- ordering is forgiving either way (the proc checks for this table and skips
|
|
-- the audit insert when it is absent).
|
|
|
|
IF OBJECT_ID('ProductionDataBaseSync.SyncApplyRunLog', 'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE ProductionDataBaseSync.SyncApplyRunLog (
|
|
RunLogID bigint IDENTITY(1,1) NOT NULL,
|
|
CycleID nvarchar(40) NULL, -- correlation id from the Python service ([cyc:...])
|
|
TargetSchema nvarchar(128) NOT NULL,
|
|
TargetTable nvarchar(255) NOT NULL,
|
|
PendingCount int NOT NULL, -- pending queue rows seen for this table
|
|
DistinctRecords int NULL, -- distinct RecordIDs among them
|
|
MergedCount int NULL, -- rows affected by the MERGE (insert + update)
|
|
DeletedCount int NULL, -- rows affected by the DELETE
|
|
AppliedCount int NULL, -- queue rows flipped to 'applied'
|
|
ErrorCount int NULL, -- queue rows flipped to 'error' (will retry)
|
|
DeadCount int NULL, -- queue rows flipped to 'dead' (retries exhausted)
|
|
Outcome varchar(10) NOT NULL, -- 'ok' | 'error'
|
|
ErrorMsg nvarchar(max) NULL,
|
|
StartedAt datetime2 NOT NULL
|
|
CONSTRAINT DF_SyncApplyRunLog_Started DEFAULT sysdatetime(),
|
|
DurationMs int NULL,
|
|
CONSTRAINT PK_SyncApplyRunLog PRIMARY KEY CLUSTERED (RunLogID)
|
|
);
|
|
END
|
|
GO
|
|
|
|
-- Join back to the Python service log of one cycle.
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes
|
|
WHERE name = 'IX_SyncApplyRunLog_Cycle'
|
|
AND object_id = OBJECT_ID('ProductionDataBaseSync.SyncApplyRunLog'))
|
|
BEGIN
|
|
CREATE INDEX IX_SyncApplyRunLog_Cycle
|
|
ON ProductionDataBaseSync.SyncApplyRunLog(CycleID);
|
|
END
|
|
GO
|
|
|
|
-- "What happened to this table around time T?"
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes
|
|
WHERE name = 'IX_SyncApplyRunLog_Table'
|
|
AND object_id = OBJECT_ID('ProductionDataBaseSync.SyncApplyRunLog'))
|
|
BEGIN
|
|
CREATE INDEX IX_SyncApplyRunLog_Table
|
|
ON ProductionDataBaseSync.SyncApplyRunLog(TargetSchema, TargetTable, StartedAt);
|
|
END
|
|
GO
|
|
|
|
-- Fast scan for failed applies.
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes
|
|
WHERE name = 'IX_SyncApplyRunLog_Outcome'
|
|
AND object_id = OBJECT_ID('ProductionDataBaseSync.SyncApplyRunLog'))
|
|
BEGIN
|
|
CREATE INDEX IX_SyncApplyRunLog_Outcome
|
|
ON ProductionDataBaseSync.SyncApplyRunLog(Outcome, StartedAt);
|
|
END
|
|
GO
|