xiaoshu-app/legacy-sync-bridge/database/install.sql

IF OBJECT_ID(N'dbo.XiaoshuSyncCommandLog', N'U') IS NULL
BEGIN
  CREATE TABLE dbo.XiaoshuSyncCommandLog (
    Id bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
    IdempotencyKey nvarchar(128) NOT NULL,
    Operation nvarchar(100) NOT NULL,
    Actor nvarchar(200) NOT NULL,
    Reason nvarchar(500) NOT NULL,
    PayloadJson nvarchar(max) NOT NULL,
    ResultJson nvarchar(max) NULL,
    State varchar(20) NOT NULL,
    CreatedAt datetime2(3) NOT NULL CONSTRAINT DF_XiaoshuSyncCommandLog_CreatedAt DEFAULT SYSUTCDATETIME(),
    CompletedAt datetime2(3) NULL,
    CONSTRAINT UQ_XiaoshuSyncCommandLog_Idempotency UNIQUE (IdempotencyKey)
  );
END;
GO

CREATE OR ALTER PROCEDURE dbo.XiaoshuSync_ExecuteCommand
  @Operation nvarchar(100), @Actor nvarchar(200), @Reason nvarchar(500),
  @IdempotencyKey nvarchar(128), @ExpectedUpdatedAt nvarchar(50) = NULL,
  @PayloadJson nvarchar(max), @ResultJson nvarchar(max) OUTPUT
AS
BEGIN
  SET NOCOUNT ON; SET XACT_ABORT ON;
  IF ISJSON(@PayloadJson) <> 1 THROW 51000, N'写入负载必须是有效 JSON', 1;
  BEGIN TRANSACTION;
  EXEC sys.sp_getapplock @Resource=N'xiaoshu-sync-command:'+@IdempotencyKey,@LockMode='Exclusive',@LockOwner='Transaction',@LockTimeout=10000;
  SELECT @ResultJson=ResultJson FROM dbo.XiaoshuSyncCommandLog WHERE IdempotencyKey=@IdempotencyKey AND State='completed';
  IF @ResultJson IS NOT NULL BEGIN COMMIT; RETURN; END;
  IF NOT EXISTS (SELECT 1 FROM dbo.XiaoshuSyncCommandLog WHERE IdempotencyKey=@IdempotencyKey)
    INSERT dbo.XiaoshuSyncCommandLog(IdempotencyKey,Operation,Actor,Reason,PayloadJson,State) VALUES(@IdempotencyKey,@Operation,@Actor,@Reason,@PayloadJson,'processing');

  /* 在此调用旧站 BLL/正式存储过程，与本日志同一事务并回写 ResultJson。 */
  ROLLBACK;
  THROW 51001, N'该写入操作尚未与旧站业务逻辑绑定，已安全拒绝', 1;
END;
GO