Skip to main content

Workflow Tables & DataHub Board Engine

Part 1 – What DataHub Is and Why It Exists​

Workflow Tables (DataHub Board Engine)

Preface — How This Fits Into the Interface & Integration Layer​

This chapter sits inside the Interface & Integration section because it defines the
shared workflow data structures that the entire platform – UI, agents, comms tools,
email handlers, logs, automations, and voice/SMS channels – interact with.

Other chapters describe transport (voice, chat, comms), interface layers (admin UI,
email ingestion), and agent orchestration.
This chapter defines the structured data engine they all read and write to.

Everything in this document provides:

  • the canonical workflow data model for flexible boards and processes
  • the common schema used by backend services and Reggie agents
  • the integration layer between interfaces (UI, chat, voice) and operational data
  • the shared tags that enable validation, privacy, routing, and context intelligence

This is why it belongs in this section:
it is the bridge between interfaces and real backend behaviour.

1. High-Level Architecture​

[ core_source ]  -->  [ core_ops ]  -->  [ datahub (board engine) ]
                                          /          |          \
                                   [ SC views ]   [ Activities ]  [ Business ]
  • core_source holds canonical entities (participants, staff, vehicles, venues).
  • core_ops holds canonical operations (finance, SCHADS, incidents, etc.).
  • datahub is the shared workflow/board engine — boards, rows, cells, tags, metadata.
  • “Workspaces” (SC, Activities, Business, Transport, etc.) are expressed via board context:
    • datahub.boards.context_bucket
    • plus naming / key conventions.

Interfaces (admin UI, email handlers, SMS/voice, agents) do not hardcode incident/task schemas.
They instead talk to DataHub using the structures defined here.

2. Conceptual Model​

Board
  ├─ Tables (groups)
  ├─ Columns (fields)
  │    └─ Dropdown Options (status / dropdown values)
  ├─ Rows (items)
  │    ├─ Cells (field values)
  │    └─ Comments
  └─ Rules (automations)

And the tags:

  • Board-level: overall context (context_bucket).
  • Table-level: workflow stage bucket for rows in that table (bucket).
  • Row-level: reason, validation, origin, disposition, vector embedding.
  • Column-level: comms policy (who may see this type of data).

Cells are intentionally dumb:

  • Cell = “value of this column for this row”.
  • All semantics (colours, actions, policies) are defined at board/column/row level, not per-cell.

3. DataHub Schema Overview​

All tables in this chapter live under the datahub schema.

datahub.boards          -- boards registry
  ├─ datahub.tables     -- groups on that board
  ├─ datahub.columns    -- fields on that board
  │    └─ datahub.dropdown_options -- options for status/dropdown columns
  ├─ datahub.rows       -- items on that board
  │    ├─ datahub.cells       -- one value per (row, column)
  │    └─ datahub.comments    -- row comments (optional)
  └─ datahub.rules      -- automation rules (optional/next stage)

4. Boards​

4.1 datahub.boards​

One row per workflow board.

Examples:

  • “SC Incident Workflow”
  • “Roster Exceptions”
  • “Business Ideas Backlog”
  • “Vehicle Maintenance Tasks”

Key columns:

  • id (UUID, PK)
  • key (TEXT, unique)
  • name (TEXT)
  • description (TEXT)
  • context_bucket (TEXT)
  • is_active (BOOLEAN)
  • created_at (TIMESTAMPTZ)
  • created_by (UUID)
  • updated_at (TIMESTAMPTZ)

5. Tables (Groups)​

5.1 datahub.tables​

One row per group inside a board.

Key columns:

  • id (UUID, PK)
  • board_id (UUID)
  • name (TEXT)
  • description (TEXT)
  • position (INT)
  • color (TEXT)
  • bucket (TEXT, nullable)
  • is_default (BOOLEAN)
  • created_at (TIMESTAMPTZ)
  • created_by (UUID)
  • updated_at (TIMESTAMPTZ)

6. Columns (Fields)​

6.1 datahub.columns​

One row per field/column.

Key columns:

  • id
  • board_id
  • key
  • label
  • type_id
  • description
  • position
  • width_px
  • is_required
  • is_hidden
  • allow_multiple_files
  • cpol
  • created_at
  • created_by
  • updated_at

7. Dropdown & Status Options​

7.1 datahub.dropdown_options​

One option per row per (board, column)

Fields:

  • id
  • board_id
  • column_id
  • value_key
  • label
  • color
  • position
  • has_action
  • is_active
  • created_at
  • created_by
  • updated_at

8. Rows (Items)​

8.1 datahub.rows​

Fields:

  • row_id
  • board_id
  • table_id
  • rtag
  • vtag
  • otag
  • dtag
  • is_deleted
  • vector_id
  • cpol
  • created_at
  • created_by
  • updated_at

9. Cells (Field Values)​

9.1 datahub.cells​

Fields:

  • board_id
  • row_id
  • column_id
  • type_id
  • value_text
  • value_num
  • value_bool
  • value_date
  • value_key
  • value_json
  • edited_by
  • updated_at

Example file object for value_json:

[
  {
    "url": "https://files.example.com/storage/abc123.pdf",
    "name": "Risk_Assessment_2025-01.pdf",
    "mime": "application/pdf"
  }
]

10. Comments​

10.1 datahub.comments​

Fields:

  • id
  • board_id
  • row_id
  • author_id
  • body
  • mentions
  • created_at
  • is_hidden

11. Rules (Automations)​

11.1 datahub.rules​

Fields:

  • id
  • board_id
  • name
  • is_enabled
  • trigger_type
  • trigger_conf (JSON)
  • actions_conf (JSON)
  • position
  • created_at
  • created_by
  • updated_at

12. Embeddings & Intelligence​

Rows can be embedded using:

  • board name + description + context bucket
  • table name + bucket
  • row fields
  • rtag, vtag, otag

Output vector is stored under vector_id.

Agents use this to:

  • find similar rows
  • link related workflows
  • explain decisions
  • pull in context automatically

13. Summary​

DataHub provides:

  • flexible workflow engine
  • strong metadata
  • clear comms policies
  • row-level reasoning tags
  • stable structure for frontend & backend
  • semantic intelligence layer for agents

Part 2 – Conceptual Model (Boards, Tables, Rows, Cells, Comments, Rules)​

DataHub Board Engine Specification (Updated)

This document describes the unified DataHub board engine used across interfaces, automations, agents, workflow tables, and UI. This replaces and supersedes the earlier draft, incorporating all high‑level concepts, tags, buckets, validation logic, display modes, and the SQL schema.

1. Boards (pages) (datahub.boards)​

Each board is its own workflow environment.

Columns​

  • id UUID PK
  • key TEXT UNIQUE — stable identifier
  • name TEXT — display name
  • description TEXT
  • context_bucket TEXT — SC, Staff, Transport, Business, etc.
  • workspace_key TEXT — determines which workspace menu shows the board
  • display_mode TEXT NOT NULL DEFAULT 'standard'
    • 'compact' – dense table
    • 'standard' – medium-density (default)
    • 'featured' – product/log hybrid with thumbnails
  • is_active BOOLEAN
  • created_at, created_by, updated_at

2. Tables (Groups) (datahub.tables)​

Groups can represent workflow stages, multiple tables or information compartmentalisation (groups) on the same page make up a board

Columns​

  • id UUID PK
  • board_id FK
  • name
  • description
  • position
  • color
  • bucket — table-specific workflow bucket
  • is_default
  • timestamps + created_by

3. Columns (Fields) (datahub.columns)​

Each column describes how a field should behave, and dictates who should have access to the information in each column.

Key fields​

  • key — stable per-board uniquely
  • label — display
  • type_id — one of:
    • text, number, bool, date, json
    • status
    • dropdown
    • dropdown_multi
    • file
  • width_px
  • is_required
  • is_hidden
  • allow_multiple_files
  • cpol — comms policy
  • timestamps

4. Dropdown Options (datahub.dropdown_options)​

Used for status, dropdown, and multi-dropdown types.

Fields​

  • value_key
  • label
  • color
  • position
  • has_action
  • is_active

5. Rows (datahub.rows)​

Represents the “item” in the workflow.

Per-row metadata​

  • rtag — reason tag
  • vtag — validation state
  • otag — origin
  • dtag — AI determination and suggestions (post conclusion) Brainfram v2 post task row analysis short report
  • cpol — row-level override
  • vector_id — pointer to embedding record
  • is_deleted

6. Cells (datahub.cells)​

EAV structure — one row per cell:

Typed values:​

  • value_text
  • value_num
  • value_bool
  • value_date
  • value_key
  • value_json
  • type_id (copy of column.type_id at write time)

File support​

value_json stores array:

[
  { "url": "...", "name": "file.pdf", "mime": "application/pdf", "size": 12345 }
]

7. Comments (datahub.comments)​

Each row can have threaded comments.

Fields​

  • body
  • author_id
  • mentions JSONB
  • is_hidden
  • timestamps

8. Rules / Automations (datahub.rules)​

Event-triggered workflow logic.

Trigger types​

  • cell_changed
  • row_created

Actions​

  • move row
  • set column value
  • send notification
  • emit log
  • call webhook

9. Integration With Agents​

Agents use:

  • board metadata
  • column schema
  • row context
  • tags (rtag/vtag/otag/dtag)
  • context buckets
  • validation models
  • vector embeddings

10. SQL (Full Script)​

Refer to the generated SQL migration in the DataHub schema.

Part 3 – Workflow Engine & Table Semantics​

Workflow Tables (DataHub Board Engine)

Preface — How This Fits Into the Interface & Integration Layer​

This chapter sits inside the Interface & Integration section because it defines the
shared workflow data structures that the entire platform – UI, agents, comms tools,
email handlers, logs, automations, and voice/SMS channels – interact with.

Other chapters describe transport (voice, chat, comms), interface layers (admin UI,
email ingestion), and agent orchestration.
This chapter defines the structured data engine they all read and write to.

Everything in this document provides:

  • the canonical workflow data model for flexible boards and processes
  • the common schema used by backend services and Reggie agents
  • the integration layer between interfaces (UI, chat, voice) and operational data
  • the shared tags that enable validation, privacy, routing, and context intelligence

This engine does not replace canonical data. Canonical, regulated, permanent data lives in:

  • core_source — participants, staff, vehicles, venues
  • core_ops — finance, SCHADS, schedules, timesheets, incidents, billing

The workflow engine is a flexible processing layer that lets teams create, adjust, and operate new workflows without requiring database migrations.

1. High-Level Architecture​

[ core_source ]  -->  [ core_ops ]  -->  [ datahub (board engine) ]
                                          /          |          \
                                   [ SC views ]   [ Activities ]  [ Business ]
  • core_source holds canonical entities (participants, staff, vehicles, venues).
  • core_ops holds canonical operations (finance, SCHADS, incidents, etc.).
  • datahub is the shared workflow/board engine — boards, rows, cells, tags, metadata.

“Workspaces” (SC, Activities, Business, Transport, etc.) are expressed via board context:

  • datahub.boards.context_bucket
  • plus naming/key conventions (e.g. SC_INCIDENTS_MAIN).

Interfaces (admin UI, email handlers, SMS/voice, agents) do not hardcode incident/task schemas.
They instead talk to DataHub using the structures defined here.

2. Conceptual Model​

Board
  ├─ Tables (groups)
  ├─ Columns (fields)
  │    └─ Dropdown Options (status / dropdown values)
  ├─ Rows (items)
  │    ├─ Cells (field values)
  │    └─ Comments
  └─ Rules (automations)

Tagging layers:

  • Board-level: overall context (context_bucket – roughly “workspace”).
  • Table-level: workflow stage bucket for rows in that table (bucket).
  • Row-level: reason, validation, origin, disposition, vector embedding.
  • Column-level: comms policy (who may see this type of data).

Cells are intentionally dumb:

  • Cell = “value of this column for this row”.
  • All semantics (colours, actions, policies) are defined at board/column/row level, not per-cell.

3. DataHub Schema Overview​

All tables in this chapter live under the datahub schema.

datahub.boards          -- boards registry
  ├─ datahub.tables     -- groups on that board
  ├─ datahub.columns    -- fields on that board
  │    └─ datahub.dropdown_options -- options for status/dropdown columns
  ├─ datahub.rows       -- items on that board
  │    ├─ datahub.cells       -- one value per (row, column)
  │    └─ datahub.comments    -- row comments (optional)
  └─ datahub.rules      -- automation rules (optional/next stage)

4. Boards​

4.1 datahub.boards​

One row per workflow board.

Examples:

  • “SC Incident Workflow”
  • “Roster Exceptions”
  • “Business Ideas Backlog”
  • “Vehicle Maintenance Tasks”

Key columns:

  • id (UUID, PK) — internal identifier
  • key (TEXT, unique) — stable key, e.g. SC_INCIDENTS_MAIN
  • name (TEXT) — human-readable name
  • description (TEXT)
  • context_bucket (TEXT) — workspace / domain label (SC, Transport, Business, etc.)
  • is_active (BOOLEAN)
  • created_at, created_by, updated_at (standard audit fields)

Semantics:

  • context_bucket tells Reggie what “mental drawer” this board belongs in.
  • Boards are the join point for UI, rules, and agent routing.

5. Tables (Groups)​

5.1 datahub.tables​

One row per group inside a board.

Key columns:

  • id (UUID, PK)
  • board_id (UUID → datahub.boards.id)
  • name (TEXT) — e.g. “Intake”, “In Progress”, “Completed”
  • description (TEXT)
  • position (INT) — left-to-right / top-to-bottom order on board
  • color (TEXT) — accent colour for this group
  • bucket (TEXT, nullable) — specific workflow bucket for this table
  • is_default (BOOLEAN) — new rows land here if not otherwise specified
  • created_at, created_by, updated_at

Semantics:

  • If bucket is NULL, the board’s context_bucket effectively applies.
  • If bucket is set (e.g. SC_INCIDENT_TRIAGE vs SC_INCIDENT_CLOSED), that table can have a more precise meaning than the board as a whole.
  • Exactly one table should be is_default = TRUE per board – enforced at app level or via partial unique index.

6. Columns (Fields)​

6.1 datahub.columns​

One row per field/column.

Key columns:

  • id (UUID, PK)
  • board_id (UUID) — columns are board-scoped
  • key (TEXT) — stable key per board (e.g. status, incident_title)
  • label (TEXT) — display label
  • type_id (TEXT) — logical type (see below)
  • description (TEXT)
  • position (INT) — left-to-right order
  • width_px (INT, optional) — display width hint
  • is_required (BOOLEAN)
  • is_hidden (BOOLEAN)
  • allow_multiple_files (BOOLEAN) — only meaningful when type_id='file'
  • cpol (TEXT) — comms policy for this column (who may see this type of data)
  • created_at, created_by, updated_at

Allowed type_id values (current set):

  • text
  • number
  • bool
  • date
  • json
  • status
  • dropdown
  • dropdown_multi
  • file

Column-level comms:

  • cpol is the “default” comms policy for values in this column (e.g. admin_only, staff_only, participant_ok).
  • Row-level cpol can further tighten or override this per item.

7. Dropdown & Status Options​

7.1 datahub.dropdown_options​

One option per row per (board_id, column_id, value_key).

Fields:

  • id (UUID, PK)
  • board_id (UUID)
  • column_id (UUID → datahub.columns.id)
  • value_key (TEXT) — internal key: pending, in_progress, complete
  • label (TEXT) — display label
  • color (TEXT) — e.g. gr, rd, or #22c55e
  • position (INT) — sort order within this status/dropdown
  • has_action (BOOLEAN) — whether selecting this should trigger a rule/action
  • is_active (BOOLEAN)
  • created_at, created_by, updated_at

Semantics:

  • Status columns and dropdown columns both use this table.
  • has_action is a hint that rules exist; the actual behaviour lives in datahub.rules.

8. Rows (Items)​

8.1 datahub.rows​

Fields:

  • row_id (UUID, PK)
  • board_id (UUID)
  • table_id (UUID → datahub.tables.id)

Per-row metadata:

  • rtag — reason tag (linking to Reggie’s decisions / Reason Log)
  • vtag — validation state (unvalidated, auto, human_ok, rejected, etc.)
  • otag — origin tag (how/where did this come from – import, email, board X, etc.)
  • dtag — disposition (why this is resolved, rejected, superseded, etc.)
  • is_deleted — hard “hidden” flag for the UI / soft delete

Additional fields:

  • vector_id — pointer to embedding record (semantic vector for this row)
  • cpol — row-level comms policy override (tightens or replaces column defaults)
  • created_at, created_by, updated_at

How dtag and is_deleted work together:

  • You can have rows where dtag='resolved_ok' and is_deleted = FALSE — they remain visible but clearly marked as resolved.
  • is_deleted = TRUE = “hide this from normal views”, but we still keep the row for audit / Reggie.
  • dtag is what Reggie uses to understand “how the story ended”, even if the item remains visible for reference.

9. Cells (Field Values)​

9.1 datahub.cells​

EAV structure — one row per cell.

Fields:

  • board_id (UUID → datahub.boards.id)
  • row_id (UUID → datahub.rows.row_id)
  • column_id (UUID → datahub.columns.id)
  • type_id (TEXT) — copy of columns.type_id at write time

Typed values:

  • value_text (TEXT)
  • value_num (NUMERIC)
  • value_bool (BOOLEAN)
  • value_date (TIMESTAMPTZ)
  • value_key (TEXT) — for status/dropdown values (matches dropdown_options.value_key)
  • value_json (JSONB) — for json, dropdown_multi, file, etc.

Audit:

  • edited_by (UUID)
  • updated_at (TIMESTAMPTZ)

File support (single & multi):

For type_id='file':

  • If allow_multiple_files = FALSE – treat value_json as array with 0 or 1 objects.
  • If allow_multiple_files = TRUE – same shape, but UI allows many.

Each file object:

[
  {
    "url": "https://files.example.com/storage/abc123.pdf",
    "name": "Risk_Assessment_2025-01.pdf",
    "mime": "application/pdf",
    "size": 12345
  }
]

Validation & types:

  • Backend enforces mapping:
    • type_id='text' → value_text
    • type_id='number' → value_num
    • type_id='status' → value_key (plus dropdown_options check)
    • etc.
  • More complex validation (emails, phone numbers, addresses) is done in app logic before write, but the shape is always one of the core types above.

10. Comments​

10.1 datahub.comments​

Fields:

  • id (UUID, PK)
  • board_id (UUID)
  • row_id (UUID → datahub.rows.row_id)
  • author_id (UUID)
  • body (TEXT)
  • mentions (JSONB) — array of mentioned user IDs + medallion metadata, for notifications
  • created_at (TIMESTAMPTZ)
  • is_hidden (BOOLEAN)

Usage:

  • Per-row discussion threads.
  • Notifications are generated based on mentions and board/table context.
  • Comments obey the same cpol rules as their parent row and columns.

11. Rules (Automations)​

11.1 datahub.rules​

Fields:

  • id (UUID, PK)
  • board_id (UUID)
  • name (TEXT)
  • is_enabled (BOOLEAN)
  • trigger_type (TEXT)
  • trigger_conf (JSONB)
  • actions_conf (JSONB)
  • position (INT)
  • created_at, created_by, updated_at

Trigger types (current plan):

  • cell_changed — fires when specified column(s) change from X → Y
  • row_created — fires when a new row is created on a given board/table

Action types (current plan):

  • move_row — change table_id (and optionally bucket)
  • set_cell_value — set a value in a particular column
  • send_notification — push a notification (email, SMS, in-app)
  • emit_log — write to system logs for audit
  • call_webhook — invoke external system (future)

trigger_conf and actions_conf are JSON, so we can introduce new trigger/action types without DB migrations.

Example (status change → move row):

{
  "trigger_type": "cell_changed",
  "trigger_conf": {
    "column_key": "status",
    "from": ["in_progress", "stuck"],
    "to": ["complete"]
  },
  "actions_conf": [
    {
      "type": "move_row",
      "target_table_id": "UUID-OF-COMPLETED-TABLE"
    }
  ]
}

12. Embeddings & Intelligence​

Rows can be embedded using:

  • board name + description + context_bucket
  • table name + bucket
  • row fields (selected subset)
  • rtag, vtag, otag

Output vector is stored under rows.vector_id.

Agents use this to:

  • find similar rows
  • link related workflows
  • explain decisions and their history
  • pull in context automatically for questions like “Why was this incident closed?”

13. Behaviour & Lifecycle (Worked Example)​

  1. Intake

    • A new incident is submitted.
    • Board: SC_INCIDENTS_MAIN.
    • Table: Intake (bucket SC_INCIDENT_INTAKE).
    • Row created with:
      • otag='sc_portal_form'
      • rtag='incident_reported'
      • vtag='unvalidated'
      • cpol='admin_only'

  2. Triage

    • Staff update fields (status, notes, assigned staff).
    • A rule watches for status: "triaged".
    • On transition:
      • table_id moves to the “Triage” table.
      • rtag set to 'triage_started'.
      • vtag might move to 'auto_assessed'.

  3. Resolution

    • When status becomes complete:
      • Rule moves row to “Resolved” table (bucket SC_INCIDENT_RESOLVED).
      • dtag='resolved_no_escalation'.
      • vtag='human_ok' once a supervisor signs off.

  4. Archival / Hiding

    • If a row must be hidden from normal views:
      • is_deleted = TRUE
      • dtag='archived_policy'.

  5. Agent query

    • Agent (Reggie) receives: “Has anything like this happened with Bob before?”
    • Uses vector_id to retrieve similar rows and rtag/dtag to filter to relevant history,
      while respecting cpol and requester permissions.

14. Implementation Notes​

Backend service responsibilities:

  • Enforce type_id → value_* mapping.
  • Ensure status/dropdown values exist in datahub.dropdown_options.
  • Assign sensible defaults for vtag (imported, unverified, etc.).
  • Manage bucket, rtag, otag, dtag, is_deleted on lifecycle transitions.
  • Set cpol based on business rules + column defaults.

Frontend principles:

  • /boards/:boardId is a generic board view page (HTML + vanilla JS).
  • It loads:
    • board metadata
    • tables + columns + dropdown options
    • first page of rows
    • cells for those rows (via a generic API)
  • It never hardcodes columns for any specific board; everything is driven from metadata.

Promotion path:

  • If a workflow stabilises into a strict domain model (e.g. Support Coordination case management), you can:
    • Design strict tables in a dedicated schema (e.g. sc_core.*).
    • Migrate data from datahub into the strict schema using otag and tags.
    • Lock down or retire the board when appropriate.

15. Summary​

DataHub provides:

  • a flexible workflow engine
  • strong, layered metadata (rtag, vtag, otag, dtag, buckets)
  • clear comms policies (cpol on columns and rows)
  • a stable structure for frontend & backend
  • a semantic intelligence layer for agents via vector_id

It is the bridge between flexible UI/agents and the regulated core datasets. Developers and agents should treat this file as the canonical reference when building or querying workflows.

Part 4 – Schema-Level Reference​

Workflow Tables (Flex Board Engine)

Preface — How This Fits Into the Interface & Integration Layer​

This chapter sits inside the Interface & Integration section because it defines the
shared workflow data structures that the entire platform – UI, agents, comms tools,
email handlers, logs, automations, and voice/SMS channels – interact with.

Other chapters describe transport (voice, chat, comms), interface layers (admin UI,
email ingestion), and agent orchestration.
This chapter defines the structured data engine they all read and write to.

Everything in this document provides:

  • the canonical workflow data model for flexible boards and processes
  • the common schema used by backend services and Reggie agents
  • the integration layer between interfaces (UI, chat, voice) and operational data
  • the shared tags that enable validation, privacy, routing, and context intelligence

This is why it belongs in this section:
it is the bridge between interfaces and real backend behaviour.

2. Architecture Overview​

2.1 Core layers​

[ core_source ]  -->  [ core_ops ]  -->  [ flex (board engine) ]
                                          /        |        \
                                   [scdata]    [adata]    [bdata]
  • core_source holds canonical entities (participants, staff, vehicles, venues).
  • core_ops holds canonical operations (finance, SCHADS, incidents, etc) and global registries.
  • flex is the shared workflow/board engine.
  • scdata, adata, bdata, etc. are views over flex for each domain.

2.2 Schemas and domains​

SchemaPurpose
core_sourceCanonical entities — participants, staff, vehicles, venues
core_opsCanonical operations — finance, SCHADS, timesheets, incidents; global registries
flexThe workflow/board engine — boards, rows, cells, tags, metadata

Each workflow “workspace” (SC, Activities, Business, Transport, etc) is expressed by a value in the domain column on flex.*.

We expose each domain via views:

CREATE VIEW scdata.board_rows AS
  SELECT * FROM flex.board_rows WHERE domain = 'scdata';

CREATE VIEW adata.board_rows AS
  SELECT * FROM flex.board_rows WHERE domain = 'adata';

Domains behave like separate workspaces while sharing one engine.

3. Core Registries (core_ops)​

These registries define what a board is, what columns exist, how they behave, and what enumerations are allowed.

[ board_registry ]      [ column_registry ]
          \                /
           \              /
             [ flex.* uses both ]
                     |
               [ enum_options ]

3.1 Board Registry​

core_ops.board_registry (
  id          UUID PRIMARY KEY,
  code        TEXT UNIQUE NOT NULL,
  name        TEXT NOT NULL,
  description TEXT,
  domain      TEXT NOT NULL,
  active      BOOLEAN NOT NULL DEFAULT TRUE
);
  • code is a stable identifier (e.g. SC_ACTIVE_CASES).
  • domain associates the board with a logical workspace.

3.2 Column Registry​

core_ops.column_registry (
  id               UUID PRIMARY KEY,
  key              TEXT UNIQUE NOT NULL,
  label            TEXT NOT NULL,
  type_id          TEXT NOT NULL,
  default_width_px INT,
  default_align    TEXT,
  default_views    JSONB,
  icon             TEXT,
  help_text        TEXT
);
  • key is a stable identifier (e.g. participant_name, status, incident_date).
  • type_id is one of: text, number, bool, date, enum, json, etc.
  • Default width/align/views can be overridden per board but live here by default.

3.3 Enum Options​

core_ops.enum_options (
  column_id  UUID NOT NULL REFERENCES core_ops.column_registry(id),
  value_key  TEXT NOT NULL,
  label      TEXT NOT NULL,
  sort_index INT NOT NULL DEFAULT 0,
  active     BOOLEAN NOT NULL DEFAULT TRUE,
  PRIMARY KEY (column_id, value_key)
);

Per-column enums, not a global all-in-one enum type.

4. Flex Engine Tables​

At a high level, the flex engine looks like this:

[ core_ops.board_registry ]      [ core_ops.column_registry ]
                 |                         |
                 v                         v
             [ flex.boards ]       [ flex.board_columns ]
                      \           /
                       v         v
                       [ flex.board_rows ]
                                |
                                v
                         [ flex.board_cells ]

4.1 Boards (flex.boards)​

flex.boards (
  id           UUID PRIMARY KEY,
  registry_id  UUID NOT NULL REFERENCES core_ops.board_registry(id),
  domain       TEXT NOT NULL,
  name_override TEXT,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by   UUID,
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
  • registry_id references the global definition.
  • domain is redundant with board_registry.domain but convenient for queries.

4.2 Board Columns (flex.board_columns)​

flex.board_columns (
  board_id       UUID NOT NULL REFERENCES flex.boards(id),
  column_id      UUID NOT NULL REFERENCES core_ops.column_registry(id),
  position       INT NOT NULL,
  width_px       INT,
  is_hidden      BOOLEAN NOT NULL DEFAULT FALSE,
  views_override JSONB,
  PRIMARY KEY (board_id, column_id)
);

This is the per-board mapping of global columns.

4.3 Board Rows (flex.board_rows)​

This table represents entities/items/tasks/incidents/etc in a board.

flex.board_rows (
  row_id        UUID PRIMARY KEY,
  board_id      UUID NOT NULL REFERENCES flex.boards(id),
  domain        TEXT NOT NULL,
  group_id      UUID,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by    UUID,
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),

  bucket        TEXT,  -- workflow bucket
  rtag          TEXT,  -- reason tag
  otag          TEXT,  -- origin tag
  dtag          TEXT,  -- delete/disposition tag
  is_deleted    BOOLEAN NOT NULL DEFAULT FALSE,

  vector_id     UUID   -- for semantic search/context
);

Quick mental diagram:

[ flex.board_rows ]
   |  row_id
   |  board_id
   |  domain
   |  group_id
   |  bucket  / rtag / otag
   |  dtag    / is_deleted
   |  vector_id

Recommended indexes:

CREATE INDEX ON flex.board_rows (domain, board_id, is_deleted, created_at DESC, row_id);
CREATE INDEX ON flex.board_rows (board_id, bucket);
CREATE INDEX ON flex.board_rows (domain, board_id, group_id);

4.4 Board Cells (flex.board_cells)​

This is the typed EAV layer. Each row holds one cell.

flex.board_cells (
  board_id   UUID NOT NULL REFERENCES flex.boards(id),
  row_id     UUID NOT NULL REFERENCES flex.board_rows(row_id),
  column_id  UUID NOT NULL REFERENCES core_ops.column_registry(id),
  domain     TEXT NOT NULL,

  type_id    TEXT NOT NULL,

  value_text TEXT,
  value_num  NUMERIC,
  value_bool BOOLEAN,
  value_date DATE,
  value_json JSONB,

  vtag       TEXT,  -- validation tag
  cpol       TEXT,  -- comms policy tag

  edited_by  UUID,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

  PRIMARY KEY (board_id, row_id, column_id)
);

Diagram for how cells relate:

(board_id, row_id, column_id)  -->  one cell

type_id   --> which value_* column is used
vtag      --> how it was validated
cpol      --> who is allowed to see/use it

4.4.1 Type Enforcement​

ALTER TABLE flex.board_cells
ADD CONSTRAINT chk_board_cells_type_match
CHECK (
  (type_id='text'   AND value_text IS NOT NULL AND value_num IS NULL AND value_bool IS NULL AND value_date IS NULL AND value_json IS NULL)
  OR
  (type_id='number' AND value_num IS NOT NULL AND value_text IS NULL AND value_bool IS NULL AND value_date IS NULL AND value_json IS NULL)
  OR
  (type_id='bool'   AND value_bool IS NOT NULL AND value_text IS NULL AND value_num IS NULL AND value_date IS NULL AND value_json IS NULL)
  OR
  (type_id='date'   AND value_date IS NOT NULL AND value_bool IS NULL AND value_text IS NULL AND value_num IS NULL AND value_json IS NULL)
  OR
  (type_id='enum'   AND value_text IS NOT NULL AND value_num IS NULL AND value_bool IS NULL AND value_date IS NULL AND value_json IS NULL)
  OR
  (type_id='json'   AND value_json IS NOT NULL AND value_text IS NULL AND value_num IS NULL AND value_bool IS NULL AND value_date IS NULL)
);

4.4.2 Enum Validation​

ALTER TABLE flex.board_cells
ADD CONSTRAINT fk_board_cells_enum_option
FOREIGN KEY (column_id, value_text)
REFERENCES core_ops.enum_options(column_id, value_key)
DEFERRABLE INITIALLY IMMEDIATE;
CREATE INDEX ON flex.board_cells (domain, board_id, row_id);
CREATE INDEX ON flex.board_cells (board_id, column_id);
CREATE INDEX ON flex.board_cells (domain, board_id, column_id);

You can also add partial indexes for hot numeric/date filters, for example:

CREATE INDEX ON flex.board_cells (board_id, column_id, value_num)
WHERE type_id = 'number';

5. Tag Semantics​

Tags exist at two levels:

[ board_rows ]  --> row-level meaning & lifecycle
[ board_cells ] --> per-field validation & comms policy

5.1 Row Tags (on board_rows)​

TagMeaning
bucketHigh-level workflow stage (e.g. new, triage, working, done)
rtagReason tag: why the row exists or changed (client_request, system_escalation, etc.)
otagOrigin tag: pointer back to canonical/master/intake record
dtagDelete/disposition descriptor (user_deleted, policy_cleanup, etc.)
is_deletedBoolean soft-delete flag; board views ignore deleted rows by default
vector_idReference used for semantic search / context buckets

Typical otag formats:

"core_ops.incidents:<uuid>"
"import:file:<file_id>"
"api:twilio:<message_sid>"

The implementation can be a simple string or a small JSON object; the important part is that an agent or service can resolve it back to a concrete source record if needed.

5.2 Cell Tags (on board_cells)​

5.2.1 vtag — Validation Tag​

Indicates how the value was obtained or validated, e.g.:

  • auto_assigned — filled by a rule or automation
  • human_reviewed — explicitly confirmed by a person
  • imported — imported from an external file/system
  • system_generated — generated by deterministic logic
  • llm_suggested — suggested by an LLM, not yet confirmed
  • unverified — no validation has been applied yet

Agents and UI can use vtag to:

  • Highlight fields needing human review
  • Prefer human_reviewed values over unverified ones
  • Decide which fields can be trusted in high-stakes actions

5.2.2 cpol — Communication Policy​

Defines who may see or receive this cell’s value, based on the requester’s identity and role(s).

Example policy values (these are conventions; you can formalise them in a policy_registry if desired):

  • admin_only
  • staff_only
  • participant_only
  • admin_and_participant
  • linked_uuid_only
  • internal_notes_only
  • public_metadata
  • restricted:role:SC
  • restricted:user:<uuid>

At runtime:

  • API and agents determine who is asking (user id, participant id, roles).
  • For each cell they want to use/return, they check cpol.
  • If the policy does not allow disclosure, the value is hidden or redacted.

Important:

  • cpol is not enforced by PostgreSQL constraints.
  • Enforcement happens in:
    • API/service layer
    • Agent runtime logic
    • Any consumer responsible for enforcing data safety and privacy

6. Domain Views​

To keep queries and permissions simple, define per-domain views:

CREATE VIEW scdata.board_rows AS
  SELECT * FROM flex.board_rows WHERE domain = 'scdata';

CREATE VIEW scdata.board_cells AS
  SELECT * FROM flex.board_cells WHERE domain = 'scdata';

CREATE VIEW adata.board_rows AS
  SELECT * FROM flex.board_rows WHERE domain = 'adata';

CREATE VIEW adata.board_cells AS
  SELECT * FROM flex.board_cells WHERE domain = 'adata';

From here, tools and agents can be pointed at scdata.*, adata.*, etc, instead of querying flex directly.

7. Query Patterns​

7.1 Fetch paged rows for a board​

First page (latest first):

SELECT *
FROM flex.board_rows
WHERE domain    = :domain
  AND board_id  = :board_id
  AND is_deleted = false
ORDER BY created_at DESC, row_id
LIMIT 100;

This uses the index:

CREATE INDEX ON flex.board_rows (domain, board_id, is_deleted, created_at DESC, row_id);

7.2 Keyset (cursor) pagination​

Avoid large OFFSET. Use a cursor from the last row of the previous page:

SELECT *
FROM flex.board_rows
WHERE domain    = :domain
  AND board_id  = :board_id
  AND is_deleted = false
  AND (
    created_at < :last_created_at
    OR (created_at = :last_created_at AND row_id > :last_row_id)
  )
ORDER BY created_at DESC, row_id
LIMIT 100;

The frontend keeps last_created_at and last_row_id as the page cursor.

7.3 Fetch cells for a page of rows​

Once you have a page of row_ids:

SELECT *
FROM flex.board_cells
WHERE board_id = :board_id
  AND row_id   = ANY(:row_ids);

The frontend pivots (row_id, column_id) into a grid using column metadata.

8. Example Workflow: Incident Intake​

Inline big-picture view:

[ core_ops.incidents ]  -- canonical record
           |
           v
[ flex.board_rows / SC_INCIDENT_WORKFLOW ]
           |
           v
[ flex.board_cells + tags (vtag, cpol, etc.) ]

Steps​

  1. Incident submitted into core_ops.incidents.

  2. A workflow row is created:

    • board_id = (SC incident workflow board id)
    • domain = 'scdata'
    • otag = 'core_ops.incidents:<incident_id>'
    • bucket = 'new'
    • rtag = 'incident_auto_created'
    • is_deleted = false

  3. Cells are populated in flex.board_cells:

    • Participant, date, type, status, notes, etc.
    • Some fields marked:
      • vtag = 'imported'
      • cpol = 'admin_only' for sensitive internal notes
      • cpol = 'participant_only' for fields that may be shared directly

  4. As work progresses:

    • bucket changes across stages.
    • rtag may change as reasons are updated.
    • vtag may move from auto_assigned to human_reviewed.
    • cpol can be tightened or relaxed if policies change.

  5. When an agent answers a participant or staff question:

    • Finds relevant workflow rows (by board filters or via vector_id).
    • For each candidate cell, checks:
      • cpol against requester identity/role.
    • Only uses values the requester is allowed to see.

9. Implementation Notes​

  • All writes to flex.* should go through a thin backend service that:

    • Enforces the type_id/value_* mapping (or lets the DB CHECK fail loudly).
    • Ensures enum values exist in core_ops.enum_options.
    • Assigns sensible defaults for vtag (e.g. imported, unverified).
    • Sets cpol based on business rules.
    • Manages bucket, rtag, otag, dtag, and is_deleted on lifecycle transitions.

  • Frontend:

    • /boards/:boardId is a generic board view page (HTML + vanilla JS).
    • It loads:
      • board metadata
      • board columns + column_registry
      • first page of rows
      • cells for those rows
    • It never hardcodes columns for any specific board; everything is driven from metadata.

  • Promotion path:

    • If a specific workflow stabilises into a strict domain model (e.g. Support Coordination case management), you can:
      • Design strict tables in a dedicated schema (e.g. sc_core.*).
      • Migrate data from flex into the strict schema using otag and tags.
      • Lock down or retire the board when appropriate.

10. Summary​

  • The flex engine provides a generic, typed, metadata-driven workflow layer.
  • It uses:
    • global registries in core_ops for boards, columns, and enums
    • shared flex tables for boards, rows, and cells
    • row-level and cell-level tags for workflow, reasoning, validation, and communication policy.
  • Domains (SC, Activities, Business, etc.) share one engine, isolated via domain and exposed as views.
  • The design supports:
    • operational flexibility (new boards/columns without migrations)
    • safe use by agents (via vtag and cpol)
    • later promotion to strict canonical schemas when workflows mature.

This file is the canonical reference for implementing the workflow/board engine in the database and backend.

Part 5 – Frontend Experience & Workspaces​

Task: Build DataHub Workspace & Board Frontend (Cyber Admin Template)

Goal​

Implement a generic, metadata-driven workspace + board UI for the DataHub board engine using the existing Cyber Admin Bootstrap theme. The UI must:

  • Work for any board defined in datahub.boards (no hard-coded schemas).
  • Support three display modes (compact, standard, featured rows).
  • Allow users to design boards (tables, columns, dropdowns) from the UI.
  • Allow users to create/edit rows, change statuses, attach files, and comment.
  • Talk only to a set of generic DataHub APIs (described in 20_datahub_backend_task.md).

You are NOT designing visual styles from scratch – re-use Cyber Admin cards, tables, badges, forms, and avatars as described in the existing task_helper_* docs.

1. Context / Data Model (Read-only)​

You do not touch SQL. Assume the DB schema is exactly as documented in 10_Workflow_Tables_DataHub_Board_Engine.md:

Key tables:

  • datahub.boards — boards registry
  • datahub.tables — groups on that board
  • datahub.columns — fields on that board
  • datahub.dropdown_options — status / dropdown values
  • datahub.rows — items on that board
  • datahub.cells — one value per (row, column)
  • datahub.comments — row comment threads
  • datahub.rules — rules/automations

You will only interact with JSON APIs that expose this data in a UI-friendly shape (e.g. rows as { rowId, tableId, cellsByColumnKey }). Backend will take care of joins.

2. Pages & Files​

Create the following HTML pages in the admin template project:

  1. datahub_workspace.html
  2. datahub_board_compact.html
  3. datahub_board_standard.html
  4. datahub_board_featured.html

Each page should:

  • Use the Cyber Admin layout (sidebar, header, theme pickers).
  • Use standard cards, tables, forms, and list items as per the template helpers.
  • Load JS from a shared DataHub script: assets/js/datahub_board.js.

3. Workspace Page (datahub_workspace.html)​

3.1 Layout​

Use a standard full-width content area with a responsive grid of board cards:

  • Each card corresponds to a board in datahub.boards filtered by context_bucket.
  • Use the schedule-style clean cards (no glass) – simple image/icon + text + badges.

Card contents:

  • Board name
  • Short description
  • context_bucket as a subtle text/badge
  • Count of open rows (provided by API)
  • Small pill badges for key tables (e.g. Intake, In Progress, Resolved)

3.2 Behaviour​

  • On load, call GET /api/datahub/workspaces/:bucket/boards and render cards.

  • Each card has:

    • Primary click → open board in datahub_board_standard.html?boardId=....
    • Kebab menu →:
      • “Open in compact view”
      • “Open in featured view”
      • “Design board” (opens board designer for admins).

  • Provide a “+ New Board” button:

    • Opens a modal:
      • Name, description
      • Context bucket dropdown (SC, Activities, Business, etc.)
    • On submit, POST to /api/datahub/boards.
    • On success, update the cards list without full page reload.

4. Board Pages (Compact / Standard / Featured)​

All three board pages share the same JS and differ mainly in layout CSS classes:

  • Compact → denser table-sm, fewer paddings.
  • Standard → typical table, moderate spacing.
  • Featured → product-style row cards with thumbnail column and big status badges.

4.1 Shared data loading​

On page load:

  1. Read boardId from query string.
  2. Call GET /api/datahub/boards/:boardId to fetch:
    • board info
    • tables (groups)
    • columns
    • dropdown options
    • rule summary (optional for now)
  3. Call GET /api/datahub/boards/:boardId/rows with query params:
    • table_id (active group)
    • page, page_size
    • sort/filter params

Backend returns JSON like:

{
  "board": { },
  "tables": [ ],
  "columns": [ ],
  "dropdownOptions": { "columnKey": [ ] },
  "rowsPage": {
    "page": 1,
    "pageSize": 50,
    "total": 1234,
    "rows": [
      {
        "rowId": "uuid",
        "tableId": "uuid",
        "rtag": "...",
        "vtag": "...",
        "otag": "...",
        "dtag": null,
        "cpol": "admin_only",
        "cells": {
          "status": { "type": "status", "value": "in_progress", "label": "In progress", "color": "yl" },
          "title":  { "type": "text",   "value": "Follow up with James" },
          "files":  { "type": "file",   "files": [ { "url": "...", "name": "...", "mime": "..."} ] }
        }
      }
    ]
  }
}

You do not query datahub.* directly; always use the API.

4.2 Layout – Shared​

The board view should have:

  • Header bar:

    • Board name + description
    • Context bucket badge
    • Dropdown to switch display mode (compact, standard, featured)
    • Button: “Board settings” (opens Board Designer modal/panel)

  • Tabs or pill switcher across the top for tables (groups):

    • Each pill = one entry from datahub.tables for this board
    • Show name and optional badge with count

  • Main area:

    • Table/card list of rows
    • Pagination controls (page x of y, next/prev)

  • Right-side panel (collapsible):

    • Row details + comments for the selected row
    • Medallions & mentions area

4.3 Layout differences by display mode​

Compact (datahub_board_compact.html)

  • Use table table-dark table-hover table-sm with table-responsive.
  • One row = one item; minimal heights.
  • Truncate long text with text-truncate, show tooltips on hover.

Standard (datahub_board_standard.html)

  • Same core table, but no table-sm. Space enough for multiple badges.
  • Show rtag / vtag / dtag as small icon badges in an extra metadata column.

Featured (datahub_board_featured.html)

  • Use the product list style:
    • Each row becomes a card-like <tr> with:
      • Leading cell = thumbnail:
        • If a file column (e.g. photo) has an image, show it.
        • Else show grey square with a camera/file icon.
      • Main cell = title + key fields stacked vertically.
      • Right cell = big status badge + main assignee + primary date.
  • Keep row borders & hover consistent with Cyber Admin examples.

5. Board Designer (Columns, Tables, Statuses)​

The Board Designer can initially be a side drawer or modal launched from “Board settings”.

5.1 Tabs​

Use a tabbed interface within the designer:

  1. Tables
  2. Columns
  3. Dropdowns & status
  4. Automations (rules) — stubbed out UI, just future-ready

5.2 Tables tab​

  • List existing tables (name, bucket, colour, is_default, position).
  • Allow:
    • Add new table
    • Rename
    • Change colour
    • Reorder (position)
    • Toggle “Default” (exactly one per board; enforce in UI)

Talk to APIs like:

  • POST /api/datahub/boards/:boardId/tables
  • PATCH /api/datahub/tables/:tableId
  • DELETE /api/datahub/tables/:tableId (only if empty / allowed)

5.3 Columns tab​

  • List columns in board-scoped order.

  • Each row shows:

    • Label
    • Key (read-only)
    • Type (text, number, status, dropdown, dropdown_multi, file, etc.)
    • Required checkbox
    • Hidden checkbox
    • Width_px input
    • Allow multiple files (only enabled if type=file)
    • Column comms policy selector (cpol)

  • Allow reorder via drag handle.

APIs:

  • POST /api/datahub/boards/:boardId/columns
  • PATCH /api/datahub/columns/:columnId
  • DELETE /api/datahub/columns/:columnId (only if empty / allowed)

5.4 Dropdowns & status tab​

  • When user selects a column of type status/dropdown/dropdown_multi, show its options.
  • Table of options:
    • value_key (read-only or advanced)
    • label
    • colour (use theme accent or small colour picker)
    • position (sort order)
    • has_action (checkbox)
    • is_active

APIs:

  • GET /api/datahub/columns/:columnId/options
  • POST /api/datahub/columns/:columnId/options
  • PATCH /api/datahub/options/:optionId
  • DELETE /api/datahub/options/:optionId

5.5 Rules tab (stub)​

  • Simple read-only list:
    • Name
    • Trigger type
    • Enabled flag
  • You do not need to implement full rule editing yet.
  • Just shape a UI that will later bind to datahub.rules.

6. Row Creation & Editing​

You must support both:

  1. “+ New item” → modal form
  2. Inline edits for simple fields (like Monday-style cells).

6.1 New row modal​

  • Fields:

    • Table selector (defaults to current table)
    • For each visible column:
      • Render appropriate control:
        • text → <input type="text" class="form-control">
        • number → <input type="number" ...>
        • bool → checkbox / switch
        • date → datetime input
        • status / dropdown → <select> with options from API
        • dropdown_multi → <select multiple> with multi select styling
        • file → file input (support multiple when allow_multiple_files)

  • On submit:

    • Build JSON body with:
      • tableId
      • cells keyed by column key and simple values
    • POST to /api/datahub/boards/:boardId/rows.

6.2 Inline edit​

  • Clicking a cell:
    • Turns into inline input or dropdown.
    • On change/blur:
      • PATCH /api/datahub/rows/:rowId/cells with changed value.
    • On success, update table; on error, revert and show toast.

7. Comments & Mentions​

Use the chat/comment widget style from Cyber Admin.

  • Right-side panel shows:

    • Row metadata (rtag, vtag, otag, dtag)
    • Comment list (datahub.comments)

  • Each comment row:

    • Author avatar + medallion (role colour)
    • Body text with @mentions highlighted
    • Timestamp
    • Hide icon if is_hidden = TRUE (admins only)

  • Input area at bottom:

    • Textarea / single-line
    • When typing @, show a dropdown of users.
    • On submit:
      • Parse mentions → mentions JSON
      • POST /api/datahub/rows/:rowId/comments

8. Misc Requirements​

  • Use vanilla JS (no new frameworks).
  • All styling via existing Cyber Admin classes; no heavy custom CSS.
  • No direct DB access; only call the DataHub API layer.
  • Ensure pages remain responsive and handle boards with many columns by using table-responsive wrappers and horizontal scrolling.

End of task.

Part 6 – Backend Service Contracts​

Task: DataHub Board Engine Backend Service

Goal​

Implement a backend service layer that exposes the DataHub board engine through generic JSON APIs suitable for the frontend described in 10_datahub_frontend_workspace_template_task.md.

You will:

  • Use the existing PostgreSQL schema in the datahub schema (already created).
  • Implement a set of REST endpoints for:
    • workspaces & boards
    • tables
    • columns & dropdown options
    • rows & cells
    • comments
    • basic rules listing
  • Enforce type safety, comms policy hooks, and tagging conventions described in 10_Workflow_Tables_DataHub_Board_Engine.md.

You must not change table structures or constraints without explicit instruction.

1. Environment & Assumptions​

  • DB = PostgreSQL
  • Schema: datahub with tables:
    • boards, tables, columns, dropdown_options,
    • rows, cells, comments, rules.
  • You have normal access via existing Node/Express (or similar) backend.

Important: All writes to datahub.* go through this service, which is responsible for enforcing types and business rules.

2. Data Shapes (Service-Level, Not DB-Level)​

2.1 Board summary DTO​

type BoardSummary = {
  id: string,
  key: string | null,
  name: string,
  description: string | null,
  contextBucket: string | null,
  isActive: boolean,
  workspace: string | null,   // derived or direct (context bucket / naming)
  tableCount: number,
  openRowCount: number
}

2.2 Board detail DTO​

type BoardDetail = {
  board: BoardSummary & { createdAt: string, createdBy: string | null },
  tables: TableSummary[],
  columns: ColumnDetail[],
  dropdownOptionsByColumn: {
    [columnId: string]: DropdownOption[]
  },
  rules: RuleSummary[]
}

Where:

type TableSummary = {
  id: string,
  boardId: string,
  name: string,
  description: string | null,
  bucket: string | null,
  position: number,
  color: string | null,
  isDefault: boolean
}

type ColumnDetail = {
  id: string,
  boardId: string,
  key: string,
  label: string,
  typeId: string, // 'text','number','bool','date','json','status','dropdown','dropdown_multi','file'
  description: string | null,
  position: number,
  widthPx: number | null,
  isRequired: boolean,
  isHidden: boolean,
  allowMultipleFiles: boolean,
  cpol: string | null
}

type DropdownOption = {
  id: string,
  boardId: string,
  columnId: string,
  valueKey: string,
  label: string,
  color: string | null,
  position: number,
  hasAction: boolean,
  isActive: boolean
}

type RuleSummary = {
  id: string,
  name: string,
  triggerType: string,
  isEnabled: boolean,
  position: number
}

2.3 Row page DTO​

type RowPage = {
  page: number,
  pageSize: number,
  total: number,
  rows: RowItem[]
}

type RowItem = {
  rowId: string,
  boardId: string,
  tableId: string,
  rtag: string | null,
  vtag: string | null,
  otag: string | null,
  dtag: string | null,
  isDeleted: boolean,
  vectorId: string | null,
  cpol: string | null,
  createdAt: string,
  createdBy: string | null,
  updatedAt: string,
  cells: {
    [columnKey: string]: CellValue
  }
}

type CellValue =
  | { type: 'text', value: string | null }
  | { type: 'number', value: number | null }
  | { type: 'bool', value: boolean | null }
  | { type: 'date', value: string | null }
  | { type: 'status' | 'dropdown', value: string | null, label: string | null, color: string | null }
  | { type: 'dropdown_multi', values: string[], labels: string[] }
  | { type: 'json', value: any }
  | { type: 'file', files: FileInfo[] }

type FileInfo = {
  url: string,
  name: string,
  mime: string | null,
  size: number | null
}

You will assemble these from datahub.rows + datahub.cells + datahub.columns

  • datahub.dropdown_options.

3. Endpoints​

3.1 Workspaces & boards​

GET /api/datahub/workspaces/:bucket/boards​

Returns all active boards in a given logical workspace (mapped from context_bucket or key convention).

  • Path params:
    • bucket – e.g. SC, Activities, Business
  • Response:
    • BoardSummary[]

Implementation:

  • SELECT from datahub.boards WHERE context_bucket = :bucket AND is_active = true.
  • Compute tableCount from datahub.tables.
  • Compute openRowCount by counting datahub.rows with is_deleted = false for this board.

POST /api/datahub/boards​

Create a new board.

Body:

{
  "name": "SC Incident Workflow",
  "key": "SC_INCIDENTS_MAIN",
  "description": "Main board for incident handling",
  "contextBucket": "SC"
}

Behaviour:

  • Insert into datahub.boards.
  • Optionally create a default table named “Items” with is_default = true.
  • Return BoardDetail.

3.2 Tables​

POST /api/datahub/boards/:boardId/tables​

Create table (group) within a board.

Body:

{
  "name": "In Progress",
  "description": "Work in progress",
  "bucket": "SC_INCIDENT_ACTIVE",
  "color": "yl",
  "isDefault": false
}
  • Determine next position by max + 1.
  • If isDefault = true, unset any previous default in this board.

PATCH /api/datahub/tables/:tableId​

Update name, description, bucket, color, position, isDefault.

DELETE /api/datahub/tables/:tableId​

Soft rule: only allow if:

  • No rows assigned to this table, OR
  • Caller passes a moveToTableId and you reassign rows.

Handle via transaction.

3.3 Columns​

POST /api/datahub/boards/:boardId/columns​

Create a new column.

Body:

{
  "key": "status",
  "label": "Status",
  "typeId": "status",
  "description": "Primary workflow status",
  "widthPx": 160,
  "isRequired": false,
  "isHidden": false,
  "allowMultipleFiles": false,
  "cpol": "staff_only"
}

Behaviour:

  • Validate that typeId is in the allowed set.
  • Create at position = max(position)+1 for this board.
  • Enforce (board_id, key) uniqueness.

PATCH /api/datahub/columns/:columnId​

Allow updates to:

  • label, description, position, widthPx, isRequired, isHidden, allowMultipleFiles, cpol.

Do not allow board_id or key changes.

DELETE /api/datahub/columns/:columnId​

Only if:

  • No cells exist for this column; or
  • You implement a migration behaviour (out of scope initially).

3.4 Dropdown options​

GET /api/datahub/columns/:columnId/options​

Return all options for this column ordered by position.

POST /api/datahub/columns/:columnId/options​

Create new option.

Body:

{
  "valueKey": "in_progress",
  "label": "In progress",
  "color": "yl",
  "hasAction": true,
  "isActive": true
}

Enforce uniqueness of (board_id, column_id, value_key).

PATCH /api/datahub/options/:optionId​

Update label, colour, position, hasAction, isActive.

DELETE /api/datahub/options/:optionId​

Soft delete is fine: set is_active=false. Deleting row is optional.

3.5 Rows & cells​

GET /api/datahub/boards/:boardId/rows​

Query params:

  • table_id (optional – if omitted, include multiple tables or default only)
  • page (1-based, default 1)
  • page_size (default 50, max 200)
  • sort (e.g. status:asc,created_at:desc)
  • filters (JSON or simple key/value pairs – initial version can be minimal)

Behaviour:

  • Filter datahub.rows by board_id, is_deleted = false, optional table_id.
  • Apply pagination (offset/limit).
  • Fetch cells for returned row_ids and join with datahub.columns and datahub.dropdown_options to build cells in the DTO format.

POST /api/datahub/boards/:boardId/rows​

Create new row and cells.

Body example:

{
  "tableId": "uuid-of-table",
  "rtag": "incident_reported",
  "otag": "sc_portal_form",
  "cpol": "admin_only",
  "cells": {
    "title": { "type": "text", "value": "New incident" },
    "status": { "type": "status", "value": "pending" },
    "due_date": { "type": "date", "value": "2025-01-01T10:00:00Z" }
  }
}

Responsibilities:

  • Insert into datahub.rows with:
    • board_id, table_id, rtag, otag, default vtag (unvalidated), etc.
  • Insert into datahub.cells:
    • Resolve each columnKey to columns.id and type_id.
    • Enforce correct value_* column:
      • text → value_text
      • number → value_num
      • status/dropdown → value_key and validate dropdown_options.
      • dropdown_multi → value_json array of value_keys.
      • file → value_json array of {url, name, mime, size}.
  • Return the created RowItem.

PATCH /api/datahub/rows/:rowId​

Update row metadata only:

Body:

{
  "tableId": "optional-new-table",
  "rtag": "optional-new-rtag",
  "vtag": "optional-new-vtag",
  "otag": "optional-new-otag",
  "dtag": "optional-new-dtag",
  "isDeleted": true,
  "cpol": "optional-new-cpol"
}

Handle:

  • Moving between tables (update table_id).
  • Mark deleted / undeleted.
  • Tag changes.

PATCH /api/datahub/rows/:rowId/cells​

Partial update of some cells.

Body:

{
  "cells": {
    "status": { "type": "status", "value": "complete" },
    "notes":  { "type": "text", "value": "Done, no follow-up required." }
  }
}

Steps:

  • For each entry:
    • Resolve column by board_id + key.
    • Ensure request type matches column type_id.
    • Upsert into datahub.cells with correct value_* column.
  • Write edited_by and updated_at for each cell.
  • Return updated RowItem.

DELETE /api/datahub/rows/:rowId​

Soft delete:

  • Set is_deleted = true and dtag='soft_deleted' (unless provided).

3.6 Comments​

GET /api/datahub/rows/:rowId/comments​

Return comments sorted by created_at ASC.

POST /api/datahub/rows/:rowId/comments​

Body:

{
  "body": "Following up with @alice and @bob",
  "mentions": [
    { "userId": "uuid-alice", "medallion": "TL" },
    { "userId": "uuid-bob",   "medallion": "SC" }
  ]
}
  • Insert into datahub.comments.
  • Trigger notification pipeline (emit log or enqueue – you can just stub).

3.7 Rules (read-only for now)​

GET /api/datahub/boards/:boardId/rules​

Return RuleSummary[].

You do not need create/update/delete endpoints yet. We just need enough so the Board Designer can show “these are the current rules.”

4. Business Rules & Validation​

  1. Type enforcement

    • Reject requests where requested type does not match columns.type_id.
    • For status / dropdown:
      • Reject values not present in datahub.dropdown_options with is_active = true.

  2. Defaults

    • When creating a row:
      • Default vtag = 'unvalidated' (or similar).
      • Default is_deleted = false.
      • If tableId is not provided, use board’s is_default = true table.

  3. Tag management

    • Backend is responsible for keeping rtag, vtag, otag, dtag, is_deleted coherent with rules and transitions (even if initial implementation is minimal).

  4. Pagination & performance

    • Always paginate rows, never return unbounded result sets.
    • Use existing indexes (board_id, table_id, row_id, column_id) to avoid full table scans.

5. Hooks for Reggie & Embeddings​

You are not implementing embeddings here, but you should:

  • Expose vector_id in row DTOs.

  • Provide a simple stub endpoint:

    • POST /api/datahub/rows/:rowId/embedding
    • Body: { "vectorId": "..." }

So another service (embedding worker) can write the vector_id back.

6. Logging & Security (Minimum)​

  • Every write endpoint should:
    • Capture userId from auth context.
    • Write created_by / edited_by where possible.
  • Consider emitting events to the existing logging/notifications system when:
    • Rows are moved between tables.
    • Status cells change to a value with hasAction = true.

You don’t have to build the full notifications stack in this task – just provide clear TODO hooks or emit simple logs that other services can subscribe to.

End of task.

Part 7 – Board Function & Style Rules​

30_datahub_boards_function&style_sans_gemini_task.md

DataHub Board Engine — Verify Wiring, Finish Backend, Align UI

0. Ground Rules​

You are working on the RABS admin app.

  • Do NOT change the database schema in datahub.* or any lists.* tables.
  • Do NOT rename existing API routes unless they are provably wrong and we update both backend + frontend together.
  • Do NOT re-interpret concepts like workspaces vs buckets; follow the definitions below.
  • You must look at the actual code (JS/HTML + Express router) and not rely on older documentation that talks about mock data.

Primary reference docs in the repo:

  • 00_task_header.md
  • 10_Workflow_Tables_DataHub_Board_Engine.md
  • datahub_board_engine.md
  • workflow_tables.md
  • datahub.md
  • 10_datahub_frontend_workspace_template_task.md
  • 20_datahub_backend_task.md
  • backend/routes_v1p/datahub.js (this file is already implemented)
  • admin/src/html/datahub_*.html and admin/src/js/pages/datahub_*.js

1. Concepts You MUST Respect​

1.1 Workspaces vs Context Buckets​

Workspaces (datahub.workspaces):

  • Where boards live in the UI (admin sections).
  • Keys like: support_co, whs, comms, transport, ai_robots.
  • Used for:
    • workspace cards on datahub_workspace.html
    • header workspace switcher
    • grouping boards

Context Buckets (lists.buckets.slug):

  • Semantic domains for AI/agents/reporting.
  • Examples: admin, comms, staff_hr, participants, transport, reporting, ops, etc.
  • Used for:
    • DataHub “context bucket” field on boards
    • AI routing and reporting logic

Every board has both:

  • workspace_key → which workspace shows it.
  • context_bucket → which semantic bucket it belongs to.

1.2 Board Identification​

  • datahub.boards.id = UUID primary key.
  • datahub.boards.key = optional, human-friendly slug.
  • Not required for function.

1.3 Rows / Cells (high-level)​

  • datahub.rows = rows with tags and flags.
  • datahub.cells = typed cell values with a type_id.

2. Step 1 — Recon: Verify Actual Wiring​

2.1 Backend​

Inspect routes_v1p/datahub.js:

  • Confirm all routes mounted at /api/v1/datahub/*.
  • List endpoints.
  • Compare against 20_datahub_backend_task.md.

2.2 Frontend​

Inspect:

  • datahub_workspace.html
  • datahub_workspace.js
  • datahub_board_*.html
  • datahub_board.js

Check:

  • Whether workspaces come from DB or mocks.
  • Which path is being called for board listing.
  • What the “New Board” modal submits.
  • Whether workspaceKey/contextBucket are mixed.

3. Step 2 — Backend Corrections​

3.1 Workspaces​

GET /workspaces

  • Should return [{ id, label, description }].

GET /workspaces/:workspaceKey/boards

  • Replace legacy :bucket.
  • Filter by workspace_key = :workspaceKey.
  • Optional fallback to context_bucket.

3.2 Create Boards​

POST /boards requires:

name
workspaceKey
(optional) description
(optional) contextBucket
(optional) key

3.3 Rows / Cells / Comments​

Ensure row pagination, row creation, and comment routes match spec.

4. Step 3 — Fix Frontend Wiring​

4.1 Workspace Page​

  • Always call /api/v1/datahub/workspaces.
  • Populate cards, header switcher, modal workspace dropdown.
  • On workspace change, call /workspaces/:workspaceKey/boards.

4.2 New Board Modal​

Fields:

  • name (required)
  • description
  • workspaceKey (required)
  • contextBucket (optional)
  • key (optional)

4.3 Board Pages​

  • Load metadata from /boards/:boardId.
  • Load rows from /boards/:boardId/rows.
  • New item → POST row.
  • Comments → GET/POST row comments.

5. Step 4 — Visual Style​

Match:

  • page_logs1.html
  • page_vehicles.html

Remove experimental/glass styles.

6. Step 5 — Final Checks​

Verify:

  • Workspace list loads from DB.
  • Boards load from DB.
  • Board creation works.
  • Rows load & create correctly.
  • Comments panel works.

No hard-coded workspace lists remain unless DB is empty.

Part 8 – Implementation Checklist​

31_datahub_boards_checklist.md

DataHub Board Engine — Developer Checklist (Cupboard‑GPT Edition)

This file contains ONLY plain Markdown.
No rich text. No styling weirdness.
Safe to slide through the coding cupboard slot.

✅ 1. Recon: Verify Actual Wiring​

1.1 Backend Mount​

  • Confirm datahub.js is mounted at /api/v1/datahub.

1.2 Existing Routes in datahub.js​

Verify presence of:

  • GET /workspaces
  • GET /workspaces/:something/boards
  • POST /boards
  • GET /boards/:boardId
  • POST /boards/:boardId/tables
  • GET /boards/:boardId/rows
  • POST /boards/:boardId/rows
  • GET /rows/:rowId/comments
  • POST /rows/:rowId/comments

1.3 Compare With Backend Spec​

  • Check 20_datahub_backend_task.md for missing endpoints:
    • cell update (PATCH /rows/:rowId/cells)
    • column / dropdown management
    • board rules listing
    • search parameter handling

1.4 Frontend Workspace Logic​

Inspect:

  • admin/src/js/pages/datahub_workspace.js
  • admin/src/html/datahub_workspace.html

Check:

  • Is it still using mock workspaces?
  • Or hard-coded lists?
  • Or calling /api/v1/datahub/workspaces?
  • What URL is used to load boards?
  • What the “New Board” modal sends
  • Whether workspaceKey and contextBucket are mixed up

1.5 Board Engine Pages​

Inspect:

  • admin/src/js/pages/datahub_board.js
  • The datahub_board_* HTML files

Verify:

  • Metadata loads via /boards/:boardId
  • Rows load via /boards/:boardId/rows
  • Comments load via /rows/:rowId/comments
  • No leftover mock JSON

✅ 2. Backend Corrections (Minimal and Surgical)​

2.1 Fix Workspace Route​

  • Change param to :workspaceKey
  • Filter boards primarily by workspace_key = :workspaceKey
  • Optional fallback: OR context_bucket = :workspaceKey

2.2 Fix POST /boards​

It must accept:

name
description (optional)
workspaceKey (required)
contextBucket (optional)
key (optional)

Ensure:

  • workspaceKey required
  • contextBucket optional
  • No “infer workspace from bucket” logic
  • Auto-generate key if missing

2.3 Rows API​

  • Confirm pagination works
  • Confirm row DTO returns correct shape
  • Confirm row creation inserts both row + cells

2.4 Cell Update Endpoint​

If missing, add:

  • PATCH /rows/:rowId/cells
    • Accepts { columnId, value }
    • Writes correct value_* field
    • Returns updated cell

✅ 3. Frontend Fixes (Wire to Real API)​

3.1 Workspace Page​

  • Fetch workspaces from /api/v1/datahub/workspaces
  • Populate workspace cards
  • Populate header workspace dropdown
  • Populate modal workspace select
  • On click: load boards via /workspaces/:workspaceKey/boards

3.2 New Board Modal​

Ensure fields:

  • name
  • description
  • workspaceKey
  • contextBucket
  • key (optional)

Submit:

  • POST to /api/v1/datahub/boards
  • Refresh board list on success

3.3 Board Pages (Standard/Compact/Featured)​

  • Load board metadata
  • Load rows
  • New row → POST rows
  • Comments → GET/POST comments
  • Remove any mock data code

✅ 4. Style Alignment​

Match theme of:

  • page_logs1.html
  • page_vehicles.html

Checklist:

  • No frosted glass or white overlays
  • Typography consistent with admin pages
  • Containers + tables match existing theme
  • Buttons in correct style
  • No Gemini styling artefacts

✅ 5. Final End‑to‑End Tests​

Workspaces​

  • Load from DB
  • Switcher works
  • Workspace cards clickable

Boards​

  • Boards load from DB
  • New board creates with correct workspace_key & context_bucket
  • Board appears in UI without reload

Board Engine​

  • Metadata loads
  • Rows load
  • New row appears
  • Inline cell edit updates DB
  • Comments appear and reload correctly

Sanity​

  • No mismatched param naming
  • No leftover mocks
  • No workspace/bucket confusion

This is the full surgical checklist for Cupboard‑GPT.
No ambiguity. No creativity required.
Just follow it step‑by‑step until DataHub is aligned and operational.

Part 9 – Alternate Draft / Design History​

Workflow Tables (DataHub Board Engine)

Preface — How This Fits Into the Interface & Integration Layer​

This chapter sits inside the Interface & Integration section because it defines the
shared workflow data structures that the entire platform – UI, agents, comms tools,
email handlers, logs, automations, and voice/SMS channels – interact with.

Other chapters describe transport (voice, chat, comms), interface layers (admin UI,
email ingestion), and agent orchestration.
This chapter defines the structured data engine they all read and write to.

Everything in this document provides:

  • the canonical workflow data model for flexible boards and processes
  • the common schema used by backend services and Reggie agents
  • the integration layer between interfaces (UI, chat, voice) and operational data
  • the shared tags that enable validation, privacy, routing, and context intelligence

This engine does not replace canonical data. Canonical, regulated, permanent data lives in:

  • core_source — participants, staff, vehicles, venues
  • core_ops — finance, SCHADS, schedules, timesheets, incidents, billing

The workflow engine is a flexible processing layer that lets teams create, adjust, and operate new workflows without requiring database migrations.

1. High-Level Architecture​

[ core_source ]  -->  [ core_ops ]  -->  [ datahub (board engine) ]
                                          /          |          \
                                   [ SC views ]   [ Activities ]  [ Business ]
  • core_source holds canonical entities (participants, staff, vehicles, venues).
  • core_ops holds canonical operations (finance, SCHADS, incidents, etc.).
  • datahub is the shared workflow/board engine — boards, rows, cells, tags, metadata.

“Workspaces” (SC, Activities, Business, Transport, etc.) are expressed via board context:

  • datahub.boards.context_bucket
  • plus naming/key conventions (e.g. SC_INCIDENTS_MAIN).

Interfaces (admin UI, email handlers, SMS/voice, agents) do not hardcode incident/task schemas.
They instead talk to DataHub using the structures defined here.

2. Conceptual Model​

Board
  ├─ Tables (groups)
  ├─ Columns (fields)
  │    └─ Dropdown Options (status / dropdown values)
  ├─ Rows (items)
  │    ├─ Cells (field values)
  │    └─ Comments
  └─ Rules (automations)

Tagging layers:

  • Board-level: overall context (context_bucket – roughly “workspace”).
  • Table-level: workflow stage bucket for rows in that table (bucket).
  • Row-level: reason, validation, origin, disposition, vector embedding.
  • Column-level: comms policy (who may see this type of data).

Cells are intentionally dumb:

  • Cell = “value of this column for this row”.
  • All semantics (colours, actions, policies) are defined at board/column/row level, not per-cell.

3. DataHub Schema Overview​

All tables in this chapter live under the datahub schema.

datahub.boards          -- boards registry
  ├─ datahub.tables     -- groups on that board
  ├─ datahub.columns    -- fields on that board
  │    └─ datahub.dropdown_options -- options for status/dropdown columns
  ├─ datahub.rows       -- items on that board
  │    ├─ datahub.cells       -- one value per (row, column)
  │    └─ datahub.comments    -- row comments (optional)
  └─ datahub.rules      -- automation rules (optional/next stage)

4. Boards​

4.1 datahub.boards​

One row per workflow board.

Examples:

  • “SC Incident Workflow”
  • “Roster Exceptions”
  • “Business Ideas Backlog”
  • “Vehicle Maintenance Tasks”

Key columns:

  • id (UUID, PK) — internal identifier
  • key (TEXT, unique) — stable key, e.g. SC_INCIDENTS_MAIN
  • name (TEXT) — human-readable name
  • description (TEXT)
  • context_bucket (TEXT) — workspace / domain label (SC, Transport, Business, etc.)
  • is_active (BOOLEAN)
  • created_at, created_by, updated_at (standard audit fields)

Semantics:

  • context_bucket tells Reggie what “mental drawer” this board belongs in.
  • Boards are the join point for UI, rules, and agent routing.

5. Tables (Groups)​

5.1 datahub.tables​

One row per group inside a board.

Key columns:

  • id (UUID, PK)
  • board_id (UUID → datahub.boards.id)
  • name (TEXT) — e.g. “Intake”, “In Progress”, “Completed”
  • description (TEXT)
  • position (INT) — left-to-right / top-to-bottom order on board
  • color (TEXT) — accent colour for this group
  • bucket (TEXT, nullable) — specific workflow bucket for this table
  • is_default (BOOLEAN) — new rows land here if not otherwise specified
  • created_at, created_by, updated_at

Semantics:

  • If bucket is NULL, the board’s context_bucket effectively applies.
  • If bucket is set (e.g. SC_INCIDENT_TRIAGE vs SC_INCIDENT_CLOSED), that table can have a more precise meaning than the board as a whole.
  • Exactly one table should be is_default = TRUE per board – enforced at app level or via partial unique index.

6. Columns (Fields)​

6.1 datahub.columns​

One row per field/column.

Key columns:

  • id (UUID, PK)
  • board_id (UUID) — columns are board-scoped
  • key (TEXT) — stable key per board (e.g. status, incident_title)
  • label (TEXT) — display label
  • type_id (TEXT) — logical type (see below)
  • description (TEXT)
  • position (INT) — left-to-right order
  • width_px (INT, optional) — display width hint
  • is_required (BOOLEAN)
  • is_hidden (BOOLEAN)
  • allow_multiple_files (BOOLEAN) — only meaningful when type_id='file'
  • cpol (TEXT) — comms policy for this column (who may see this type of data)
  • created_at, created_by, updated_at

Allowed type_id values (current set):

  • text
  • number
  • bool
  • date
  • json
  • status
  • dropdown
  • dropdown_multi
  • file

Column-level comms:

  • cpol is the “default” comms policy for values in this column (e.g. admin_only, staff_only, participant_ok).
  • Row-level cpol can further tighten or override this per item.

7. Dropdown & Status Options​

7.1 datahub.dropdown_options​

One option per row per (board_id, column_id, value_key).

Fields:

  • id (UUID, PK)
  • board_id (UUID)
  • column_id (UUID → datahub.columns.id)
  • value_key (TEXT) — internal key: pending, in_progress, complete
  • label (TEXT) — display label
  • color (TEXT) — e.g. gr, rd, or #22c55e
  • position (INT) — sort order within this status/dropdown
  • has_action (BOOLEAN) — whether selecting this should trigger a rule/action
  • is_active (BOOLEAN)
  • created_at, created_by, updated_at

Semantics:

  • Status columns and dropdown columns both use this table.
  • has_action is a hint that rules exist; the actual behaviour lives in datahub.rules.

8. Rows (Items)​

8.1 datahub.rows​

Fields:

  • row_id (UUID, PK)
  • board_id (UUID)
  • table_id (UUID → datahub.tables.id)

Per-row metadata:

  • rtag — reason tag (linking to Reggie’s decisions / Reason Log)
  • vtag — validation state (unvalidated, auto, human_ok, rejected, etc.)
  • otag — origin tag (how/where did this come from – import, email, board X, etc.)
  • dtag — disposition (why this is resolved, rejected, superseded, etc.)
  • is_deleted — hard “hidden” flag for the UI / soft delete

Additional fields:

  • vector_id — pointer to embedding record (semantic vector for this row)
  • cpol — row-level comms policy override (tightens or replaces column defaults)
  • created_at, created_by, updated_at

How dtag and is_deleted work together:

  • You can have rows where dtag='resolved_ok' and is_deleted = FALSE — they remain visible but clearly marked as resolved.
  • is_deleted = TRUE = “hide this from normal views”, but we still keep the row for audit / Reggie.
  • dtag is what Reggie uses to understand “how the story ended”, even if the item remains visible for reference.

9. Cells (Field Values)​

9.1 datahub.cells​

EAV structure — one row per cell.

Fields:

  • board_id (UUID → datahub.boards.id)
  • row_id (UUID → datahub.rows.row_id)
  • column_id (UUID → datahub.columns.id)
  • type_id (TEXT) — copy of columns.type_id at write time

Typed values:

  • value_text (TEXT)
  • value_num (NUMERIC)
  • value_bool (BOOLEAN)
  • value_date (TIMESTAMPTZ)
  • value_key (TEXT) — for status/dropdown values (matches dropdown_options.value_key)
  • value_json (JSONB) — for json, dropdown_multi, file, etc.

Audit:

  • edited_by (UUID)
  • updated_at (TIMESTAMPTZ)

File support (single & multi):

For type_id='file':

  • If allow_multiple_files = FALSE – treat value_json as array with 0 or 1 objects.
  • If allow_multiple_files = TRUE – same shape, but UI allows many.

Each file object:

[
  {
    "url": "https://files.example.com/storage/abc123.pdf",
    "name": "Risk_Assessment_2025-01.pdf",
    "mime": "application/pdf",
    "size": 12345
  }
]

Validation & types:

  • Backend enforces mapping:
    • type_id='text' → value_text
    • type_id='number' → value_num
    • type_id='status' → value_key (plus dropdown_options check)
    • etc.
  • More complex validation (emails, phone numbers, addresses) is done in app logic before write, but the shape is always one of the core types above.

10. Comments​

10.1 datahub.comments​

Fields:

  • id (UUID, PK)
  • board_id (UUID)
  • row_id (UUID → datahub.rows.row_id)
  • author_id (UUID)
  • body (TEXT)
  • mentions (JSONB) — array of mentioned user IDs + medallion metadata, for notifications
  • created_at (TIMESTAMPTZ)
  • is_hidden (BOOLEAN)

Usage:

  • Per-row discussion threads.
  • Notifications are generated based on mentions and board/table context.
  • Comments obey the same cpol rules as their parent row and columns.

11. Rules (Automations)​

11.1 datahub.rules​

Fields:

  • id (UUID, PK)
  • board_id (UUID)
  • name (TEXT)
  • is_enabled (BOOLEAN)
  • trigger_type (TEXT)
  • trigger_conf (JSONB)
  • actions_conf (JSONB)
  • position (INT)
  • created_at, created_by, updated_at

Trigger types (current plan):

  • cell_changed — fires when specified column(s) change from X → Y
  • row_created — fires when a new row is created on a given board/table

Action types (current plan):

  • move_row — change table_id (and optionally bucket)
  • set_cell_value — set a value in a particular column
  • send_notification — push a notification (email, SMS, in-app)
  • emit_log — write to system logs for audit
  • call_webhook — invoke external system (future)

trigger_conf and actions_conf are JSON, so we can introduce new trigger/action types without DB migrations.

Example (status change → move row):

{
  "trigger_type": "cell_changed",
  "trigger_conf": {
    "column_key": "status",
    "from": ["in_progress", "stuck"],
    "to": ["complete"]
  },
  "actions_conf": [
    {
      "type": "move_row",
      "target_table_id": "UUID-OF-COMPLETED-TABLE"
    }
  ]
}

12. Embeddings & Intelligence​

Rows can be embedded using:

  • board name + description + context_bucket
  • table name + bucket
  • row fields (selected subset)
  • rtag, vtag, otag

Output vector is stored under rows.vector_id.

Agents use this to:

  • find similar rows
  • link related workflows
  • explain decisions and their history
  • pull in context automatically for questions like “Why was this incident closed?”

13. Behaviour & Lifecycle (Worked Example)​

  1. Intake

    • A new incident is submitted.
    • Board: SC_INCIDENTS_MAIN.
    • Table: Intake (bucket SC_INCIDENT_INTAKE).
    • Row created with:
      • otag='sc_portal_form'
      • rtag='incident_reported'
      • vtag='unvalidated'
      • cpol='admin_only'

  2. Triage

    • Staff update fields (status, notes, assigned staff).
    • A rule watches for status: "triaged".
    • On transition:
      • table_id moves to the “Triage” table.
      • rtag set to 'triage_started'.
      • vtag might move to 'auto_assessed'.

  3. Resolution

    • When status becomes complete:
      • Rule moves row to “Resolved” table (bucket SC_INCIDENT_RESOLVED).
      • dtag='resolved_no_escalation'.
      • vtag='human_ok' once a supervisor signs off.

  4. Archival / Hiding

    • If a row must be hidden from normal views:
      • is_deleted = TRUE
      • dtag='archived_policy'.

  5. Agent query

    • Agent (Reggie) receives: “Has anything like this happened with Bob before?”
    • Uses vector_id to retrieve similar rows and rtag/dtag to filter to relevant history,
      while respecting cpol and requester permissions.

14. Implementation Notes​

Backend service responsibilities:

  • Enforce type_id → value_* mapping.
  • Ensure status/dropdown values exist in datahub.dropdown_options.
  • Assign sensible defaults for vtag (imported, unverified, etc.).
  • Manage bucket, rtag, otag, dtag, is_deleted on lifecycle transitions.
  • Set cpol based on business rules + column defaults.

Frontend principles:

  • /boards/:boardId is a generic board view page (HTML + vanilla JS).
  • It loads:
    • board metadata
    • tables + columns + dropdown options
    • first page of rows
    • cells for those rows (via a generic API)
  • It never hardcodes columns for any specific board; everything is driven from metadata.

Promotion path:

  • If a workflow stabilises into a strict domain model (e.g. Support Coordination case management), you can:
    • Design strict tables in a dedicated schema (e.g. sc_core.*).
    • Migrate data from datahub into the strict schema using otag and tags.
    • Lock down or retire the board when appropriate.

15. Summary​

DataHub provides:

  • a flexible workflow engine
  • strong, layered metadata (rtag, vtag, otag, dtag, buckets)
  • clear comms policies (cpol on columns and rows)
  • a stable structure for frontend & backend
  • a semantic intelligence layer for agents via vector_id

It is the bridge between flexible UI/agents and the regulated core datasets. Developers and agents should treat this file as the canonical reference when building or querying workflows.

Part 10 – Developer Notes & Evolution​

DataHub UI & Workflow Overview

Recent Visual & UX Enhancements​

  • Workspace & Board selectors now mirror the frosted participant aesthetic: lighter card shells, transparent workspace badges, and a 4-column board grid for denser scanning.
  • Featured board view has been rebuilt around participant-style cards with uppercase titles, meta tiles, and status/type/date chips that reuse the new squared badge style.
  • Standard & compact tables enforce deterministic alignment: first column left, middle columns centered, final "Edit" column right-aligned, with labels mirroring their cells even after reorder.
  • Status & dropdown chips everywhere (tables, featured cards, designer, comments) now use squared 0.35rem rectangles with increased saturation so colors match slide-over fidelity.
  • Inline quick edit: clicking a status or dropdown badge opens an anchored menu to change the value without launching the slide-over; multi-select dropdowns show checkbox lists with apply/clear controls.
  • Comments & edit panel: once a row has >4 comments the slide-over splits into side-by-side editor and comments panes, each with independent scroll regions.
  • Profile avatars in comments now call /api/v1/profile-photo/serve/resolved with JWT token + timestamp so medallion images load correctly.
  • Context labels: headers show only the workspace name (no "Workspace:" prefix) and a "Reggie's Context" badge; in All Tables view each table also lists whether it inherits the board context or overrides it.

Conceptual Model​

Workspaces​

  • Represent the highest grouping for boards (e.g., Participants, Operations).
  • Workspace cards live on datahub_workspace.html; clicking a workspace navigates to its board selector.
  • A workspace provides defaults such as the context bucket name and theme color used in board headers.

Boards​

  • Each workspace can have multiple boards (e.g., "Participants · Active", "Finance · Forecast").
  • Boards define:
  • Tables (one or more data tables per board).
  • Columns (shared schema across tables; position/order stored in metadata).
  • Context bucket (Reggie's Context) used for color & labeling unless a table overrides it.
  • Board pages exist in three layouts: standard tables, compact tables, and featured cards, all powered by admin/src/js/pages/datahub_board.js.

Tables (within a Board)​

  • Tables hold the actual rows. One table can be marked default; All Tables view renders every table stacked.
  • Each table may inherit the board context or specify its own bucket; UI now displays this per-table in All Tables view.
  • Columns can be reordered, hidden, and assigned width presets (small/responsive/large) via the board designer modal.

Columns & Cell Types​

  • Supported types: text, number, bool, date, json, dropdown, dropdown_multi, status, file.
  • Status & dropdown types surface both in tables and inline quick-edit menus; dropdown options are defined per column in metadata.
  • Width presets ensure consistent layout across devices; derived helper functions translate presets to pixel widths.

Row Editing Workflow​

  1. Clicking any row (outside inline-edit triggers) opens the slide-over panel.
  2. Panel top half shows editable fields (auto-generated from columns). Bottom half hosts comments; split mode engages when comments > 4.
  3. Inline quick edits write directly through updateRow with { cells: { columnKey: { value/values }}} payloads and then re-fetch the affected table.
  4. "Delete Row" button currently hides the row (no unhide UI yet); label updated to reflect actual behavior.

Comments & Notifications​

  • Comments load via /api/v1/datahub/rows/:rowId/comments with bearer auth.
  • Avatars fetch signed images, falling back to initials if unavailable.
  • Comment counts propagate to row badges so table chips accurately reflect unread activity.

Key Files​

  • admin/src/js/pages/datahub_board.js: primary logic for rendering tables, featured cards, inline quick edit, slide-over, context labels.
  • admin/src/html/datahub_board_*.html: markup wrappers for the three board layouts, including the side panel scaffolding.
  • admin/src/html/datahub_workspace.html: workspace and board selector layout.
  • admin/tasks-audit_ignore/AGENTdocs/datahub_overview.md: this document.

Testing & Build​

  • Frontend bundles via npm --prefix admin run build (Vite). Static vendor scripts still trigger warnings because they are legacy non-module assets copied as-is.

DataHub Board Engine — Progress Summary

Date: 2025-11-21 (AM) Status: Board surfaces polished (glass headers, comment telemetry), metadata APIs stable, Designer ready for deeper editing

Update — 2025-11-21​

  • Board Surfaces: Standard/Compact/Featured headers now share the roster-style glass treatment. Rows swapped the chevron column for live comment bubbles (neutral glass until a row has comments, then they inherit the user’s theme colour). Compact tables slimmed down with text-only dropdown/status rendering plus tighter padding.
  • Featured Layout: Cards drop the lone blue panel, switch all copy to white, and include a “future media” placeholder tile. Comment bubbles float in the bottom-right corner of each card so stakeholders can jump straight into the discussion.
  • Comment System: GET /boards/:id/rows now returns per-row commentsCount, and the comments drawer reads/writes real datahub.comments records (with avatar medallions + @mention highlighting). Backend now auto-parses mentions before insert so notifications can key off stored metadata.
  • Column Controls: Each table header exposes a dropdown with Move Left/Right + Sort Asc/Desc/Clear; drag swaps persist via PATCH /boards/:id/columns/:columnId. Sorts apply across Standard, Compact, and Featured views (including the stacked “All Tables” mode).
  • New Item CTAs: Every table group (single view + stacked “All” view) now repeats the New Item button along the lower-left edge, pre-selecting the relevant table when launched.

1. What’s Done​

1.1 Workspace + Board Shell​

  • admin/src/html/datahub_workspace.html & ../js/pages/datahub_workspace.js
    • Workspace grid rebuilt with canonical 19 workspaces, text wrapping, and card-only navigation.
    • Header now swaps to WORKSPACE · {name} when drilling into boards, and the boards sub-heading emphasizes the active workspace.
    • Board cards keep the whole surface clickable (defaulting to Standard view) and expose real buttons for Standard / Compact / Featured that toggle to solid theme colours on hover before deep-linking into the chosen layout.
    • “New Board” modal enforces workspace + bucket selection, uses modal-cover, auto-preselects the active workspace, and surfaces API errors directly.

1.2 Board Views (Standard / Compact / Featured)​

  • Page chrome no longer overlays an extra bg-body, letting the Cyber Admin gradient show through like other modules, while toolbars/cards keep their neutral surfaces.
  • Header now shows WORKSPACE: {label} beside the back button and moves the CONTEXT: {bucket} callout to the right of the view selector for immediate orientation.
  • Board Designer modal swaps the old “X” with an explicit Done check button so users don’t worry about discarding saved metadata.
  • Empty states now explain when a board has zero tables and provide a one-click shortcut back into the Designer so teams can seed “Items” (or other) tables before adding data.
  • Table toolbar now mirrors the nav-tabs-v2 style with an “All” tab that stacks each table group vertically (Monday.com style) plus per-table tabs beside the search field, while empty tables still render their column headers and show a proper “No rows yet” row; the stacked headers inherit the participant glass effect so table names + counts stay visible on the dark gradient.
  • New Item modal now respects the active/default table, shows a hint about where the record will land, and posts directly to POST /boards/:id/rows, refreshing the current tab (or “All” view) once the backend confirms persistence.
  • admin/src/js/pages/datahub_board.js remains the shared renderer; it now:
    • Derives friendly workspace/bucket labels for the header, updates the document title, and keeps dropdown option maps/table ordering in sync.
    • Drives the Board Designer modal (tables, columns, dropdown/status options, automations) with bucket pickers and hover-aware controls.

1.3 Backend API Surface (backend/routes_v1p/datahub.js)​

  • Board creation now auto-slugs keys when absent, guarantees uniqueness, returns workspace labels, and refuses to create default tables for unknown workspaces.
  • Subsequent designer endpoints guard against phantom board IDs (columns/options/rules now 404 if the parent board doesn’t exist), which prevents stray metadata when users were still on mock boards.
  • Existing workspace/board/row endpoints remain extended with:
    • PATCH /boards/:boardId/tables/:tableId — update name, color, bucket, default flag.
    • POST | PATCH /boards/:boardId/columns — create/update metadata-driven columns (type validation, slugged keys) plus dropdown/status option CRUD.
    • POST | PATCH /boards/:boardId/rules — create or enable/disable automation rules (JSON triggers/actions) with camelCase payloads consumed by the designer.
  • GET /boards/:boardId response now includes workspace metadata (key + label) alongside camelCase rule payloads for the frontend.

1.4 Validation​

  • npm --prefix admin run build passes (standard Vite legacy warnings only); dist includes updated board bundles.

2. Current Focus / Open Threads​

  1. Board Designer Polish
    • Drag/drop column ordering, inline bucket color pickers, and richer validation are still pending.
    • Automations tab currently accepts raw JSON; needs guided builders per the AA spec.
  2. Inline Cell Editing + Row Mutations
    • Frontend still mocks inline edits; backend has POST /rows but no cell PATCH yet.

Update — 2025-11-21 (Evening)​

  • Comments UI: The row-level comment affordance now renders as a bare bubble/count pairing (no chip background) in every layout, and featured cards keep the bubble floated just outside the glass body to avoid obscuring copy.
  • Dropdown Styling: Non-status dropdowns/multi-dropdowns returned to the original pill treatment with brighter typography, while status fields remain the only colored chips. Compact rows still display the lighter inline text version to save height.
  • Designer Color Tooling: Table cards and the “Add Table” form now use native color pickers (same control as the calendar modal). Status option editing exposes a swatch palette covering the approved theme colors, prevents duplicate colors per board, and hides the picker entirely for plain dropdowns.
  • Option List Preview: The options grid re-renders color badges using the same palette logic so admins can see which tokens are already claimed.
  • Next Step: Item slug automation needs schema confirmation (rows currently lack a slug column). Once confirmed, we can auto-build workspace-board-table-title slugs during POST /boards/:id/rows while still allowing manual overrides.
  1. File Column Storage
    • Need API for rabs/storage/workspaces/<workspace_key>/... so file-type columns can upload/download.
  2. Context Buckets / Workspace Data Sync
    • Buckets endpoint ships raw DB data; caching + iconography still handled client-side.

3. Next Actions (ordered)​

  1. Wire inline cell editing + PATCH /rows/:rowId/cells endpoint (respecting CPOL + bucket inheritance).
  2. Implement Board Designer niceties (column reorder, table deletion/archive, automation templates per AA_taskdocs spec).
  3. Stand up file upload/download handlers pointing at rabs/storage/workspaces/ + signed URL support.
  4. Add lightweight polling/websocket hook so multiple operators see board changes live.
  5. Kick off the “slug upgrade” pass (boards/tables/columns) once UI polish stabilizes so URLs + lookups stay canonical.

If the session drops, reload this doc and continue with Next Action #1; Board Designer + backend scaffolding are ready for incremental enhancements.

Session Progress Summary (calendar + email settings)​

Date: current session
Focus: Admin frontend breakages after DB2 schema cutover (calendar, email settings).

What changed​

  • Added DB2-safe email settings route: backend/routes_v1p/me-email.js (uses admin_mail.*, req.db).
  • Mounted it in backend/routes_v1p/index.js and in both cutover and default branches of backend/server.js so /api/v1/me/email always goes to DB2.

Calendar issue (still open)​

  • Frontend POST: POST /api/pdb/v1/admin-calendar/events returns relation "admin.calendar_event_participant" does not exist.
  • That error can only come from the legacy DB1 router (backend/routes/admin-calendar.js, schema admin.*). The DB2 router uses admin_calendar.* and would not produce that relation name.
  • Hypothesis: running server is still hitting the legacy handler for this path despite code having routes_v1p/admin-calendar.js. Restart was done, but issue persists — need to verify which router handles the request.

Next steps (when back)​

  1. Restart backend again and retest the calendar POST. If it still errors, add a small diagnostic/log/guard to confirm which handler is invoked and block the legacy mount for /admin-calendar paths.
  2. After restart, retest Settings → Email; it should now hit the DB2 route.

Notes​

  • CUTOVER is on; DB1 fallback in cutover can silently catch missing routes. For calendar, prefer explicit DB2 mount or remove the legacy handler for this path.