1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
|
CREATE TABLE "change_logs" (
"id" serial PRIMARY KEY NOT NULL,
"contract_id" integer NOT NULL,
"entity_type" varchar(50) NOT NULL,
"entity_id" integer NOT NULL,
"action" varchar(20) NOT NULL,
"changed_fields" jsonb,
"old_values" jsonb,
"new_values" jsonb,
"user_id" integer,
"user_name" varchar(255),
"created_at" timestamp DEFAULT now() NOT NULL,
"is_synced" boolean DEFAULT false,
"sync_attempts" integer DEFAULT 0,
"last_sync_error" text,
"synced_at" timestamp,
"target_systems" jsonb DEFAULT '[]'::jsonb
);
--> statement-breakpoint
CREATE TABLE "sync_batches" (
"id" serial PRIMARY KEY NOT NULL,
"contract_id" integer NOT NULL,
"target_system" varchar(50) NOT NULL,
"batch_size" integer NOT NULL,
"status" varchar(20) DEFAULT 'PENDING' NOT NULL,
"started_at" timestamp,
"completed_at" timestamp,
"error_message" text,
"retry_count" integer DEFAULT 0,
"change_log_ids" jsonb NOT NULL,
"success_count" integer DEFAULT 0,
"failure_count" integer DEFAULT 0,
"sync_metadata" jsonb,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "sync_configs" (
"id" serial PRIMARY KEY NOT NULL,
"contract_id" integer NOT NULL,
"target_system" varchar(50) NOT NULL,
"sync_enabled" boolean DEFAULT true,
"sync_interval_minutes" integer DEFAULT 30,
"last_successful_sync" timestamp,
"last_sync_attempt" timestamp,
"endpoint_url" text NOT NULL,
"auth_token" text,
"api_version" varchar(20) DEFAULT 'v1',
"max_batch_size" integer DEFAULT 100,
"retry_max_attempts" integer DEFAULT 3,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "sync_configs" ADD CONSTRAINT "sync_configs_contract_id_contracts_id_fk" FOREIGN KEY ("contract_id") REFERENCES "public"."contracts"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_change_logs_contract_synced" ON "change_logs" USING btree ("contract_id","is_synced");--> statement-breakpoint
CREATE INDEX "idx_change_logs_created_at" ON "change_logs" USING btree ("created_at");--> statement-breakpoint
CREATE INDEX "idx_change_logs_entity" ON "change_logs" USING btree ("entity_type","entity_id");--> statement-breakpoint
CREATE INDEX "idx_change_logs_sync_attempts" ON "change_logs" USING btree ("sync_attempts");--> statement-breakpoint
CREATE INDEX "idx_sync_batches_contract_system" ON "sync_batches" USING btree ("contract_id","target_system");--> statement-breakpoint
CREATE INDEX "idx_sync_batches_status" ON "sync_batches" USING btree ("status");--> statement-breakpoint
CREATE INDEX "idx_sync_batches_created_at" ON "sync_batches" USING btree ("created_at");--> statement-breakpoint
CREATE INDEX "idx_sync_configs_contract_system" ON "sync_configs" USING btree ("contract_id","target_system");--> statement-breakpoint
CREATE VIEW "public"."sync_status_view" AS (
WITH change_stats AS (
SELECT
cl.contract_id,
sc.target_system,
COUNT(*) as total_changes,
COUNT(CASE WHEN cl.is_synced = false AND cl.sync_attempts < sc.retry_max_attempts THEN 1 END) as pending_changes,
COUNT(CASE WHEN cl.is_synced = true THEN 1 END) as synced_changes,
COUNT(CASE WHEN cl.sync_attempts >= sc.retry_max_attempts AND cl.is_synced = false THEN 1 END) as failed_changes,
MAX(cl.synced_at) as last_sync_at
FROM change_logs cl
CROSS JOIN sync_configs sc
WHERE cl.contract_id = sc.contract_id
AND (cl.target_systems IS NULL OR cl.target_systems @> to_jsonb(sc.target_system))
GROUP BY cl.contract_id, sc.target_system
)
SELECT
cs.contract_id,
cs.target_system,
COALESCE(cs.total_changes, 0) as total_changes,
COALESCE(cs.pending_changes, 0) as pending_changes,
COALESCE(cs.synced_changes, 0) as synced_changes,
COALESCE(cs.failed_changes, 0) as failed_changes,
cs.last_sync_at,
CASE
WHEN sc.sync_enabled = true AND sc.last_successful_sync IS NOT NULL
THEN sc.last_successful_sync + (sc.sync_interval_minutes || ' minutes')::interval
ELSE NULL
END as next_sync_at,
sc.sync_enabled
FROM sync_configs sc
LEFT JOIN change_stats cs ON sc.contract_id = cs.contract_id AND sc.target_system = cs.target_system
);
|
