319 lines
18 KiB
SQL
319 lines
18 KiB
SQL
-- Central verification DB (Phase 1: dashboard pipeline).
|
|
-- Ingest reads a folder of videos + sibling export-JSONs and fills these tables.
|
|
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
source_path TEXT NOT NULL, -- local folder now; mounted NAS share on the VM
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS videos (
|
|
id SERIAL PRIMARY KEY,
|
|
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
file_name TEXT NOT NULL,
|
|
rel_path TEXT NOT NULL,
|
|
has_json BOOLEAN NOT NULL DEFAULT FALSE,
|
|
width INTEGER,
|
|
height INTEGER,
|
|
fps DOUBLE PRECISION,
|
|
frame_count INTEGER,
|
|
annotation_count INTEGER NOT NULL DEFAULT 0,
|
|
annotation_time_ms BIGINT NOT NULL DEFAULT 0,
|
|
primary_annotator TEXT NOT NULL DEFAULT '',
|
|
-- 'pending' = no JSON yet (not annotated)
|
|
-- 'annotated'= has annotations from the NAS JSON (awaiting verification)
|
|
-- 'verified' = a worker pushed a verified pass (Phase 3)
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
annotated_at TIMESTAMPTZ, -- max(annotation.created_at), used for throughput
|
|
raw_json JSONB, -- full imported export doc (for Phase 3 pull)
|
|
ingested_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (project_id, rel_path)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_videos_project ON videos(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_videos_status ON videos(project_id, status);
|
|
|
|
CREATE TABLE IF NOT EXISTS annotations (
|
|
id SERIAL PRIMARY KEY,
|
|
video_id INTEGER NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
|
|
frame_number INTEGER NOT NULL,
|
|
label TEXT NOT NULL,
|
|
side TEXT NOT NULL DEFAULT '',
|
|
shape_type TEXT NOT NULL DEFAULT 'bbox',
|
|
vertices JSONB,
|
|
annotated_by TEXT NOT NULL DEFAULT '',
|
|
review_status TEXT NOT NULL DEFAULT 'none',
|
|
remark TEXT NOT NULL DEFAULT '',
|
|
subclass TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_annotations_video ON annotations(video_id);
|
|
CREATE INDEX IF NOT EXISTS idx_annotations_user ON annotations(annotated_by);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Phase 3: worker pull/push (claim → verify → push) + token auth.
|
|
-- These run on every startup; ALTER ... IF NOT EXISTS keeps them idempotent and
|
|
-- migrates existing Postgres volumes without a separate migration step.
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
-- Provisioned workers/admins. Tokens are never stored in clear: token_hash is
|
|
-- the lowercase hex SHA-256 of the issued token. Admin provisions these.
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE,
|
|
display_name TEXT NOT NULL DEFAULT '',
|
|
token_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'ml_support', -- 'ml_support' | 'admin'
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_users_token ON users(token_hash);
|
|
|
|
-- Roles were renamed worker → ml_support (only two roles now: ml_support | admin).
|
|
-- Idempotent: migrate existing rows + the column default on already-provisioned DBs.
|
|
UPDATE users SET role='ml_support' WHERE role='worker';
|
|
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'ml_support';
|
|
|
|
-- Claiming is orthogonal to the annotation `status` above: a worker claims an
|
|
-- 'annotated' video (exclusive lease), verifies it, then pushes → 'verified'.
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS claimed_by TEXT;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS claimed_at TIMESTAMPTZ;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS lease_expires_at TIMESTAMPTZ;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS completed_by TEXT;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS completed_at TIMESTAMPTZ;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS verify_time_ms BIGINT NOT NULL DEFAULT 0;
|
|
|
|
-- A video is claimable when annotated and either unclaimed or its lease lapsed.
|
|
CREATE INDEX IF NOT EXISTS idx_videos_claim ON videos(project_id, claimed_by, lease_expires_at);
|
|
|
|
-- Imported baseline: how many annotations the video started a pass with (NAS-ingested
|
|
-- count, or the worker's claim-time count on push). vs annotation_count it reveals how
|
|
-- many the worker added/deleted during verification.
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS imported_count INTEGER NOT NULL DEFAULT 0;
|
|
|
|
-- "Raise hand" signal: anyone (ml_support or admin) can raise a hand on a video to
|
|
-- start a conversation; anyone can lower it. Orthogonal to the remark thread.
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS hand_raised BOOLEAN NOT NULL DEFAULT FALSE;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS hand_raised_by TEXT;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS hand_raised_at TIMESTAMPTZ;
|
|
|
|
-- "Ignore" flag: admins can mark a video as ignored (excluded from the workflow);
|
|
-- the dashboard/desktop cross out the row. Admin-only to set.
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS ignored BOOLEAN NOT NULL DEFAULT FALSE;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS ignored_by TEXT;
|
|
|
|
-- Admin assignment: which user a video is assigned to (advisory owner). Orthogonal to
|
|
-- claim/status; drives the 'assigned' workflow state until someone claims it.
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS assigned_to TEXT;
|
|
CREATE INDEX IF NOT EXISTS idx_videos_assigned ON videos(project_id, assigned_to);
|
|
|
|
-- Audit + analytics: every claim/release/push/auto_release/ingest.
|
|
CREATE TABLE IF NOT EXISTS video_events (
|
|
id SERIAL PRIMARY KEY,
|
|
video_id INTEGER NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
|
|
username TEXT NOT NULL DEFAULT '',
|
|
event TEXT NOT NULL, -- 'claim'|'release'|'push'|'auto_release'|'reopen'
|
|
at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
meta JSONB NOT NULL DEFAULT '{}'::jsonb
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_events_video ON video_events(video_id);
|
|
CREATE INDEX IF NOT EXISTS idx_events_at ON video_events(at);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Clients own projects (one client → many projects). Admins create these from
|
|
-- the dashboard; each project points at a NAS folder (`source_path`).
|
|
-- Idempotent: safe to re-run on every startup.
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS clients (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- projects gains a client owner; name becomes unique PER client
|
|
-- (Microsoft/India and Google/India can both exist).
|
|
ALTER TABLE projects ADD COLUMN IF NOT EXISTS client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE;
|
|
|
|
-- One-time backfill: only if a client-less project exists, park it under a 'default' client.
|
|
-- Guarded so a deleted 'default' is NOT resurrected on every boot.
|
|
DO $$ BEGIN
|
|
IF EXISTS (SELECT 1 FROM projects WHERE client_id IS NULL) THEN
|
|
INSERT INTO clients (name) VALUES ('default') ON CONFLICT (name) DO NOTHING;
|
|
UPDATE projects SET client_id = (SELECT id FROM clients WHERE name='default') WHERE client_id IS NULL;
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Swap the old global UNIQUE(name) for UNIQUE(client_id, name). DROP IF EXISTS is idempotent;
|
|
-- ADD CONSTRAINT is guarded by a DO-block (Postgres has no ADD CONSTRAINT IF NOT EXISTS).
|
|
ALTER TABLE projects DROP CONSTRAINT IF EXISTS projects_name_key;
|
|
DO $$ BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname='projects_client_name_key') THEN
|
|
ALTER TABLE projects ADD CONSTRAINT projects_client_name_key UNIQUE (client_id, name);
|
|
END IF;
|
|
END $$;
|
|
CREATE INDEX IF NOT EXISTS idx_projects_client ON projects(client_id);
|
|
|
|
-- How to reach a project's source folder. 'local' = scan source_path directly (a path inside the
|
|
-- container, incl. compose-managed NFS volumes); 'nfs' = mount NFS_HOST:source_path on demand
|
|
-- (the NAS host/IP lives only in the NFS_HOST env, never here).
|
|
ALTER TABLE projects ADD COLUMN IF NOT EXISTS source_kind TEXT NOT NULL DEFAULT 'local';
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Audit log of org-level actions (client/project created, sync dir changed,
|
|
-- synced, user created) with timestamp + actor.
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS audit_events (
|
|
id SERIAL PRIMARY KEY,
|
|
at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
username TEXT NOT NULL DEFAULT '',
|
|
action TEXT NOT NULL, -- 'client_created'|'project_created'|'project_source_changed'|'synced'|'user_created'
|
|
detail JSONB NOT NULL DEFAULT '{}'::jsonb
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_at ON audit_events(at);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Per-video remark thread. Admins and workers both append; everyone sees the
|
|
-- same thread (synced). Each line is attributed to its author + timestamp.
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS video_remarks (
|
|
id SERIAL PRIMARY KEY,
|
|
video_id INTEGER NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
|
|
username TEXT NOT NULL,
|
|
body TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_video_remarks_video ON video_remarks(video_id, created_at);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Project membership: which users are picked for a project. Admins manage this.
|
|
-- Non-admins only see projects they're a member of; videos can only be assigned
|
|
-- to members.
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS project_members (
|
|
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
username TEXT NOT NULL,
|
|
added_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (project_id, username)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_project_members_user ON project_members(username);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Project timer: a stopwatch for "active project time". Admins start/stop/reset it;
|
|
-- a push (verify/re-verify) auto-resumes it; 72h of inactivity auto-stops it (idle
|
|
-- time isn't counted). elapsed = accumulated + (running ? now - started_at : 0).
|
|
-- ---------------------------------------------------------------------------
|
|
ALTER TABLE projects ADD COLUMN IF NOT EXISTS time_running BOOLEAN NOT NULL DEFAULT TRUE;
|
|
ALTER TABLE projects ADD COLUMN IF NOT EXISTS time_accumulated_ms BIGINT NOT NULL DEFAULT 0;
|
|
ALTER TABLE projects ADD COLUMN IF NOT EXISTS time_started_at TIMESTAMPTZ;
|
|
ALTER TABLE projects ADD COLUMN IF NOT EXISTS last_activity_at TIMESTAMPTZ;
|
|
-- Backfill: running projects count from their creation; seed last activity.
|
|
UPDATE projects SET time_started_at = created_at WHERE time_running AND time_started_at IS NULL;
|
|
UPDATE projects SET last_activity_at = created_at WHERE last_activity_at IS NULL;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Folders to consider for verification. When a project has ANY rows here, only
|
|
-- videos in those folders are shown/counted; empty = consider all folders.
|
|
-- Admins add/modify/delete the set.
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS project_folders (
|
|
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
folder TEXT NOT NULL, -- directory part of rel_path ('' = project root)
|
|
PRIMARY KEY (project_id, folder)
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- GPS pipeline (map view + chainage). A background sweep extracts each video's
|
|
-- GPS track with exiftool (dashcam Doc{N} samples), one video at a time:
|
|
-- gps_status: 'pending' → 'ok' (track stored) | 'none' (no GPS metadata)
|
|
-- | 'error' (exiftool/mount failure, see gps_error)
|
|
-- The RAW track is immutable once scanned; a kept "Viterbi" correction goes to
|
|
-- gps_track_corrected (map/chainage prefer it, raw is never modified).
|
|
-- Track format: [[lat, lon, t_s], ...] (t_s = seconds from first sample).
|
|
-- ---------------------------------------------------------------------------
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_status TEXT NOT NULL DEFAULT 'pending';
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_track JSONB;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_len_m DOUBLE PRECISION;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_error TEXT;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_scanned_at TIMESTAMPTZ;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_track_corrected JSONB;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_corrected_by TEXT;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_corrected_at TIMESTAMPTZ;
|
|
-- Human-readable summary of what the correction did ("2 spikes · start stitched …").
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_corrected_note TEXT;
|
|
-- Data-quality + continuity metadata from the raw scan: absolute epoch of the
|
|
-- first/last GPS fix (lets consecutive files stitch across a missing start),
|
|
-- raw sample count and the largest inter-fix gap (dropout visibility).
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_start_epoch DOUBLE PRECISION;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_end_epoch DOUBLE PRECISION;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_sample_count INTEGER;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS gps_max_gap_s DOUBLE PRECISION;
|
|
-- Real media duration read from the container by the exiftool scan. Load-bearing for
|
|
-- the stitch guard: an inter-file gap is only "missing start" when the track covers
|
|
-- LESS than the file's duration — otherwise the camera simply wasn't recording.
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS duration_s DOUBLE PRECISION;
|
|
CREATE INDEX IF NOT EXISTS idx_videos_gps ON videos(gps_status) WHERE gps_status = 'pending';
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Road types: admin-defined per-project labels (LHS/RHS/MCW/Servicelane, …)
|
|
-- assigned to videos. Injected into the export/claim JSON as `road_type` and
|
|
-- re-stamped on push (server-authoritative).
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS project_road_types (
|
|
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
PRIMARY KEY (project_id, name)
|
|
);
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS road_type TEXT NOT NULL DEFAULT '';
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Chainage (linear referencing with piecewise-linear calibration).
|
|
-- chainage_points = admin-entered client calibration pairs (lat/lon ↔ chainage).
|
|
-- Optional road_type tag scopes a point to one calibration group (LHS vs RHS are
|
|
-- separate routes); '' = usable by any group.
|
|
-- Per video:
|
|
-- sr_chainage_* = ACTUAL measured position along the GPS route (immutable,
|
|
-- regenerated only from GPS — never admin-editable)
|
|
-- chainage_* = client-calibrated values (error distributed between
|
|
-- consecutive control points)
|
|
-- route_seq = position of the video along the calibrated route
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS chainage_points (
|
|
id SERIAL PRIMARY KEY,
|
|
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
lat DOUBLE PRECISION NOT NULL,
|
|
lon DOUBLE PRECISION NOT NULL,
|
|
chainage_m DOUBLE PRECISION NOT NULL,
|
|
note TEXT NOT NULL DEFAULT '',
|
|
road_type TEXT NOT NULL DEFAULT '',
|
|
created_by TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_chainage_points_project ON chainage_points(project_id);
|
|
-- origin marks generated points ('quick' = the two-point quick assign) so they can
|
|
-- be replaced on the next quick run regardless of the (freely editable) note text.
|
|
ALTER TABLE chainage_points ADD COLUMN IF NOT EXISTS origin TEXT NOT NULL DEFAULT '';
|
|
UPDATE chainage_points SET origin='quick' WHERE origin='' AND note LIKE 'quick:%';
|
|
-- The full result of the last recompute (route/spans/per-video/warnings JSON) so
|
|
-- any admin who signs in later sees the existing computed data, not a recompute ask.
|
|
ALTER TABLE chainage_calibrations ADD COLUMN IF NOT EXISTS summary JSONB;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS chainage_start_m DOUBLE PRECISION;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS chainage_end_m DOUBLE PRECISION;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS sr_chainage_start_m DOUBLE PRECISION;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS sr_chainage_end_m DOUBLE PRECISION;
|
|
ALTER TABLE videos ADD COLUMN IF NOT EXISTS route_seq INTEGER;
|
|
|
|
-- The piecewise-linear calibration itself (per project + group), persisted at
|
|
-- recompute time as sorted [s_m, chainage_m] anchors. Needed to evaluate chainage at
|
|
-- ANY route position later — e.g. frame-exact per-annotation chainage in exports.
|
|
CREATE TABLE IF NOT EXISTS chainage_calibrations (
|
|
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
road_type TEXT NOT NULL DEFAULT '',
|
|
anchors JSONB NOT NULL,
|
|
route_len_m DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
computed_by TEXT NOT NULL DEFAULT '',
|
|
computed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (project_id, road_type)
|
|
);
|