30 lines
1.0 KiB
SQL
30 lines
1.0 KiB
SQL
CREATE TABLE IF NOT EXISTS dataset_versions (
|
|
id SERIAL PRIMARY KEY,
|
|
hash VARCHAR(64) NOT NULL UNIQUE,
|
|
previous_hash VARCHAR(64),
|
|
crawl_run_id INTEGER REFERENCES crawl_runs(id),
|
|
employee_count INTEGER NOT NULL DEFAULT 0,
|
|
active_count INTEGER NOT NULL DEFAULT 0,
|
|
dismissed_count INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_dataset_versions_created_at
|
|
ON dataset_versions (created_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS dataset_version_items (
|
|
id SERIAL PRIMARY KEY,
|
|
dataset_version_id INTEGER NOT NULL REFERENCES dataset_versions(id),
|
|
profile_key VARCHAR(255) NOT NULL,
|
|
employee_id INTEGER REFERENCES employees(id),
|
|
status VARCHAR(32) NOT NULL,
|
|
checksum VARCHAR(64) NOT NULL,
|
|
CONSTRAINT uq_dataset_version_items_version_profile UNIQUE (dataset_version_id, profile_key)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_dataset_version_items_hash
|
|
ON dataset_version_items (dataset_version_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_dataset_version_items_profile_key
|
|
ON dataset_version_items (profile_key);
|