---
title: Scrolls schema
scope: 3dmk
type: schema
subject: scrolls
status: current
updated: "2026-10-31"
source: https://3dmk.com/documentation#scrolls-schema
author: Nick Olson
contact:
  email: 3dmk@3dmk.com
  site: https://3dmk.com
license: CC BY 4.0
license_url: https://creativecommons.org/licenses/by/4.0/
attribution: Nick Olson, 3dmk.com
tags:
  - scrolls
  - postgresql
  - work-tracking
---

# Scrolls schema

Scrolls keeps its work items in one PostgreSQL database of 15 tables. This document gives that schema as a new install receives it, with every table empty.

## What the schema holds

Every table has a serial integer key for joins and a universally unique identifier (UUID) for references from outside the database. Each theme, initiative, epic, and work item has a kebab-case name, held to that form by a check constraint.

The tables fall into five groups.

| Group | Tables | Holds |
|---|---|---|
| Lookups | `scroll_status`, `scroll_priority`, `scroll_size`, `scroll_action`, `scroll_template` | The allowed values a theme, an initiative, or a work item names |
| Templates | `scroll_template_epic` | The standard epics an initiative receives under its template |
| Agents | `scroll_agent` | The agents that take work, each with its model and context limit |
| Hierarchy | `scroll_theme`, `scroll_initiative`, `scroll_epic` | The containers, from a product domain down to a cohesive chunk of work |
| Work items | `scroll_work_ref`, `scroll_story`, `scroll_task`, `scroll_bug`, `scroll_subtask` | The units of work and the reference they share |

Stories, tasks, bugs, and subtasks each own one row in `scroll_work_ref`. A subtask names its parent through that row, so one column can point at a story, a task, or a bug.

Two triggers run on the tables that change. One stamps `updated_on` on every update. The other rejects a status whose `applies_to` list leaves out the row’s kind of entity.

The lookup tables start empty. Every theme and work item names a status, so the developer adds the lookup values before the first theme. An initiative names its template on insert, because an empty database holds no default template.

The SQL runs in the order given here, on an empty PostgreSQL 13 database or later.

## Functions

The triggers call two shared functions, so this block runs first.

### functions

```sql
CREATE FUNCTION scroll_set_updated_on()
RETURNS trigger AS $$
BEGIN
    NEW.updated_on = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION scroll_status_applies_to_check()
RETURNS trigger AS $$
DECLARE
    kind  text := TG_ARGV[0];
    nm    text;
    appl  text[];
BEGIN
    SELECT s.name, s.applies_to
      INTO nm, appl
      FROM scroll_status s
     WHERE s.id = NEW.status_id;

    IF nm IS NULL THEN
        RAISE EXCEPTION
            'unknown status_id % for %', NEW.status_id, kind
            USING ERRCODE = 'foreign_key_violation';
    END IF;

    IF NOT (kind = ANY(appl)) THEN
        RAISE EXCEPTION
            'status % is not valid for %; allowed: %',
            nm, kind, (
                SELECT array_agg(s.name ORDER BY s.id)
                  FROM scroll_status s
                 WHERE kind = ANY(s.applies_to)
            )
            USING ERRCODE = 'check_violation';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
```

## Lookups and agents

These tables hold the values the rest of the schema names.

### scroll_status

```sql
CREATE TABLE scroll_status (
    id          serial       PRIMARY KEY,
    key         uuid         NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    name        text         NOT NULL UNIQUE,
    applies_to  text[]       NOT NULL,
    created_on  timestamptz  NOT NULL DEFAULT now()
);
```

### scroll_priority

```sql
CREATE TABLE scroll_priority (
    id          serial       PRIMARY KEY,
    key         uuid         NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    name        text         NOT NULL UNIQUE,
    sort_order  int          NOT NULL,
    created_on  timestamptz  NOT NULL DEFAULT now()
);
```

### scroll_size

```sql
CREATE TABLE scroll_size (
    id             serial       PRIMARY KEY,
    key            uuid         NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    name           text         NOT NULL UNIQUE,
    agent_default  text         NOT NULL,
    created_on     timestamptz  NOT NULL DEFAULT now()
);
```

### scroll_action

```sql
CREATE TABLE scroll_action (
    id          serial       PRIMARY KEY,
    key         uuid         NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    name        text         NOT NULL UNIQUE,
    created_on  timestamptz  NOT NULL DEFAULT now()
);
```

### scroll_template

```sql
CREATE TABLE scroll_template (
    id          serial       PRIMARY KEY,
    key         uuid         NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    name        text         NOT NULL UNIQUE,
    created_on  timestamptz  NOT NULL DEFAULT now()
);
```

### scroll_template_epic

```sql
CREATE TABLE scroll_template_epic (
    id           serial       PRIMARY KEY,
    key          uuid         NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    template_id  int          NOT NULL REFERENCES scroll_template(id),
    name         text         NOT NULL,
    title        text         NOT NULL,
    sort_order   int          NOT NULL,
    role         text         NOT NULL DEFAULT 'domain'
                              CHECK (role IN ('sentinel', 'domain')),
    created_on   timestamptz  NOT NULL DEFAULT now(),
    UNIQUE (template_id, name),
    CHECK (name ~ '^[a-z][a-z0-9]*(-[a-z0-9]+)*$')
);
```

### scroll_agent

```sql
CREATE TABLE scroll_agent (
    id           serial       PRIMARY KEY,
    key          uuid         NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    name         text         NOT NULL UNIQUE,
    model        text         NOT NULL,
    status       text         NOT NULL DEFAULT 'active'
                              CHECK (status IN ('active', 'inactive')),
    max_context  int,
    created_on   timestamptz  NOT NULL DEFAULT now(),
    updated_on   timestamptz  NOT NULL DEFAULT now()
);

CREATE TRIGGER trg_updated_on
    BEFORE UPDATE ON scroll_agent
    FOR EACH ROW
    EXECUTE FUNCTION scroll_set_updated_on();
```

## Hierarchy

Each level holds the one beneath it, from theme to epic.

### scroll_theme

```sql
CREATE TABLE scroll_theme (
    id            serial       PRIMARY KEY,
    key           uuid         NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    name          text         NOT NULL UNIQUE
                               CHECK (name ~ '^[a-z][a-z0-9]*(-[a-z0-9]+)*$'),
    display_name  text         NOT NULL,
    description   text,
    status_id     int          NOT NULL REFERENCES scroll_status(id),
    labels        text[]       DEFAULT '{}',
    created_on    timestamptz  NOT NULL DEFAULT now(),
    updated_on    timestamptz  NOT NULL DEFAULT now()
);

CREATE TRIGGER trg_updated_on
    BEFORE UPDATE ON scroll_theme
    FOR EACH ROW
    EXECUTE FUNCTION scroll_set_updated_on();
CREATE TRIGGER trg_status_check
    BEFORE INSERT OR UPDATE OF status_id ON scroll_theme
    FOR EACH ROW
    EXECUTE FUNCTION scroll_status_applies_to_check('theme');
```

### scroll_initiative

```sql
CREATE TABLE scroll_initiative (
    id            serial       PRIMARY KEY,
    key           uuid         NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    theme_id      int          NOT NULL REFERENCES scroll_theme(id) ON DELETE RESTRICT,
    name          text         NOT NULL
                               CHECK (name ~ '^[a-z][a-z0-9]*(-[a-z0-9]+)*$'),
    title         text         NOT NULL,
    epic_ident    text         NOT NULL
                               CHECK (epic_ident ~ '^[A-Z][A-Z0-9]{1,7}$'),
    description   text,
    status_id     int          NOT NULL REFERENCES scroll_status(id),
    labels        text[]       DEFAULT '{}',
    created_on    timestamptz  NOT NULL DEFAULT now(),
    updated_on    timestamptz  NOT NULL DEFAULT now(),
    template_id   int          NOT NULL REFERENCES scroll_template(id),
    UNIQUE (theme_id, name),
    UNIQUE (theme_id, epic_ident)
);

CREATE INDEX idx_scroll_initiative_theme_id
    ON scroll_initiative (theme_id);
CREATE INDEX idx_scroll_initiative_status_id
    ON scroll_initiative (status_id);
CREATE INDEX idx_scroll_initiative_template_id
    ON scroll_initiative (template_id);

CREATE TRIGGER trg_updated_on
    BEFORE UPDATE ON scroll_initiative
    FOR EACH ROW
    EXECUTE FUNCTION scroll_set_updated_on();
CREATE TRIGGER trg_status_check
    BEFORE INSERT OR UPDATE OF status_id ON scroll_initiative
    FOR EACH ROW
    EXECUTE FUNCTION scroll_status_applies_to_check('initiative');
```

### scroll_epic

```sql
CREATE TABLE scroll_epic (
    id             serial            PRIMARY KEY,
    key            uuid              NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    initiative_id  int               NOT NULL REFERENCES scroll_initiative(id) ON DELETE RESTRICT,
    identifier     text              NOT NULL UNIQUE
                                     CHECK (identifier ~ '^[A-Z][A-Z0-9]{1,7}-EPIC-\d{3}$'),
    name           text              NOT NULL
                                     CHECK (name ~ '^[a-z][a-z0-9]*(-[a-z0-9]+)*$'),
    title          text              NOT NULL,
    description    text,
    status_id      int               NOT NULL REFERENCES scroll_status(id),
    sort_order     int               NOT NULL DEFAULT 0,
    labels         text[]            DEFAULT '{}',
    created_on     timestamptz       NOT NULL DEFAULT now(),
    updated_on     timestamptz       NOT NULL DEFAULT now(),
    phase          double precision,
    rank           text,
    UNIQUE (initiative_id, name)
);

CREATE INDEX idx_scroll_epic_initiative_id
    ON scroll_epic (initiative_id);
CREATE INDEX idx_scroll_epic_status_id
    ON scroll_epic (status_id);

CREATE TRIGGER trg_updated_on
    BEFORE UPDATE ON scroll_epic
    FOR EACH ROW
    EXECUTE FUNCTION scroll_set_updated_on();
CREATE TRIGGER trg_status_check
    BEFORE INSERT OR UPDATE OF status_id ON scroll_epic
    FOR EACH ROW
    EXECUTE FUNCTION scroll_status_applies_to_check('epic');
```

## Work items

Each work item table carries its own indexes and triggers after its definition.

### scroll_work_ref

```sql
CREATE TABLE scroll_work_ref (
    id          serial       PRIMARY KEY,
    key         uuid         NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    kind        text         NOT NULL
                             CHECK (kind IN ('story', 'task', 'bug', 'subtask')),
    created_on  timestamptz  NOT NULL DEFAULT now()
);
```

### scroll_story

```sql
CREATE TABLE scroll_story (
    id                    serial            PRIMARY KEY,
    key                   uuid              NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    work_ref_id           int               NOT NULL UNIQUE REFERENCES scroll_work_ref(id) ON DELETE CASCADE,
    initiative_id         int               NOT NULL REFERENCES scroll_initiative(id) ON DELETE RESTRICT,
    epic_id               int               REFERENCES scroll_epic(id) ON DELETE SET NULL,
    identifier            text              NOT NULL UNIQUE
                                            CHECK (identifier ~ '^[A-Z][A-Z0-9]{1,7}-STORY-\d{3}-\d{6}$'),
    name                  text              NOT NULL
                                            CHECK (name ~ '^[a-z][a-z0-9]*(-[a-z0-9]+)*$'),
    title                 text              NOT NULL,
    description           text              NOT NULL,
    agent_spec            text,
    status_id             int               NOT NULL REFERENCES scroll_status(id),
    priority_id           int               NOT NULL REFERENCES scroll_priority(id),
    size_id               int               NOT NULL REFERENCES scroll_size(id),
    agent_identifier      int               REFERENCES scroll_agent(id),
    labels                text[]            DEFAULT '{}',
    doc_references        jsonb,
    standards_references  text[]            DEFAULT '{}',
    action_id             int               REFERENCES scroll_action(id),
    action_ref_id         int,
    action_extra          text,
    auto_start            boolean           NOT NULL DEFAULT false,
    depends_on            int[]             DEFAULT '{}',
    due_date              date,
    completed_on          timestamptz,
    created_on            timestamptz       NOT NULL DEFAULT now(),
    updated_on            timestamptz       NOT NULL DEFAULT now(),
    phase                 double precision
);

CREATE INDEX idx_scroll_story_initiative_id
    ON scroll_story (initiative_id);
CREATE INDEX idx_scroll_story_epic_id
    ON scroll_story (epic_id);
CREATE INDEX idx_scroll_story_status_id
    ON scroll_story (status_id);
CREATE INDEX idx_scroll_story_priority_id
    ON scroll_story (priority_id);
CREATE INDEX idx_scroll_story_agent_identifier
    ON scroll_story (agent_identifier);
CREATE INDEX idx_scroll_story_status_priority
    ON scroll_story (status_id, priority_id);

CREATE TRIGGER trg_updated_on
    BEFORE UPDATE ON scroll_story
    FOR EACH ROW
    EXECUTE FUNCTION scroll_set_updated_on();
CREATE TRIGGER trg_status_check
    BEFORE INSERT OR UPDATE OF status_id ON scroll_story
    FOR EACH ROW
    EXECUTE FUNCTION scroll_status_applies_to_check('story');
```

### scroll_task

```sql
CREATE TABLE scroll_task (
    id                serial            PRIMARY KEY,
    key               uuid              NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    work_ref_id       int               NOT NULL UNIQUE REFERENCES scroll_work_ref(id) ON DELETE CASCADE,
    initiative_id     int               NOT NULL REFERENCES scroll_initiative(id) ON DELETE RESTRICT,
    epic_id           int               REFERENCES scroll_epic(id) ON DELETE SET NULL,
    identifier        text              NOT NULL UNIQUE
                                        CHECK (identifier ~ '^[A-Z][A-Z0-9]{1,7}-TASK-\d{3}-\d{6}$'),
    name              text              NOT NULL
                                        CHECK (name ~ '^[a-z][a-z0-9]*(-[a-z0-9]+)*$'),
    title             text              NOT NULL,
    description       text              NOT NULL,
    agent_spec        text,
    status_id         int               NOT NULL REFERENCES scroll_status(id),
    priority_id       int               NOT NULL REFERENCES scroll_priority(id),
    size_id           int               NOT NULL REFERENCES scroll_size(id),
    agent_identifier  int               REFERENCES scroll_agent(id),
    labels            text[]            DEFAULT '{}',
    repo              text,
    action_id         int               REFERENCES scroll_action(id),
    action_ref_id     int,
    action_extra      text,
    auto_start        boolean           NOT NULL DEFAULT false,
    depends_on        int[]             DEFAULT '{}',
    due_date          date,
    completed_on      timestamptz,
    created_on        timestamptz       NOT NULL DEFAULT now(),
    updated_on        timestamptz       NOT NULL DEFAULT now(),
    phase             double precision
);

CREATE INDEX idx_scroll_task_initiative_id
    ON scroll_task (initiative_id);
CREATE INDEX idx_scroll_task_epic_id
    ON scroll_task (epic_id);
CREATE INDEX idx_scroll_task_status_id
    ON scroll_task (status_id);
CREATE INDEX idx_scroll_task_priority_id
    ON scroll_task (priority_id);
CREATE INDEX idx_scroll_task_agent_identifier
    ON scroll_task (agent_identifier);
CREATE INDEX idx_scroll_task_repo
    ON scroll_task (repo);
CREATE INDEX idx_scroll_task_status_priority
    ON scroll_task (status_id, priority_id);

CREATE TRIGGER trg_updated_on
    BEFORE UPDATE ON scroll_task
    FOR EACH ROW
    EXECUTE FUNCTION scroll_set_updated_on();
CREATE TRIGGER trg_status_check
    BEFORE INSERT OR UPDATE OF status_id ON scroll_task
    FOR EACH ROW
    EXECUTE FUNCTION scroll_status_applies_to_check('task');
```

### scroll_bug

```sql
CREATE TABLE scroll_bug (
    id                serial            PRIMARY KEY,
    key               uuid              NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    work_ref_id       int               NOT NULL UNIQUE REFERENCES scroll_work_ref(id) ON DELETE CASCADE,
    initiative_id     int               NOT NULL REFERENCES scroll_initiative(id) ON DELETE RESTRICT,
    epic_id           int               REFERENCES scroll_epic(id) ON DELETE SET NULL,
    identifier        text              NOT NULL UNIQUE
                                        CHECK (identifier ~ '^[A-Z][A-Z0-9]{1,7}-BUG-\d{3}-\d{6}$'),
    name              text              NOT NULL
                                        CHECK (name ~ '^[a-z][a-z0-9]*(-[a-z0-9]+)*$'),
    title             text              NOT NULL,
    description       text              NOT NULL,
    agent_spec        text,
    status_id         int               NOT NULL REFERENCES scroll_status(id),
    priority_id       int               NOT NULL REFERENCES scroll_priority(id),
    agent_identifier  int               REFERENCES scroll_agent(id),
    labels            text[]            DEFAULT '{}',
    repo              text,
    action_id         int               REFERENCES scroll_action(id),
    action_ref_id     int,
    action_extra      text,
    auto_start        boolean           NOT NULL DEFAULT false,
    depends_on        int[]             DEFAULT '{}',
    completed_on      timestamptz,
    created_on        timestamptz       NOT NULL DEFAULT now(),
    updated_on        timestamptz       NOT NULL DEFAULT now(),
    phase             double precision
);

CREATE INDEX idx_scroll_bug_initiative_id
    ON scroll_bug (initiative_id);
CREATE INDEX idx_scroll_bug_epic_id
    ON scroll_bug (epic_id);
CREATE INDEX idx_scroll_bug_status_id
    ON scroll_bug (status_id);
CREATE INDEX idx_scroll_bug_priority_id
    ON scroll_bug (priority_id);
CREATE INDEX idx_scroll_bug_agent_identifier
    ON scroll_bug (agent_identifier);
CREATE INDEX idx_scroll_bug_repo
    ON scroll_bug (repo);

CREATE TRIGGER trg_updated_on
    BEFORE UPDATE ON scroll_bug
    FOR EACH ROW
    EXECUTE FUNCTION scroll_set_updated_on();
CREATE TRIGGER trg_status_check
    BEFORE INSERT OR UPDATE OF status_id ON scroll_bug
    FOR EACH ROW
    EXECUTE FUNCTION scroll_status_applies_to_check('bug');
```

### scroll_subtask

```sql
CREATE TABLE scroll_subtask (
    id                  serial       PRIMARY KEY,
    key                 uuid         NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    work_ref_id         int          NOT NULL UNIQUE REFERENCES scroll_work_ref(id) ON DELETE CASCADE,
    parent_work_ref_id  int          NOT NULL REFERENCES scroll_work_ref(id) ON DELETE CASCADE,
    initiative_id       int          NOT NULL REFERENCES scroll_initiative(id) ON DELETE RESTRICT,
    identifier          text         NOT NULL UNIQUE
                                     CHECK (identifier ~ '^[A-Z][A-Z0-9]{1,7}-SUB-\d{3}-\d{6}$'),
    name                text         NOT NULL
                                     CHECK (name ~ '^[a-z][a-z0-9]*(-[a-z0-9]+)*$'),
    title               text         NOT NULL,
    description         text,
    agent_spec          text,
    status_id           int          NOT NULL REFERENCES scroll_status(id),
    priority_id         int          REFERENCES scroll_priority(id),
    size_id             int          REFERENCES scroll_size(id),
    agent_identifier    int          REFERENCES scroll_agent(id),
    labels              text[]       DEFAULT '{}',
    repo                text,
    action_id           int          REFERENCES scroll_action(id),
    action_ref_id       int,
    action_extra        text,
    auto_start          boolean      NOT NULL DEFAULT false,
    depends_on          int[]        DEFAULT '{}',
    sort_order          int          NOT NULL DEFAULT 0,
    completed_on        timestamptz,
    created_on          timestamptz  NOT NULL DEFAULT now(),
    updated_on          timestamptz  NOT NULL DEFAULT now()
);

CREATE INDEX idx_scroll_subtask_parent_work_ref_id
    ON scroll_subtask (parent_work_ref_id);
CREATE INDEX idx_scroll_subtask_initiative_id
    ON scroll_subtask (initiative_id);
CREATE INDEX idx_scroll_subtask_status_id
    ON scroll_subtask (status_id);
CREATE INDEX idx_scroll_subtask_agent_identifier
    ON scroll_subtask (agent_identifier);

CREATE TRIGGER trg_updated_on
    BEFORE UPDATE ON scroll_subtask
    FOR EACH ROW
    EXECUTE FUNCTION scroll_set_updated_on();
CREATE TRIGGER trg_status_check
    BEFORE INSERT OR UPDATE OF status_id ON scroll_subtask
    FOR EACH ROW
    EXECUTE FUNCTION scroll_status_applies_to_check('subtask');
```