Repository navigation
Expand file tree
/
Copy pathrun_schema.sql
More file actions
189 lines (150 loc) · 8.62 KB
/
Copy pathrun_schema.sql
File metadata and controls
189 lines (150 loc) · 8.62 KB
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
-- =============================================================================
-- run database – Neptune ETL operational schema (v3)
-- =============================================================================
-- Database role: READ-WRITE for the ETL script.
-- Lives on a separate PostgreSQL server from the meta database.
--
-- This schema holds all tables that are mutated during pipeline execution:
-- run.load_run – one row per pipeline execution
-- run.load_run_detail – per-mapping statistics for each run
-- run.table_watermark – high-water marks for timestamp-mode extraction
-- run.row_hash – SHA-256 fingerprints for hash-mode change detection
--
-- Cross-database integrity
-- ────────────────────────
-- PostgreSQL does not support foreign keys across databases. References to
-- meta.entity_mapping and meta.load_run are therefore carried as plain UUID
-- columns (mapping_id, run_id) without FK constraints. The application layer
-- is responsible for consistency.
--
-- Design rules
-- ────────────
-- • All PKs are UUID, generated by gen_random_uuid() (pgcrypto required).
-- • No password columns.
-- • All timestamps are TIMESTAMP (no time zone), stored as UTC.
-- =============================================================================
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE SCHEMA IF NOT EXISTS run;
-- ---------------------------------------------------------------------------
-- 1. LOAD RUNS
-- One row per pipeline execution (full or incremental).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS run.load_run (
run_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- 'full' – Neptune Bulk Loader via S3 CSV
-- 'incremental' – Gremlin HTTP upserts + drop()
run_type VARCHAR(20) NOT NULL DEFAULT 'incremental'
CHECK (run_type IN ('full', 'incremental')),
started_at TIMESTAMP NOT NULL DEFAULT (NOW() AT TIME ZONE 'UTC'),
finished_at TIMESTAMP,
status VARCHAR(20) NOT NULL DEFAULT 'RUNNING'
CHECK (status IN ('RUNNING','SUCCESS','FAILED','PARTIAL')),
-- S3 prefix written for full/bulk-load runs; NULL for incremental runs.
s3_prefix VARCHAR(500),
-- Neptune Bulk Loader job ID returned by the loader REST API (full only).
bulk_load_job_id VARCHAR(200), -- Job ID returned by the target graph bulk loader; NULL for incremental runs.
rows_upserted BIGINT NOT NULL DEFAULT 0,
rows_deleted BIGINT NOT NULL DEFAULT 0,
rows_skipped BIGINT NOT NULL DEFAULT 0,
error_message TEXT,
-- Free-text: IAM user, Lambda ARN, cron schedule, etc.
triggered_by VARCHAR(200)
);
COMMENT ON TABLE run.load_run IS 'Audit record for each ETL pipeline execution.';
COMMENT ON COLUMN run.load_run.run_type IS '"full" (Bulk Loader) or "incremental" (Gremlin).';
COMMENT ON COLUMN run.load_run.bulk_load_job_id IS 'Bulk-load job ID from the target graph system; NULL for incremental runs.';
-- ---------------------------------------------------------------------------
-- 2. LOAD RUN DETAILS
-- Per-mapping statistics for a single run.
-- mapping_id references meta.entity_mapping.mapping_id (UUID, no FK).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS run.load_run_detail (
detail_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- References run.load_run.run_id
run_id UUID NOT NULL REFERENCES run.load_run(run_id),
-- References meta.entity_mapping.mapping_id (cross-DB, no FK constraint)
mapping_id UUID NOT NULL,
-- 'bulk_load' | 'gremlin_upsert' | 'gremlin_delete'
operation VARCHAR(30),
-- S3 URI written for bulk_load operations; NULL otherwise.
s3_uri VARCHAR(500),
rows_upserted BIGINT NOT NULL DEFAULT 0,
rows_deleted BIGINT NOT NULL DEFAULT 0,
rows_skipped BIGINT NOT NULL DEFAULT 0,
error_message TEXT,
started_at TIMESTAMP NOT NULL DEFAULT (NOW() AT TIME ZONE 'UTC'),
finished_at TIMESTAMP
);
COMMENT ON COLUMN run.load_run_detail.mapping_id IS
'UUID of meta.entity_mapping; cross-database reference, no FK enforced.';
COMMENT ON COLUMN run.load_run_detail.operation IS
'"bulk_load", "gremlin_upsert", or "gremlin_delete".';
-- ---------------------------------------------------------------------------
-- 3. TABLE WATERMARKS
-- High-water mark per entity mapping for change_mode = 'timestamp'.
--
-- Two-phase commit pattern:
-- BEFORE reading source data → write pending_watermark = run start time
-- AFTER successful Gremlin writes → promote pending → last_watermark
-- This prevents gaps when a run is interrupted between extraction and write.
--
-- mapping_id references meta.entity_mapping.mapping_id (UUID, no FK).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS run.table_watermark (
watermark_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- References meta.entity_mapping.mapping_id (cross-DB, no FK constraint)
mapping_id UUID NOT NULL UNIQUE,
-- Lower bound for the next incremental extract.
-- NULL on first run (treated as "extract everything").
last_watermark TIMESTAMP,
-- Captured at run-start; promoted to last_watermark only on success.
pending_watermark TIMESTAMP,
-- References run.load_run.run_id of the run that last updated this row.
last_run_id UUID REFERENCES run.load_run(run_id),
updated_at TIMESTAMP NOT NULL DEFAULT (NOW() AT TIME ZONE 'UTC')
);
COMMENT ON TABLE run.table_watermark IS
'High-water mark for timestamp-mode incremental extraction.';
COMMENT ON COLUMN run.table_watermark.last_watermark IS
'Committed lower bound used as the WHERE predicate of the next run.';
COMMENT ON COLUMN run.table_watermark.pending_watermark IS
'Run-start timestamp; promoted to last_watermark only after success.';
COMMENT ON COLUMN run.table_watermark.mapping_id IS
'UUID of meta.entity_mapping; cross-database reference, no FK enforced.';
-- ---------------------------------------------------------------------------
-- 4. ROW HASHES
-- SHA-256 fingerprint per Neptune entity ID for change_mode = 'hash'.
-- Enables insert / update / delete detection without CDC infrastructure.
--
-- mapping_id references meta.entity_mapping.mapping_id (UUID, no FK).
-- first/last_seen_run_id reference run.load_run.run_id.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS run.row_hash (
hash_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- References meta.entity_mapping.mapping_id (cross-DB, no FK constraint)
mapping_id UUID NOT NULL,
-- Serialised Neptune ~id value produced by id_column_expr.
entity_id_value VARCHAR(500) NOT NULL,
-- SHA-256 hex digest of the concatenated mapped column values.
row_hash CHAR(64) NOT NULL,
first_seen_run_id UUID REFERENCES run.load_run(run_id),
last_seen_run_id UUID REFERENCES run.load_run(run_id),
updated_at TIMESTAMP NOT NULL DEFAULT (NOW() AT TIME ZONE 'UTC'),
UNIQUE (mapping_id, entity_id_value)
);
COMMENT ON TABLE run.row_hash IS
'Per-row SHA-256 fingerprint for hash-mode change detection.';
COMMENT ON COLUMN run.row_hash.entity_id_value IS
'Serialised Neptune ~id produced by id_column_expr.';
COMMENT ON COLUMN run.row_hash.row_hash IS
'SHA-256 hex digest of the concatenated mapped column values.';
COMMENT ON COLUMN run.row_hash.mapping_id IS
'UUID of meta.entity_mapping; cross-database reference, no FK enforced.';
-- ---------------------------------------------------------------------------
-- INDEXES
-- ---------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_run_detail_run_id ON run.load_run_detail (run_id);
CREATE INDEX IF NOT EXISTS idx_run_detail_mapping ON run.load_run_detail (mapping_id);
CREATE INDEX IF NOT EXISTS idx_watermark_mapping ON run.table_watermark (mapping_id);
CREATE INDEX IF NOT EXISTS idx_row_hash_mapping ON run.row_hash (mapping_id);
CREATE INDEX IF NOT EXISTS idx_row_hash_lookup ON run.row_hash (mapping_id, entity_id_value);