Database Schema

This section provides detailed information about the ToDoWrite Models database schema.

Schema Overview

The ToDoWrite Models implementation uses:

• 33 Total Tables: 12 model tables + 21 association tables

• Integer Primary Keys: Auto-incrementing IDs (1, 2, 3, …)

• Proper Foreign Keys: Referential integrity across all relationships

• Database Support: SQLite and PostgreSQL

Schema Files

The complete database schema is available in the following files:

SQL Schema

The SQL schema file contains complete table definitions for SQLite:

• lib_package/src/todowrite/core/schemas/todowrite_models_schema.sql

-- ToDoWrite Models Schema
-- Generated on: 2025-11-18T21:16:05.919141
-- Total tables: 33 (12 model tables + 21 association tables)
-- Database support: SQLite3 (this schema) + PostgreSQL
-- Note: All id fields are PRIMARY KEY, NOT NULL, and autoincrementing
--   - SQLite: INTEGER PRIMARY KEY AUTOINCREMENT
--   - PostgreSQL: id SERIAL PRIMARY KEY (recommended)
--     Generates: integer NOT NULL PRIMARY KEY DEFAULT nextval('table_name_id_seq'::regclass)
--     Modern alternative: id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
--     Strict alternative: id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
--
-- Foreign Key Constraints Pattern:
-- All association tables follow these conventions:
--   Table names: model1_model2 (alphabetical order, singular)
--   Column names: model_name_id (referencing model's id field)
--   Foreign keys: model_name_id INTEGER REFERENCES model_name(id)
--   Example: goals_phases (goal_id INTEGER REFERENCES goals(id), phase_id INTEGER REFERENCES phases(id))
-- This ensures referential integrity across all relationships in the ToDoWrite hierarchy.

CREATE TABLE acceptance_criteria (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title VARCHAR NOT NULL,
    description TEXT,
    status VARCHAR NOT NULL,
    progress INTEGER,
    started_date VARCHAR,
    completion_date VARCHAR,
    owner VARCHAR,
    severity VARCHAR,
    work_type VARCHAR,
    assignee VARCHAR,
    extra_data TEXT,
    created_at VARCHAR NOT NULL,
    updated_at VARCHAR NOT NULL
);

CREATE TABLE acceptance_criteria_interface_contracts (
    acceptance_criteria_id INTEGER REFERENCES acceptance_criteria(id),
    interface_contract_id INTEGER REFERENCES interface_contracts(id)
);

CREATE TABLE acceptance_criteria_labels (
    acceptance_criteria_id INTEGER REFERENCES acceptance_criteria(id),
    label_id INTEGER REFERENCES labels(id)
);

CREATE TABLE commands (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title VARCHAR NOT NULL,
    description TEXT,

JSON Schema

The JSON schema provides detailed model definitions for validation:

• lib_package/src/todowrite/core/schemas/todowrite_models.schema.json

{
  "title": "ToDoWrite Models Schema",
  "description": "JSON Schema for ToDoWrite 12-layer hierarchy",
  "version": "1.0.0",
  "models": {
    "Goal": {
      "table_name": "goals",
      "columns": {
        "id": {
          "type": "INTEGER",
          "nullable": false,
          "primary_key": true,
          "autoincrement": true
        },
        "title": {
          "type": "VARCHAR",
          "nullable": false,
          "primary_key": false,
          "autoincrement": "auto"
        },
        "description": {
          "type": "TEXT",
          "nullable": true,
          "primary_key": false,
          "autoincrement": "auto"
        },
        "status": {
          "type": "VARCHAR",
          "nullable": false,
          "primary_key": false,

Model Tables

Each of the 12 ToDoWrite Models has its own table:

1. goals - Project objectives

2. concepts - Abstract ideas

3. contexts - Background information

4. constraints - Technical/business constraints

5. requirements - Functional requirements

6. acceptance_criteria - Definition of done criteria

7. interface_contracts - API contracts

8. phases - Project phases

9. steps - Individual steps

10. tasks - Specific tasks

11. sub_tasks - Task breakdowns

12. commands - Executable commands

13. labels - Tagging system

Association Tables

Many-to-many relationships are handled through join tables following the pattern:

-- Example: goals and tasks relationship
CREATE TABLE goals_tasks (
    goal_id INTEGER NOT NULL,
    task_id INTEGER NOT NULL,
    FOREIGN KEY (goal_id) REFERENCES goals(id),
    FOREIGN KEY (task_id) REFERENCES tasks(id),
    PRIMARY KEY (goal_id, task_id)
);

Common association tables include:

• goals_tasks - Goals ↔ Tasks

• tasks_labels - Tasks ↔ Labels

• commands_labels - Commands ↔ Labels

• phases_steps - Phases ↔ Steps

• steps_tasks - Steps ↔ Tasks

• tasks_sub_tasks - Tasks ↔ SubTasks

And many more to support the complete hierarchy.

Foreign Key Constraints

All association tables maintain referential integrity:

• Parent-Child: Direct hierarchy relationships

• Many-to-Many: Cross-layer associations

• Cascading: Proper cascade rules for data consistency

Schema Validation

The schema is validated using:

1. JSON Schema: Data structure validation

2. SQLAlchemy ORM: Model definition validation

3. Database Constraints: Referential integrity checks

For programmatic schema validation, see ToDoWrite Models API Reference.