Central DB — ER diagram

Schema for the central/ verification dashboard (Postgres). Source of truth: central/db/init.sql (re-run idempotently on every server boot). This diagram was generated from the live database and verified against it.

Render: GitHub renders the Mermaid block below directly; in VS Code use the Markdown Preview Mermaid extension (or any Mermaid viewer).

Hierarchy at a glance

clients ──1:N──▶ projects ──1:N──▶ videos ──1:N──▶ annotations
                                      ├──1:N──▶ video_remarks   (remark thread)
                                      └──1:N──▶ video_events     (claim/push log)

users          — provisioned workers/admins (token auth)
audit_events   — org-level action log
   (both link to the hierarchy only "softly", by username string — no FK)

Entity–relationship diagram

erDiagram
    clients   ||--o{ projects       : "owns"
    projects  ||--o{ videos         : "contains"
    videos    ||--o{ annotations    : "has"
    videos    ||--o{ video_remarks  : "thread"
    videos    ||--o{ video_events   : "logs"

    clients {
        int         id          PK
        text        name        UK "unique client name"
        timestamptz created_at
    }

    projects {
        int         id          PK
        int         client_id   FK "→ clients.id (CASCADE); nullable"
        text        name        "UK(client_id, name)"
        text        source_path "NAS export path only — host lives in NFS_HOST env"
        text        source_kind "'local' | 'nfs'"
        timestamptz created_at
    }

    videos {
        int         id                 PK "= serverVideoId on the desktop"
        int         project_id         FK "→ projects.id (CASCADE)"
        text        file_name
        text        rel_path           "UK(project_id, rel_path); keeps subfolders"
        bool        has_json           "sibling export-JSON exists"
        int         width
        int         height
        float8      fps
        int         frame_count
        int         annotation_count
        bigint      annotation_time_ms
        text        primary_annotator
        text        status             "pending | annotated | verified"
        timestamptz annotated_at       "max(annotation.created_at)"
        jsonb       raw_json           "full imported export doc (Phase-3 pull)"
        timestamptz ingested_at
        text        claimed_by         "claim dimension — orthogonal to status"
        timestamptz claimed_at
        timestamptz lease_expires_at
        text        completed_by       "set on push (verify pass)"
        timestamptz completed_at
        bigint      verify_time_ms
    }

    annotations {
        int    id            PK
        int    video_id      FK "→ videos.id (CASCADE)"
        int    frame_number  "0-indexed"
        text   label
        text   side
        text   shape_type    "bbox | polygon"
        jsonb  vertices
        text   annotated_by  "username (soft link to users)"
        text   review_status "default 'none'"
        text   remark        "per-annotation note (NOT the thread)"
        text   subclass
        text   created_at
    }

    video_remarks {
        int         id         PK
        int         video_id   FK "→ videos.id (CASCADE)"
        text        username   "author (soft link to users)"
        text        body
        timestamptz created_at
    }

    video_events {
        int         id        PK
        int         video_id  FK "→ videos.id (CASCADE)"
        text        username
        text        event     "claim | release | push | auto_release | reopen"
        timestamptz at
        jsonb       meta
    }

    users {
        int         id           PK
        text        username     UK
        text        display_name
        text        token_hash   "SHA-256 of issued token (never clear)"
        text        role         "worker | admin"
        bool        active
        timestamptz created_at
    }

    audit_events {
        int         id       PK
        timestamptz at
        text        username "actor"
        text        action   "client_created | project_created | project_source_changed | synced | user_created | user_token_reset"
        jsonb       detail
    }

Relationships

Parent	Child	Cardinality	On delete	Constraint
`clients.id`	`projects.client_id`	1 : N	CASCADE	FK
`projects.id`	`videos.project_id`	1 : N	CASCADE	FK
`videos.id`	`annotations.video_id`	1 : N	CASCADE	FK
`videos.id`	`video_remarks.video_id`	1 : N	CASCADE	FK
`videos.id`	`video_events.video_id`	1 : N	CASCADE	FK

Soft links (by username string — no FK): users.username is referenced as text by annotations.annotated_by, videos.claimed_by / completed_by / primary_annotator, video_remarks.username, video_events.username, and audit_events.username. These are intentionally not foreign keys so historical attribution survives a user being removed.

Unique constraints & key indexes

Unique: clients(name), projects(client_id, name), videos(project_id, rel_path), users(username).
Indexes: idx_videos_project, idx_videos_status(project_id,status), idx_videos_claim(project_id,claimed_by,lease_expires_at), idx_annotations_video, idx_annotations_user, idx_users_token, idx_video_remarks_video(video_id,created_at), idx_events_video, idx_events_at, idx_audit_at, idx_projects_client.

Notes

Two orthogonal dimensions on videos: annotation status (pending/annotated/verified) vs. the claim/lease columns (claimed_by + lease_expires_at). Claimable = status IN ('pending','annotated') and unclaimed-or-lease-expired; push → verified + completed_by/verify_time_ms.
A range annotation in the export doc expands to 1–2 rows here (start frame, and end frame if present).
The NAS host/IP is never persisted — projects.source_path holds only the export path; the host lives in the NFS_HOST server env.
Everything cascades from clients: deleting a client removes its projects → videos → annotations/remarks/events.

Regenerate / verify against the live DB

# columns
docker exec central-db-1 psql -U central -d central -c "\d+ videos"
# foreign keys
docker exec central-db-1 psql -U central -d central -Atc "
SELECT tc.table_name, kcu.column_name, ccu.table_name, rc.delete_rule
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu USING (constraint_name)
JOIN information_schema.constraint_column_usage ccu USING (constraint_name)
JOIN information_schema.referential_constraints rc USING (constraint_name)
WHERE tc.constraint_type='FOREIGN KEY';"

6.8 KiB Raw Permalink Blame History Unescape Escape