Database Schema Documentation

Source: This page is based on docs/DATABASE_SCHEMA.md

Last Updated: January 8, 2026

Database Type: PostgreSQL
Total Tables: 54

This document provides a comprehensive overview of the Eddy database schema, organized by functional domain.

Overview

The Eddy database is structured around several key domains:

Core Entities - Users, groups, workflows, and workflow runs
Workflow Structure - Pages, blocks, sections, and transitions
Data Storage - Sheets, columns, rows, and cells
Execution - Workflow run stages and progression
Assignments & Roles - User roles and permissions
Collaboration - Discussions, comments, and polls
Organization - Memberships and invitations

Entity Relationship Overview

The Workflow Hierarchy

groups
  └── workflows
        ├── pages
        │     ├── sections
        │     │     └── blocks
        │     └── page_transitions
        └── workflow_runs (sessions)
              └── workflow_run_stages

The Sheet Hierarchy

groups
  └── sheets
        ├── columns
        └── rows
              └── cells

The Assignment Chain

workflows
  └── workflow_roles
        └── stage_role_assignments
              └── session_role_assignments
                    └── session_assignments

Core Entities

users

User accounts in the system.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique user identifier
auth0Id	varchar(255)	NOT NULL, INDEXED	Auth0 authentication ID
email	varchar(255)	NOT NULL	User email address
name	varchar(255)		User display name
photo	text		Profile photo URL
workflow_preferences	jsonb		User workflow preferences
is_superadmin	boolean		Whether user is a superadmin
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Indexes:

users_auth0id_index on auth0Id

Relationships:

Has many memberships
Has many workflows (as creator)
Has many workflow_runs (as participant)

groups

Workspaces or organizations that contain workflows and members.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique group identifier
name	varchar(255)		Group name
description	text		Group description
creator_id	uuid	FK → users(id)	User who created the group
public	boolean		Whether group is publicly accessible
photo	text		Group photo URL
banner	text		Group banner URL
open_ai_api_key	varchar(255)		OpenAI API key for group
email_options	jsonb		Email configuration options
brand_logo	varchar(255)		Brand logo URL
brand_options	jsonb		Brand customization options
archived_at	timestamptz		Soft delete timestamp
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

creator_id → users(id)

Relationships:

Has many workflows
Has many sheets
Has many memberships

workflows

Workflow templates that define processes.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique workflow identifier
name	varchar(255)		Workflow name
description	text		Workflow description
user_id	uuid	FK → users(id)	Creator user ID
group_id	uuid		Associated group ID
scaffold_sheet_id	uuid	FK → sheets(id)	Scaffold sheet reference
source_workflow_id	uuid	FK → workflows(id)	Source workflow if copied
published_at	timestamptz		Publication timestamp
archived_at	timestamptz		Soft delete timestamp
public	boolean		Whether workflow is public
singleton	boolean	DEFAULT false	Only one run can exist at a time
restricted_stage_visibility	boolean	DEFAULT false	Restrict stage visibility
options	jsonb	DEFAULT ''	UI and behavior options
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

user_id → users(id)
scaffold_sheet_id → sheets(id)
source_workflow_id → workflows(id) (self-reference for duplicates)

Relationships:

Belongs to group
Has many pages
Has many workflow_runs
Has many workflow_roles
Has one scaffold_sheet

workflow_runs

Live instances of workflow execution (sessions).

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique workflow run identifier
workflow_id	uuid	FK → workflows(id)	Associated workflow template
group_id	uuid		Associated group
user_id	uuid	FK → users(id)	User who started the run
status	varchar(50)		Current status (active, completed, etc.)
started_at	timestamptz		When the run started
completed_at	timestamptz		When the run completed
archived_at	timestamptz		Soft delete timestamp
metadata	jsonb		Additional run metadata
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

workflow_id → workflows(id)
user_id → users(id)

Relationships:

Belongs to workflow
Has many workflow_run_stages
Has many session_role_assignments
Has many session_assignments

Workflow Structure

pages

Individual steps or screens in a workflow.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique page identifier
workflow_id	uuid	FK → workflows(id)	Associated workflow
name	varchar(255)		Page name
description	text		Page description
type	varchar(50)		Page type (standard, start, end)
sort_order	integer		Display order
options	jsonb	DEFAULT ''	Page configuration options
archived_at	timestamptz		Soft delete timestamp
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

workflow_id → workflows(id)

Relationships:

Belongs to workflow
Has many sections
Has many page_transitions (as from_page or to_page)
Has many stage_role_assignments

sections

Containers for blocks within a page.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique section identifier
page_id	uuid	FK → pages(id)	Associated page
type	varchar(50)		Section type (content, sheet, etc.)
name	varchar(255)		Section name
sort_order	integer		Display order
options	jsonb	DEFAULT ''	Section configuration
archived_at	timestamptz		Soft delete timestamp
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

page_id → pages(id)

Relationships:

Belongs to page
Has many blocks

blocks

Atomic units of content and user input.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique block identifier
section_id	uuid	FK → sections(id)	Associated section
type	varchar(50)		Block type (string, select, content, etc.)
label	varchar(255)		Block label/question
help_text	text		Help text for users
required	boolean	DEFAULT false	Whether block is required
sheet_id	uuid	FK → sheets(id)	Target sheet for data
column_id	uuid	FK → columns(id)	Target column for data
sort_order	integer		Display order
options	jsonb	DEFAULT ''	Block-specific configuration
archived_at	timestamptz		Soft delete timestamp
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

section_id → sections(id)
sheet_id → sheets(id) (optional)
column_id → columns(id) (optional)

Relationships:

Belongs to section
Optionally belongs to column (for data-binding)

page_transitions

Define the flow between pages in a workflow.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique transition identifier
workflow_id	uuid	FK → workflows(id)	Associated workflow
from_page_id	uuid	FK → pages(id)	Source page
to_page_id	uuid	FK → pages(id)	Destination page
conditions	jsonb		Conditional logic for transition
label	varchar(255)		Transition label
sort_order	integer		Display order
archived_at	timestamptz		Soft delete timestamp
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

workflow_id → workflows(id)
from_page_id → pages(id)
to_page_id → pages(id)

Sheets & Data

sheets

Structured data tables that store workflow outputs.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique sheet identifier
group_id	uuid		Associated group
name	varchar(255)		Sheet name
description	text		Sheet description
user_id	uuid	FK → users(id)	Creator user ID
archived_at	timestamptz		Soft delete timestamp
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

user_id → users(id)

Relationships:

Has many columns
Has many rows
Has many sheet_views

columns

Define the structure of data in sheets.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique column identifier
sheet_id	uuid	FK → sheets(id)	Associated sheet
name	varchar(255)		Column name
type	varchar(50)		Column type (text, number, date, etc.)
formula	text		Formula for computed columns
sort_order	integer		Display order
options	jsonb	DEFAULT ''	Column configuration
archived_at	timestamptz		Soft delete timestamp
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

sheet_id → sheets(id)

Relationships:

Belongs to sheet
Has many cells

rows

Individual records in sheets.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique row identifier
sheet_id	uuid	FK → sheets(id)	Associated sheet
workflow_run_id	uuid	FK → workflow_runs(id)	Source workflow run (optional)
primary_row_id	uuid	FK → rows(id)	Primary row reference (for related rows)
sort_order	integer		Display order
archived_at	timestamptz		Soft delete timestamp
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

sheet_id → sheets(id)
workflow_run_id → workflow_runs(id) (optional)
primary_row_id → rows(id) (self-reference, optional)

Relationships:

Belongs to sheet
Optionally belongs to workflow_run
Has many cells

cells

Store individual data values in sheet rows.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique cell identifier
row_id	uuid	FK → rows(id)	Associated row
column_id	uuid	FK → columns(id)	Associated column
value	jsonb		Cell value (stored as JSON)
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

row_id → rows(id)
column_id → columns(id)

Unique Constraint:

(row_id, column_id) - One cell per row/column combination

Assignments & Roles

workflow_roles

Define the different participant types in a workflow.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique role identifier
workflow_id	uuid	FK → workflows(id)	Associated workflow
name	varchar(255)		Role name (e.g., "Submitter", "Approver")
description	text		Role description
color	varchar(50)		Display color
sort_order	integer		Display order
archived_at	timestamptz		Soft delete timestamp
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

workflow_id → workflows(id)

stage_role_assignments

Assign roles to specific pages/stages in a workflow.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique assignment identifier
page_id	uuid	FK → pages(id)	Associated page
workflow_role_id	uuid	FK → workflow_roles(id)	Associated role
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

page_id → pages(id)
workflow_role_id → workflow_roles(id)

session_role_assignments

Assign users to roles for a specific workflow run.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique assignment identifier
workflow_run_id	uuid	FK → workflow_runs(id)	Associated workflow run
workflow_role_id	uuid	FK → workflow_roles(id)	Associated role
user_id	uuid	FK → users(id)	Assigned user
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

workflow_run_id → workflow_runs(id)
workflow_role_id → workflow_roles(id)
user_id → users(id)

session_assignments

Track which users are assigned to which stages in a workflow run.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique assignment identifier
workflow_run_stage_id	uuid	FK → workflow_run_stages(id)	Associated stage
user_id	uuid	FK → users(id)	Assigned user
status	varchar(50)		Assignment status
completed_at	timestamptz		When assignment was completed
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

workflow_run_stage_id → workflow_run_stages(id)
user_id → users(id)

Workflow Execution

workflow_run_stages

Represent the current state of pages within a workflow run.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique stage identifier
workflow_run_id	uuid	FK → workflow_runs(id)	Associated workflow run
page_id	uuid	FK → pages(id)	Associated page template
status	varchar(50)		Stage status (pending, active, completed)
started_at	timestamptz		When stage became active
completed_at	timestamptz		When stage was completed
data	jsonb		Stage-specific data
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

workflow_run_id → workflow_runs(id)
page_id → pages(id)

Collaboration

discussions

Threaded conversations attached to various entities.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique discussion identifier
entity_type	varchar(50)		Type of entity (workflow, workflow_run, etc.)
entity_id	uuid		ID of the entity
title	varchar(255)		Discussion title
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Relationships:

Has many comments
Has many discussion_subscriptions

comments

Individual messages within discussions.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique comment identifier
discussion_id	uuid	FK → discussions(id)	Associated discussion
user_id	uuid	FK → users(id)	Comment author
content	text		Comment content
parent_comment_id	uuid	FK → comments(id)	Parent comment (for threading)
archived_at	timestamptz		Soft delete timestamp
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

discussion_id → discussions(id)
user_id → users(id)
parent_comment_id → comments(id) (self-reference)

Organization

memberships

Define user access to groups.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY	Unique membership identifier
group_id	uuid	FK → groups(id)	Associated group
user_id	uuid	FK → users(id)	Associated user
role	varchar(50)		User role in group (admin, member, etc.)
created_at	timestamptz	NOT NULL	Creation timestamp
updated_at	timestamptz	NOT NULL	Last update timestamp

Foreign Keys:

group_id → groups(id)
user_id → users(id)

Unique Constraint:

(group_id, user_id) - One membership per user per group

Common Patterns

Soft Deletes

Most entities use archived_at timestamp for soft deletion rather than hard deletes. This preserves data integrity and allows for recovery.

Timestamps

All entities include:

created_at - When the record was created
updated_at - When the record was last modified

UUIDs

All primary keys use UUID v4 for globally unique identifiers.

JSONB Columns

Many entities use JSONB columns for flexible configuration:

options - UI and behavior configuration
metadata - Additional contextual data
conditions - Conditional logic
value - Cell values in sheets

Database Migrations

Eddy uses Knex.js for database migrations. Migrations are located in /migrations and follow the naming convention:

YYYYMMDDHHMMSS_descriptive_name.js

Running Migrations

# Run all pending migrations
npx knex migrate:latest

# Rollback the last migration
npx knex migrate:rollback

# Create a new migration
npx knex migrate:make migration_name

API Endpoints

Learn how to interact with these entities via the API

Architecture

Understand how these entities work together

Database Schema

API Endpoints

Architecture

On this page