Schema

Schema

The database schema evolves incrementally as each epic is implemented. This page documents entity descriptions, relationships, indexes, and rationale for all tables.

Conventions

• All table and column names use snake_case
• Primary keys use TEXT type with application-generated UUIDs (crypto.randomUUID()), named id
• Foreign keys follow the pattern <referenced_table_singular>_id (e.g., user_id, budget_category_id)
• All entities include created_at and updated_at timestamps (ISO 8601, stored as TEXT in SQLite)
• Soft deletes are used selectively: the users table uses deactivated_at for audit trail and referential integrity; most other entities use hard deletes
• Boolean values are stored as INTEGER (0/1) per SQLite convention
• Foreign keys enforce ON DELETE CASCADE where appropriate
• Junction tables use composite primary keys (no surrogate id column)

---

EPIC-01: Authentication & User Management

users

Stores all user accounts (both local and OIDC-authenticated).

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
email	TEXT	UNIQUE NOT NULL	User's email address (unique across all auth providers)
display_name	TEXT	NOT NULL	User's display name
role	TEXT	NOT NULL DEFAULT 'member' CHECK(role IN ('admin','member'))	Authorization role
auth_provider	TEXT	NOT NULL CHECK(auth_provider IN ('local','oidc'))	Authentication method
password_hash	TEXT	nullable	Argon2id hash; only for local users
oidc_subject	TEXT	nullable	OIDC provider's subject claim; only for OIDC users
deactivated_at	TEXT	nullable	ISO 8601 timestamp; NULL = active, set = soft-deleted
created_at	TEXT	NOT NULL	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL	ISO 8601 timestamp, updated on every modification

Indexes:

Index	Columns	Type	Rationale
sqlite_autoindex_users_1	email	UNIQUE	Enforced by UNIQUE constraint; fast email lookups for login
idx_users_oidc_lookup	auth_provider, oidc_subject	UNIQUE (partial: WHERE oidc_subject IS NOT NULL)	Fast OIDC user lookup; prevents duplicate OIDC subjects per provider

Design rationale:

• TEXT primary key (UUID): Avoids auto-increment integer IDs that could leak information about user count or creation order. UUIDs are generated at the application layer using Node.js crypto.randomUUID() for portability.
• deactivated_at instead of is_active: Provides both the active/inactive state and the timestamp of deactivation for audit purposes. Active users are queried with WHERE deactivated_at IS NULL.
• auth_provider + oidc_subject composite unique index: Allows efficient OIDC user lookup during login. The partial index (WHERE oidc_subject IS NOT NULL) avoids indexing local users who have NULL oidc_subject.
• email is globally unique: A single email can only belong to one user, regardless of auth provider. This prevents confusion and simplifies the user model. If an OIDC user has the same email as a local user, the OIDC callback must handle this conflict explicitly (Story 1.5).
• Nullable password_hash / oidc_subject: These are mutually exclusive by auth_provider. Local users have password_hash but no oidc_subject; OIDC users have oidc_subject but no password_hash. Application-layer validation enforces this invariant.

sessions

Stores server-side session data for authenticated users.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	256-bit cryptographically random hex string (crypto.randomBytes(32).toString('hex'))
user_id	TEXT	NOT NULL REFERENCES users(id) ON DELETE CASCADE	Owning user
expires_at	TEXT	NOT NULL	ISO 8601 timestamp; session invalid after this time
created_at	TEXT	NOT NULL	ISO 8601 timestamp, set on insert

Indexes:

Index	Columns	Type	Rationale
idx_sessions_user_id	user_id	INDEX	Fast lookup of all sessions for a user (used during user deactivation to invalidate all sessions)
idx_sessions_expires_at	expires_at	INDEX	Efficient cleanup of expired sessions

Design rationale:

• TEXT primary key (random hex): 256-bit random tokens provide 2^256 possible values, making brute-force infeasible. Hex encoding produces a 64-character string that fits naturally in TEXT.
• No updated_at: Sessions are immutable after creation; they are either valid or deleted. There is no session renewal/extension.
• ON DELETE CASCADE: When a user is deleted (or if hard-delete is ever used), all their sessions are automatically cleaned up.
• expires_at index: Supports periodic cleanup queries (DELETE FROM sessions WHERE expires_at < datetime('now')).

Migration File

server/src/db/migrations/0001_create_users_and_sessions.sql

-- EPIC-01: Authentication & User Management
-- Creates the users and sessions tables for authentication.

CREATE TABLE users (
  id TEXT PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  display_name TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'member' CHECK(role IN ('admin', 'member')),
  auth_provider TEXT NOT NULL CHECK(auth_provider IN ('local', 'oidc')),
  password_hash TEXT,
  oidc_subject TEXT,
  deactivated_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_users_oidc_lookup
  ON users (auth_provider, oidc_subject)
  WHERE oidc_subject IS NOT NULL;

CREATE TABLE sessions (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  expires_at TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE INDEX idx_sessions_user_id ON sessions (user_id);
CREATE INDEX idx_sessions_expires_at ON sessions (expires_at);

-- Rollback:
-- DROP INDEX IF EXISTS idx_sessions_expires_at;
-- DROP INDEX IF EXISTS idx_sessions_user_id;
-- DROP TABLE IF EXISTS sessions;
-- DROP INDEX IF EXISTS idx_users_oidc_lookup;
-- DROP TABLE IF EXISTS users;

---

EPIC-03: Work Items Core CRUD & Properties

work_items

The central entity of the application. Represents a construction task or activity in the home building project. Budget tracking is handled via the work_item_budgets child table (EPIC-05 Story 5.9). Document link fields (paperless_ngx references) will be added by EPIC-08.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
title	TEXT	NOT NULL	Short title of the work item
description	TEXT	nullable	Detailed description (supports free-form text)
status	TEXT	NOT NULL DEFAULT 'not_started' CHECK(status IN ('not_started','in_progress','completed'))	Current work item status
start_date	TEXT	nullable	Planned start date (ISO 8601 date, e.g., 2026-03-15)
end_date	TEXT	nullable	Planned end date (ISO 8601 date, e.g., 2026-03-20)
actual_start_date	TEXT	nullable	Actual start date (ISO 8601 date); auto-set on status transition to in_progress
actual_end_date	TEXT	nullable	Actual end date (ISO 8601 date); auto-set on status transition to completed
duration_days	INTEGER	nullable	Duration in working days; can be set manually or computed from start_date/end_date
start_after	TEXT	nullable	Earliest possible start constraint (ISO 8601 date); used by the scheduling engine
start_before	TEXT	nullable	Latest possible start constraint (ISO 8601 date); used by the scheduling engine
assigned_user_id	TEXT	nullable, REFERENCES users(id) ON DELETE SET NULL	User responsible for this work item
created_by	TEXT	NOT NULL, REFERENCES users(id) ON DELETE SET NULL	User who created this work item
created_at	TEXT	NOT NULL	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL	ISO 8601 timestamp, updated on every modification

Status values:

Value	Description
not_started	Work has not begun (default)
in_progress	Work is actively underway
completed	Work is finished

Actual dates auto-population:

When the status field is updated, actual dates are automatically set to the current date (YYYY-MM-DD) unless an explicit value is provided in the same request:

Transition	Auto-set field(s)
not_started -> in_progress	actual_start_date = today
in_progress -> completed	actual_end_date = today
not_started -> completed	actual_start_date = today, actual_end_date = today

If the request body explicitly includes actualStartDate or actualEndDate (even as null), the explicit value is used and auto-population is skipped for that field.

Indexes:

Index	Columns	Type	Rationale
idx_work_items_status	status	INDEX	Filter work items by status (most common filter)
idx_work_items_assigned_user_id	assigned_user_id	INDEX	Filter work items by assignee
idx_work_items_created_at	created_at	INDEX	Default sort order for list endpoint (newest first)

Design rationale:

• status CHECK constraint: The three status values (not_started, in_progress, completed) cover all practical states for a construction task. The blocked status was removed in migration 0008 (PR #308, Issue #296) because blocking is better represented through dependency relationships and tags rather than a status enum. "Cancelled" was omitted because deleting the work item achieves the same result for this small-scale application.
• actual_start_date/actual_end_date: These columns track when work actually began and finished, as opposed to the planned start_date/end_date. They are auto-populated on status transitions (see table above) but can be explicitly set or cleared by the user. The scheduling engine uses actual_start_date as a fixed start date (overriding CPM forward pass), and actual_end_date alongside actual_start_date as a fixed finish date.
• duration_days alongside start_date/end_date: Requirements specify duration can be "calculated or manual." Storing it allows users to set a duration before a start date is known. When both start_date and end_date are set, the frontend can compute duration for display; the stored value represents a manual override. The scheduling engine (EPIC-06) will use this field to auto-calculate end_date from start_date + duration when cascading updates.
• start_after/start_before constraints: These are scheduling constraints separate from the actual start_date. They represent external constraints like vendor availability windows or weather requirements. The scheduling engine uses these to constrain automatic rescheduling.
• assigned_user_id ON DELETE SET NULL: If a user is deactivated or deleted, the work item remains with an unassigned state rather than being deleted. This preserves project data.
• created_by ON DELETE SET NULL: Preserves the work item even if the creating user is removed. The SET NULL FK means the creator is unknown but the work item survives.
• No priority column: Priority was initially considered but not present in the requirements (Section 2.1 lists status but not priority). Instead, the requirements specify "Tags" for custom organization, which covers priority labeling (users can create "High Priority", "Critical" tags). This avoids an opinionated enum that may not match every homeowner's workflow.
• No budget columns on work_items: Budget tracking was originally added as flat columns on work_items (migration 0004) but was reworked in Story 5.9 to use a separate work_item_budgets child table. This allows multiple budget lines per work item (e.g., separate labor and materials estimates from different vendors), each with its own confidence level and budget source. Migration 0005 removes the flat budget columns and replaces them with the budget lines model.

tags

User-defined labels for organizing work items. Tags are a shared resource available to all work items.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
name	TEXT	UNIQUE NOT NULL	Tag display name (case-insensitive uniqueness enforced at application layer)
color	TEXT	nullable	Hex color code (e.g., #FF5733) for visual display
created_at	TEXT	NOT NULL	ISO 8601 timestamp, set on insert

Indexes:

Index	Columns	Type	Rationale
sqlite_autoindex_tags_1	name	UNIQUE	Enforced by UNIQUE constraint; prevents duplicate tag names

Design rationale:

• No updated_at: Tags are simple name+color entities. Updates are infrequent and the creation timestamp is sufficient for ordering. If update tracking becomes needed, this column can be added.
• name UNIQUE: SQLite UNIQUE is case-sensitive by default. Case-insensitive uniqueness (e.g., "Electrical" vs "electrical") is enforced at the application layer by normalizing comparisons before insert/update. This avoids relying on SQLite collation which has limited case-folding for non-ASCII characters.
• Nullable color: Tags without a color use a default display color chosen by the frontend.
• Tags will be reused by household items (EPIC-04): The work_item_tags junction table is specific to work items, but a household_item_tags junction table will be added later using the same tags table.

work_item_tags

Junction table linking work items to tags (many-to-many relationship).

Column	Type	Constraints	Description
work_item_id	TEXT	NOT NULL, REFERENCES work_items(id) ON DELETE CASCADE	The work item
tag_id	TEXT	NOT NULL, REFERENCES tags(id) ON DELETE CASCADE	The tag

Primary key: Composite (work_item_id, tag_id)

Indexes:

Index	Columns	Type	Rationale
idx_work_item_tags_tag_id	tag_id	INDEX	Efficient lookup of all work items with a given tag (the composite PK already provides work_item_id lookup)

Design rationale:

• Composite primary key: No surrogate ID needed. The combination of work_item_id + tag_id is naturally unique.
• CASCADE on both sides: Deleting a work item removes its tag associations. Deleting a tag removes it from all work items.
• Index on tag_id: The composite PK index is ordered (work_item_id, tag_id), which is efficient for "get all tags for a work item." The separate tag_id index supports the reverse query: "get all work items for a tag" (used in filtering).

work_item_notes

Free-form notes attached to a work item, attributed to the user who wrote them. Supports an activity-log style display on the work item detail page.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
work_item_id	TEXT	NOT NULL, REFERENCES work_items(id) ON DELETE CASCADE	Parent work item
content	TEXT	NOT NULL	Note content (free-form text)
created_by	TEXT	NOT NULL, REFERENCES users(id) ON DELETE SET NULL	User who authored the note
created_at	TEXT	NOT NULL	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL	ISO 8601 timestamp, updated on edit

Indexes:

Index	Columns	Type	Rationale
idx_work_item_notes_work_item_id	work_item_id	INDEX	Fast retrieval of all notes for a work item

Design rationale:

• created_by ON DELETE SET NULL: If the authoring user is removed, the note survives with an unknown author. This preserves project history.
• CASCADE from work_item_id: When a work item is deleted, all its notes are deleted. Notes do not exist independently of their parent work item.
• updated_at included: Notes can be edited; tracking the last edit time helps users see if a note has been modified since they last read it.

work_item_subtasks

Checklist items within a work item. Subtasks are ordered and can be individually completed.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
work_item_id	TEXT	NOT NULL, REFERENCES work_items(id) ON DELETE CASCADE	Parent work item
title	TEXT	NOT NULL	Subtask description
is_completed	INTEGER	NOT NULL DEFAULT 0	Completion state (0 = incomplete, 1 = completed)
sort_order	INTEGER	NOT NULL DEFAULT 0	Display order (ascending); supports drag-and-drop reordering
created_at	TEXT	NOT NULL	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL	ISO 8601 timestamp, updated on modification

Indexes:

Index	Columns	Type	Rationale
idx_work_item_subtasks_work_item_id	work_item_id	INDEX	Fast retrieval of all subtasks for a work item

Design rationale:

• is_completed as INTEGER: SQLite has no native boolean type. INTEGER with 0/1 convention is standard.
• sort_order for drag-and-drop: Enables client-side reordering. The bulk reorder endpoint updates sort_order values for all subtasks in a single operation, avoiding per-item PATCH calls.
• CASCADE from work_item_id: Subtasks are tightly coupled to their parent work item and are deleted when the work item is deleted.

work_item_dependencies

Defines predecessor/successor relationships between work items for scheduling and Gantt chart visualization. Supports four standard dependency types used in project management. The lead_lag_days column (added by migration 0006 in EPIC-06) supports lead and lag time between dependent tasks.

Column	Type	Constraints	Description
predecessor_id	TEXT	NOT NULL, REFERENCES work_items(id) ON DELETE CASCADE	The work item that must complete (or start) first
successor_id	TEXT	NOT NULL, REFERENCES work_items(id) ON DELETE CASCADE	The work item that depends on the predecessor
dependency_type	TEXT	NOT NULL DEFAULT 'finish_to_start' CHECK(dependency_type IN ('finish_to_start','start_to_start','finish_to_finish','start_to_finish'))	Type of scheduling dependency
lead_lag_days	INTEGER	NOT NULL DEFAULT 0	Lead (negative) or lag (positive) days offset; e.g., +3 = 3-day gap, -2 = 2-day overlap

Primary key: Composite (predecessor_id, successor_id)

Check constraint: predecessor_id != successor_id (prevents self-referencing dependencies)

Indexes:

Index	Columns	Type	Rationale
idx_work_item_dependencies_successor_id	successor_id	INDEX	Efficient lookup of predecessors for a given work item (the composite PK index covers predecessor_id lookups)

Dependency types:

Value	Abbreviation	Description
finish_to_start	FS	Successor cannot start until predecessor finishes (most common)
start_to_start	SS	Successor cannot start until predecessor starts
finish_to_finish	FF	Successor cannot finish until predecessor finishes
start_to_finish	SF	Successor cannot finish until predecessor starts (rare)

Design rationale:

• Composite primary key: A dependency between two work items is uniquely identified by the pair. No surrogate ID needed. This also prevents duplicate dependencies between the same two items.
• No separate id column: The delete endpoint uses predecessor_id as the identifier (within the context of the successor work item), which maps directly to the composite PK. This keeps the API clean.
• Self-reference CHECK constraint: Prevents nonsensical self-dependencies at the database level.
• Circular dependency detection at application layer: SQLite cannot enforce acyclicity via constraints. The API layer performs a depth-first traversal before creating a dependency to detect and reject cycles.
• CASCADE on both FKs: When either work item in a dependency pair is deleted, the dependency is removed. This prevents orphaned dependencies and ensures the dependency graph stays valid.
• finish_to_start as default: This is by far the most common dependency type in construction projects ("do this before that").
• lead_lag_days column (added by migration 0006): Supports lead and lag time offsets between dependent tasks. Positive values add waiting time (lag); negative values allow overlap (lead). Default 0 means the dependency relationship has no time offset. This column is used by the scheduling engine (ADR-014) when computing earliest/latest start and finish dates.

Entity Relationship Diagram (EPIC-01 + EPIC-03)

+------------------+          +------------------+
|     users        |          |    sessions      |
+------------------+          +------------------+
| id (PK, UUID)   |<---------| id (PK, random)  |
| email (UNIQUE)   |    1:N   | user_id (FK)     |
| display_name     |          | expires_at       |
| role             |          | created_at       |
| auth_provider    |          +------------------+
| password_hash    |
| oidc_subject     |
| deactivated_at   |
| created_at       |
| updated_at       |
+------------------+
    |         |
    |  1:N    | 1:N (assigned / created_by)
    |         |
    |    +----+----------------+
    |    |    work_items       |
    |    +---------------------+
    |    | id (PK, UUID)       |
    |    | title               |
    |    | description         |
    |    | status              |
    |    | start_date          |
    |    | end_date            |
    |    | duration_days       |
    |    | start_after         |
    |    | start_before        |
    |    | assigned_user_id FK |
    |    | created_by FK       |
    |    | created_at          |
    |    | updated_at          |
    |    +---------------------+
    |       |        |      |
    |       | 1:N    | M:N  | 1:N
    |       |        |      |
    |  +----+---+ +--+----+ +--------+----------+
    |  | notes  | | w_i_  | | subtasks          |
    |  +--------+ | tags  | +-------------------+
    |  | id     | +-------+ | id                |
    |  | w_i_id | | w_i_id| | work_item_id FK   |
    |  | content| | tag_id| | title             |
    |  | c_by FK| +-------+ | is_completed      |
    |  | c_at   |     |     | sort_order        |
    |  | u_at   |     |     | created_at        |
    |  +--------+     |     | updated_at        |
    |            +----+---+ +-------------------+
    |            | tags   |
    |            +--------+
    |            | id     |
    |            | name   |
    |            | color  |
    |            | c_at   |
    |            +--------+
    |
    |     work_item_dependencies
    |     +---------------------+
    |     | predecessor_id FK   |---+
    |     | successor_id FK     |---+---> work_items
    |     | dependency_type     |
    |     +---------------------+

Key relationships:

• users -> work_items: One user can be assigned to many work items (assigned_user_id). One user can create many work items (created_by). Both use SET NULL on delete.
• work_items -> work_item_tags -> tags: Many-to-many. A work item can have multiple tags; a tag can be on multiple work items.
• work_items -> work_item_notes: One-to-many. A work item can have many notes. Notes cascade-delete with their parent.
• work_items -> work_item_subtasks: One-to-many. A work item can have many subtasks. Subtasks cascade-delete with their parent.
• work_items -> work_item_dependencies -> work_items: Many-to-many self-referential. A work item can have multiple predecessors and multiple successors.

Migration File

server/src/db/migrations/0002_create_work_items.sql

-- EPIC-03: Work Items Core CRUD & Properties
-- Creates the work items, tags, notes, subtasks, and dependencies tables.

CREATE TABLE work_items (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'not_started' CHECK(status IN ('not_started', 'in_progress', 'completed', 'blocked')),
  start_date TEXT,
  end_date TEXT,
  duration_days INTEGER,
  start_after TEXT,
  start_before TEXT,
  assigned_user_id TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_by TEXT NOT NULL REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE INDEX idx_work_items_status ON work_items (status);
CREATE INDEX idx_work_items_assigned_user_id ON work_items (assigned_user_id);
CREATE INDEX idx_work_items_created_at ON work_items (created_at);

CREATE TABLE tags (
  id TEXT PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  color TEXT,
  created_at TEXT NOT NULL
);

CREATE TABLE work_item_tags (
  work_item_id TEXT NOT NULL REFERENCES work_items(id) ON DELETE CASCADE,
  tag_id TEXT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (work_item_id, tag_id)
);

CREATE INDEX idx_work_item_tags_tag_id ON work_item_tags (tag_id);

CREATE TABLE work_item_notes (
  id TEXT PRIMARY KEY,
  work_item_id TEXT NOT NULL REFERENCES work_items(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_by TEXT NOT NULL REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE INDEX idx_work_item_notes_work_item_id ON work_item_notes (work_item_id);

CREATE TABLE work_item_subtasks (
  id TEXT PRIMARY KEY,
  work_item_id TEXT NOT NULL REFERENCES work_items(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  is_completed INTEGER NOT NULL DEFAULT 0,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE INDEX idx_work_item_subtasks_work_item_id ON work_item_subtasks (work_item_id);

CREATE TABLE work_item_dependencies (
  predecessor_id TEXT NOT NULL REFERENCES work_items(id) ON DELETE CASCADE,
  successor_id TEXT NOT NULL REFERENCES work_items(id) ON DELETE CASCADE,
  dependency_type TEXT NOT NULL DEFAULT 'finish_to_start' CHECK(dependency_type IN ('finish_to_start', 'start_to_start', 'finish_to_finish', 'start_to_finish')),
  PRIMARY KEY (predecessor_id, successor_id),
  CHECK (predecessor_id != successor_id)
);

CREATE INDEX idx_work_item_dependencies_successor_id ON work_item_dependencies (successor_id);

-- Rollback:
-- DROP INDEX IF EXISTS idx_work_item_dependencies_successor_id;
-- DROP TABLE IF EXISTS work_item_dependencies;
-- DROP INDEX IF EXISTS idx_work_item_subtasks_work_item_id;
-- DROP TABLE IF EXISTS work_item_subtasks;
-- DROP INDEX IF EXISTS idx_work_item_notes_work_item_id;
-- DROP TABLE IF EXISTS work_item_notes;
-- DROP INDEX IF EXISTS idx_work_item_tags_tag_id;
-- DROP TABLE IF EXISTS work_item_tags;
-- DROP TABLE IF EXISTS tags;
-- DROP INDEX IF EXISTS idx_work_items_created_at;
-- DROP INDEX IF EXISTS idx_work_items_assigned_user_id;
-- DROP INDEX IF EXISTS idx_work_items_status;
-- DROP TABLE IF EXISTS work_items;

---

EPIC-05: Budget Management

EPIC-05 adds budget tracking with categories, vendors, invoices, financing sources, and subsidy programs. All budget tables are created in a single foundation migration (0003) so that later stories only add API routes and UI.

budget_categories

Pre-defined and user-extensible categories for organizing construction costs (e.g., Materials, Labor, Permits). Seeded with 10 default categories on first migration.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
name	TEXT	UNIQUE NOT NULL	Category name (e.g., "Materials", "Labor")
description	TEXT	nullable	Optional description of what this category covers
color	TEXT	nullable	Hex color code (e.g., #3B82F6) for UI display
sort_order	INTEGER	NOT NULL DEFAULT 0	Display order (ascending); supports drag-and-drop reordering
created_at	TEXT	NOT NULL DEFAULT (datetime('now'))	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL DEFAULT (datetime('now'))	ISO 8601 timestamp, updated on every modification

Indexes:

Index	Columns	Type	Rationale
sqlite_autoindex_budget_categories_1	name	UNIQUE	Enforced by UNIQUE constraint; prevents duplicate category names

Design rationale:

• TEXT primary key (UUID): Consistent with all other entities in the schema. UUIDs prevent information leakage about category count.
• sort_order for display ordering: Categories have a natural display order (e.g., Materials first, Other last). Users can reorder via the UI. The sort_order field enables this without relying on alphabetical sorting.
• created_at / updated_at with DEFAULT: Budget categories use SQLite's datetime('now') default unlike the application-generated timestamps in earlier tables. This is safe because budget categories are primarily managed through simple CRUD operations where the application will still set these explicitly on insert/update -- the DEFAULT acts as a safety net for seed data.
• 10 default categories seeded in migration: The migration includes INSERT statements to seed default categories. These provide a useful starting point; users can rename, reorder, or delete them.
• No created_by: Budget categories are a shared organizational resource, not user-authored content. Tracking who created a category adds no value.

vendors

Vendor/contractor database for tracking companies and individuals involved in the construction project.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
name	TEXT	NOT NULL	Vendor/contractor name
specialty	TEXT	nullable	Area of expertise (e.g., "Electrical", "Plumbing")
phone	TEXT	nullable	Contact phone number
email	TEXT	nullable	Contact email address
address	TEXT	nullable	Business address
notes	TEXT	nullable	Free-form notes about this vendor
created_by	TEXT	nullable, REFERENCES users(id) ON DELETE SET NULL	User who added this vendor
created_at	TEXT	NOT NULL DEFAULT (datetime('now'))	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL DEFAULT (datetime('now'))	ISO 8601 timestamp, updated on every modification

Indexes:

Index	Columns	Type	Rationale
idx_vendors_name	name	INDEX	Supports sorting and searching vendors by name

Design rationale:

• name is NOT UNIQUE: Multiple vendors can share a name (e.g., "Smith Construction" in different cities). Users distinguish them by other fields.
• created_by ON DELETE SET NULL: Preserves vendor records even if the creating user is removed. Consistent with work_items pattern.
• No separate address fields: A single address TEXT field is sufficient for this scale. Structured address parsing (street, city, state, zip) would add complexity without benefit for < 5 users.

invoices

Tracks invoices from vendors for payment management and cost tracking. Invoices can optionally be linked to a specific work item budget line for granular cost tracking.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
vendor_id	TEXT	NOT NULL, REFERENCES vendors(id) ON DELETE CASCADE	The vendor who issued this invoice
work_item_budget_id	TEXT	nullable, REFERENCES work_item_budgets(id) ON DELETE SET NULL	Optional link to a budget line for cost tracking
invoice_number	TEXT	nullable	Vendor's invoice number/reference
amount	REAL	NOT NULL	Invoice amount in the project's currency
date	TEXT	NOT NULL	Invoice date (ISO 8601 date, e.g., 2026-04-15)
due_date	TEXT	nullable	Payment due date (ISO 8601 date)
status	TEXT	NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','paid','claimed'))	Payment status
notes	TEXT	nullable	Free-form notes about this invoice
created_by	TEXT	nullable, REFERENCES users(id) ON DELETE SET NULL	User who recorded this invoice
created_at	TEXT	NOT NULL DEFAULT (datetime('now'))	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL DEFAULT (datetime('now'))	ISO 8601 timestamp, updated on every modification

Indexes:

Index	Columns	Type	Rationale
idx_invoices_vendor_id	vendor_id	INDEX	Fast lookup of all invoices for a vendor
idx_invoices_status	status	INDEX	Filter invoices by payment status
idx_invoices_date	date	INDEX	Sort/filter invoices by date
idx_invoices_work_item_budget_id	work_item_budget_id	INDEX	Fast lookup of all invoices linked to a budget line (used for actualCost computation)

Invoice status values:

Value	Description
pending	Invoice received, payment not yet made (default)
paid	Payment has been completed
claimed	Invoice has been claimed (e.g., submitted to a subsidy program or financing source for reimbursement)

Design rationale:

• amount as REAL: SQLite does not have a DECIMAL type. REAL (IEEE 754 double) provides sufficient precision for construction invoices (typically whole dollars or two decimal places). For a < 5 user home building app, floating-point rounding is acceptable. If exact decimal arithmetic were needed, amounts could be stored as INTEGER cents, but that adds conversion overhead throughout the application layer.
• CASCADE from vendor_id: Deleting a vendor removes all its invoices. This is consistent with the "vendor owns their invoices" model. If invoice history must survive vendor deletion, this should be revisited.
• created_by ON DELETE SET NULL: Preserves invoice records when the recording user is removed.
• work_item_budget_id FK with ON DELETE SET NULL: Invoices can optionally be linked to a budget line. If the budget line is deleted, the invoice survives with the link cleared. This allows invoices to exist independently (some may be for general project costs not tied to a specific work item budget line).
• Status changed from overdue to claimed: The overdue status was replaced by claimed in Story 5.9. Overdue detection is better handled as a computed state (comparing due_date to the current date) rather than a manually set status. The claimed status supports the subsidy/reimbursement workflow where an invoice has been submitted for payment by a third party.

budget_sources

Financing sources for the construction project (bank loans, credit lines, savings accounts, etc.).

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
name	TEXT	NOT NULL	Source name (e.g., "ABC Bank Mortgage", "Home Savings")
source_type	TEXT	NOT NULL CHECK(source_type IN ('bank_loan','credit_line','savings','other','discretionary'))	Type of financing
is_discretionary	INTEGER	NOT NULL DEFAULT 0	1 for the system-managed discretionary funding source, 0 otherwise
total_amount	REAL	NOT NULL	Total amount available from this source
interest_rate	REAL	nullable	Annual interest rate as a decimal (e.g., 3.5 for 3.5%)
terms	TEXT	nullable	Loan terms description (e.g., "30-year fixed", "5-year revolving")
notes	TEXT	nullable	Free-form notes
status	TEXT	NOT NULL DEFAULT 'active' CHECK(status IN ('active','exhausted','closed'))	Current status of this funding source
created_by	TEXT	nullable, REFERENCES users(id) ON DELETE SET NULL	User who added this source
created_at	TEXT	NOT NULL DEFAULT (datetime('now'))	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL DEFAULT (datetime('now'))	ISO 8601 timestamp, updated on every modification

Budget source type values:

Value	Description
bank_loan	Traditional bank loan or mortgage
credit_line	Revolving credit line
savings	Personal savings or cash reserves
other	Any other financing source
discretionary	System-managed source for unallocated invoice remainders (see ADR-019)

Budget source status values:

Value	Description
active	Source is available for use (default)
exhausted	All funds have been allocated/spent
closed	Source has been closed or terminated

Design rationale:

• interest_rate as REAL: Stored as a percentage value (e.g., 3.5 means 3.5%), not a fraction. This matches how users think about rates and avoids conversion errors.
• total_amount without used_amount or claimed_amount: Both usedAmount (planned allocation: sum of linked budget lines' planned_amount) and claimedAmount (actual drawdown: sum of claimed invoices on linked budget lines) are computed at the API layer. Storing them would create data consistency risks. The API also exposes derived fields availableAmount (totalAmount - usedAmount) and actualAvailableAmount (totalAmount - claimedAmount) for the two perspectives (planned vs actual).
• No payment schedule table: The requirements mention "Payment schedules to creditors" but for this scale (< 5 users), the terms and notes fields are sufficient to describe payment schedules. A dedicated payment schedule table would add significant complexity for minimal benefit.
• is_discretionary sentinel column: The is_discretionary flag identifies the system-managed "Discretionary Funding" row (seeded by migration 0021 with id discretionary-system). This source captures invoice remainder amounts (invoice total minus sum of itemized budget line amounts) and budget lines with NULL budget_source_id. It cannot be deleted or have its source_type changed. Its total_amount is always 0 because all amounts are computed at the API layer. The discretionary source type is not available in create/update schemas -- only system-seeded.

subsidy_programs

Government or institutional programs that reduce construction costs through percentage discounts or fixed reductions.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
name	TEXT	NOT NULL	Program name (e.g., "Green Building Tax Credit")
description	TEXT	nullable	Program description
eligibility	TEXT	nullable	Eligibility requirements description
reduction_type	TEXT	NOT NULL CHECK(reduction_type IN ('percentage','fixed'))	How the subsidy reduces costs
reduction_value	REAL	NOT NULL	Reduction amount: percentage (e.g., 15.0 for 15%) or fixed currency amount
application_status	TEXT	NOT NULL DEFAULT 'eligible' CHECK(application_status IN ('eligible','applied','approved','received','rejected'))	Current application status
application_deadline	TEXT	nullable	Application deadline (ISO 8601 date)
notes	TEXT	nullable	Free-form notes
created_by	TEXT	nullable, REFERENCES users(id) ON DELETE SET NULL	User who added this program
created_at	TEXT	NOT NULL DEFAULT (datetime('now'))	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL DEFAULT (datetime('now'))	ISO 8601 timestamp, updated on every modification

Application status values:

Value	Description
eligible	Program identified, not yet applied (default)
applied	Application has been submitted
approved	Application approved, awaiting funds
received	Subsidy funds/discount received
rejected	Application was rejected

Design rationale:

• reduction_value interpretation depends on reduction_type: When reduction_type is 'percentage', reduction_value is a percentage (e.g., 15.0 means 15% off). When reduction_type is 'fixed', reduction_value is a currency amount. The application layer handles the math based on the type.
• application_status as a linear progression: The five statuses represent a typical subsidy application lifecycle. The progression is not enforced at the database level (application logic can allow any status transition) because real-world processes may skip steps.
• eligibility as free text: Eligibility criteria vary wildly between programs. A structured approach (income limits, property type, etc.) would be over-engineering for this use case.

subsidy_program_categories

Junction table linking subsidy programs to the budget categories they apply to (many-to-many). A subsidy program may apply to multiple categories (e.g., a "Green Building" subsidy applies to both Materials and Equipment).

Column	Type	Constraints	Description
subsidy_program_id	TEXT	NOT NULL, REFERENCES subsidy_programs(id) ON DELETE CASCADE	The subsidy program
budget_category_id	TEXT	NOT NULL, REFERENCES budget_categories(id) ON DELETE CASCADE	The applicable budget category

Primary key: Composite (subsidy_program_id, budget_category_id)

Design rationale:

• CASCADE on both sides: Deleting a subsidy program removes its category associations. Deleting a budget category removes it from all subsidy programs.
• Composite primary key: No surrogate ID needed. Consistent with other junction tables in the schema.

work_item_budgets

Budget line items for a work item. Each budget line represents a cost estimate or allocation with its own confidence level, optional vendor, budget category, and budget source. This replaces the previous flat budget fields on work_items and the work_item_vendors junction table, enabling multiple budget lines per work item (e.g., separate labor and materials estimates).

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
work_item_id	TEXT	NOT NULL, REFERENCES work_items(id) ON DELETE CASCADE	Parent work item
description	TEXT	nullable	Description of what this budget line covers (max 500 chars)
planned_amount	REAL	NOT NULL DEFAULT 0, CHECK(planned_amount >= 0)	Planned/estimated cost amount
confidence	TEXT	NOT NULL DEFAULT 'own_estimate' CHECK(confidence IN ('own_estimate','professional_estimate','quote','invoice'))	Cost confidence level
budget_category_id	TEXT	nullable, REFERENCES budget_categories(id) ON DELETE SET NULL	Optional budget category for this line
budget_source_id	TEXT	nullable, REFERENCES budget_sources(id) ON DELETE SET NULL	Optional financing source for this line
vendor_id	TEXT	nullable, REFERENCES vendors(id) ON DELETE SET NULL	Optional vendor associated with this line
created_by	TEXT	nullable, REFERENCES users(id) ON DELETE SET NULL	User who created this budget line
created_at	TEXT	NOT NULL	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL	ISO 8601 timestamp, updated on every modification

Indexes:

Index	Columns	Type	Rationale
idx_work_item_budgets_work_item_id	work_item_id	INDEX	Fast retrieval of all budget lines for a work item
idx_work_item_budgets_vendor_id	vendor_id	INDEX	Fast lookup of budget lines by vendor (used by vendor delete check)
idx_work_item_budgets_budget_category_id	budget_category_id	INDEX	Fast lookup of budget lines by category (used by category delete check)
idx_work_item_budgets_budget_source_id	budget_source_id	INDEX	Fast lookup of budget lines by financing source

Confidence levels:

DB Value	Display Label	Margin	Description
own_estimate	Own Estimate	+/-20%	Homeowner's rough estimate (default)
professional_estimate	Professional Estimate	+/-10%	Estimate from a contractor or professional
quote	Quote	+/-5%	Formal quote from a vendor
invoice	Invoice	+/-0%	Final invoiced amount (most accurate)

Design rationale:

• Replaces flat budget columns on work_items: The original design (migration 0004) added planned_budget, actual_cost, confidence_percent, budget_category_id, and budget_source_id directly to the work_items table. This limited each work item to a single budget estimate with one vendor, one category, and one source. The budget lines model allows multiple estimates per work item, each with independent confidence, vendor, category, and source associations.
• Replaces work_item_vendors junction table: The vendor relationship is now captured per-budget-line rather than as a simple many-to-many. This is more meaningful: instead of "this work item involves vendor X", it now says "vendor X quoted $5,000 for this specific aspect of the work item."
• planned_amount >= 0: Budget amounts cannot be negative. A CHECK constraint enforces this at the database level.
• confidence as enum instead of confidence_percent: The original design used an integer percentage. The enum approach is more structured and maps directly to known confidence margins (20%, 10%, 5%, 0%). The application layer computes the margin range from the enum value.
• ON DELETE SET NULL for vendor, category, source FKs: If a referenced entity is deleted, the budget line survives with the reference cleared. This preserves budget data even when reorganizing vendors or categories.
• CASCADE from work_item_id: Budget lines are tightly coupled to their parent work item and are deleted when the work item is deleted.
• created_by ON DELETE SET NULL: Preserves budget line records when the creating user is removed. Consistent with other entities.
• Actual cost is computed, not stored: The actual cost for a budget line is the sum of all linked invoices (via invoices.work_item_budget_id). Storing it would create a data consistency risk.

work_item_vendors (DROPPED -- Story 5.9)

This table was dropped in migration 0005. The work item-to-vendor relationship is now captured through work_item_budgets.vendor_id, which provides a more meaningful association: each budget line can reference the vendor responsible for that specific cost estimate or work allocation.

Previously, this was a simple many-to-many junction table linking work items to vendors. The work_item_budgets model supersedes it by embedding the vendor reference in each budget line.

work_item_subsidies

Junction table linking work items to subsidy programs (many-to-many). A work item can benefit from multiple subsidies; a subsidy program can apply to multiple work items.

Column	Type	Constraints	Description
work_item_id	TEXT	NOT NULL, REFERENCES work_items(id) ON DELETE CASCADE	The work item
subsidy_program_id	TEXT	NOT NULL, REFERENCES subsidy_programs(id) ON DELETE CASCADE	The subsidy program

Primary key: Composite (work_item_id, subsidy_program_id)

Indexes:

Index	Columns	Type	Rationale
idx_work_item_subsidies_subsidy_program_id	subsidy_program_id	INDEX	Efficient lookup of all work items for a given subsidy program

Design rationale:

• CASCADE on both sides: Deleting a work item removes its subsidy associations. Deleting a subsidy program removes it from all work items.
• No applied_amount column: The subsidy reduction is computed from the subsidy program's reduction_type and reduction_value applied to the work item's budget. Storing a per-work-item amount would duplicate information and risk inconsistency.

Entity Relationship Diagram (EPIC-01 + EPIC-03 + EPIC-05)

+------------------+          +------------------+
|     users        |          |    sessions      |
+------------------+          +------------------+
| id (PK, UUID)   |<---------| id (PK, random)  |
| email (UNIQUE)   |    1:N   | user_id (FK)     |
| display_name     |          | expires_at       |
| role             |          | created_at       |
| auth_provider    |          +------------------+
| password_hash    |
| oidc_subject     |
| deactivated_at   |
| created_at       |
| updated_at       |
+------------------+
    |         |         |
    |  1:N    | 1:N     | 1:N (created_by on vendors, invoices, budgets, etc.)
    |         |         |
    |    +----+---------+------+
    |    |    work_items       |          +---------------------+
    |    +---------------------+          | budget_categories   |
    |    | id (PK, UUID)       |          +---------------------+
    |    | title               |          | id (PK, UUID)       |
    |    | description         |          | name (UNIQUE)       |
    |    | status              |          | description         |
    |    | start_date          |          | color               |
    |    | end_date            |          | sort_order           |
    |    | duration_days       |          | created_at          |
    |    | start_after         |          | updated_at          |
    |    | start_before        |          +---------------------+
    |    | assigned_user_id FK |               |          |
    |    | created_by FK       |               | M:N      | 0:N
    |    | created_at          |               |          |
    |    | updated_at          |     +---------+----+     |
    |    +---------------------+     | subsidy_prog_|     |
    |       |   |    |    |    |     | categories   |     |
    |       |   |    |    |    |     +--------------+     |
    |       |   |    |    |    |     | sub_prog_id  |     |
    |       |   |    |    |    |     | bud_cat_id   |     |
    |       |   |    |    |    |     +--------------+     |
    |       |   |    |    |    |           |              |
    |       |   |    |    |  M:N  +-------+--------+     |
    |       |   |    |    |   |   | subsidy_programs|     |
    |       |   |    |    |   |   +-----------------+     |
    |       |   |    |  +-+---++  | id (PK, UUID)   |     |
    |       |   |    |  | w_i_ |  | name             |     |
    |       |   |    |  | subs |  | reduction_type   |     |
    |       |   |    |  +------+  | reduction_value  |     |
    |       |   |    |  | w_i_id| | app_status       |     |
    |       |   |    |  | sub_id| +-----------------+     |
    |       |   |    |  +------+                          |
    |       |   |    |                                    |
    |       |   |  1:N  (work_item_budgets)               |
    |       |   |    |                                    |
    |       |   |  +-+-----------------------+            |
    |       |   |  | work_item_budgets       |            |
    |       |   |  +-------------------------+            |
    |       |   |  | id (PK, UUID)           |            |
    |       |   |  | work_item_id FK         |            |
    |       |   |  | description             |            |
    |       |   |  | planned_amount          |            |
    |       |   |  | confidence (enum)       |            |
    |       |   |  | budget_category_id FK --+------------+
    |       |   |  | budget_source_id FK ----+---> budget_sources
    |       |   |  | vendor_id FK -----------+---> vendors
    |       |   |  | created_by FK           |
    |       |   |  +-------------------------+
    |       |   |           |
    |       | 1:N   M:N    | 0:N
    |       |   |    |     |
    |  +----+--+ +--+---+ |    +---------------------+
    |  | notes | | w_i_ | |    | vendors             |
    |  +-------+ | tags | |    +---------------------+
    |  | id    | +------+ |    | id (PK, UUID)       |
    |  | w_i_id| | w_i_id||    | name                |
    |  | content| tag_id||    | specialty           |
    |  | c_by  | +------+|    | phone, email        |
    |  | c_at  |    |    |    | created_by FK       |
    |  | u_at  |    |    |    +---------------------+
    |  +-------+    |    |          |
    |          +----+--+ |          | 1:N
    |          | tags   | |          |
    |          +--------+ |    +----+-----------+
    |          | id     | |    | invoices       |
    |          | name   | |    +----------------+
    |          | color  | |    | id             |
    |          | c_at   | +--->| w_i_budget_id  |
    |          +--------+      | vendor_id FK   |
    |                          | amount         |
    |  work_item_deps          | status         |
    |  +----------------+      | date           |
    |  | pred_id FK     |      | c_by FK        |
    |  | succ_id FK     |      +----------------+
    |  | dep_type       |
    |  | lead_lag_days  |
    |  +----------------+      +---------------------+
    |                          | budget_sources      |
    |  work_item_subtasks      +---------------------+
    |  +----------------+      | id (PK, UUID)       |
    |  | id             |      | name                |
    |  | work_item_id   |      | source_type         |
    |  | title          |      | total_amount        |
    |  | is_completed   |      | interest_rate       |
    |  | sort_order     |      | terms, status       |
    |  +----------------+      | created_by FK       |
    |                          +---------------------+

Key relationships (EPIC-05):

• work_items -> work_item_budgets: One-to-many. A work item can have multiple budget lines. Budget lines cascade-delete with their parent work item.
• work_item_budgets -> budget_categories: Many-to-one (optional). Each budget line can be assigned to one category. ON DELETE SET NULL preserves the budget line if the category is removed.
• work_item_budgets -> budget_sources: Many-to-one (optional). Each budget line can be linked to one financing source. ON DELETE SET NULL preserves the budget line if the source is removed.
• work_item_budgets -> vendors: Many-to-one (optional). Each budget line can reference one vendor. ON DELETE SET NULL preserves the budget line if the vendor is removed. This replaces the old work_item_vendors many-to-many junction table.
• work_item_budgets -> invoices (via invoices.work_item_budget_id): One-to-many. A budget line can have multiple invoices linked to it. ON DELETE SET NULL preserves invoices if the budget line is removed.
• budget_categories: Referenced by work_item_budgets and subsidy_program_categories. Delete is blocked if any budget lines or subsidy programs reference the category.
• vendors -> invoices: One-to-many. A vendor can have many invoices. Invoices cascade-delete with their vendor.
• work_items -> work_item_subsidies -> subsidy_programs: Many-to-many. A work item can benefit from multiple subsidies; a subsidy can apply to multiple work items.
• subsidy_programs -> subsidy_program_categories -> budget_categories: Many-to-many. A subsidy program can apply to multiple budget categories; a category can be covered by multiple subsidy programs.
• budget_sources: Referenced by work_item_budgets. Linked to specific budget lines per work item.
• users -> vendors/invoices/budget_sources/subsidy_programs/work_item_budgets: One-to-many via created_by. All use ON DELETE SET NULL to preserve records when users are removed.

Migration File

server/src/db/migrations/0003_create_budget_tables.sql

-- EPIC-05: Budget Management
-- Creates all budget-related tables: categories, vendors, invoices,
-- budget sources, subsidy programs, and junction tables.

-- Budget categories for organizing construction costs
CREATE TABLE budget_categories (
  id TEXT PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  description TEXT,
  color TEXT,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Seed default budget categories
INSERT INTO budget_categories (id, name, description, color, sort_order, created_at, updated_at) VALUES
  ('bc-materials',   'Materials',   'Raw materials and building supplies',             '#3B82F6', 0, datetime('now'), datetime('now')),
  ('bc-labor',       'Labor',       'Contractor and worker labor costs',               '#EF4444', 1, datetime('now'), datetime('now')),
  ('bc-permits',     'Permits',     'Building permits and regulatory fees',            '#F59E0B', 2, datetime('now'), datetime('now')),
  ('bc-design',      'Design',      'Architectural and design services',               '#8B5CF6', 3, datetime('now'), datetime('now')),
  ('bc-equipment',   'Equipment',   'Tools and equipment rental or purchase',          '#06B6D4', 4, datetime('now'), datetime('now')),
  ('bc-landscaping', 'Landscaping', 'Outdoor landscaping and hardscaping',             '#22C55E', 5, datetime('now'), datetime('now')),
  ('bc-utilities',   'Utilities',   'Utility connections and installations',           '#F97316', 6, datetime('now'), datetime('now')),
  ('bc-insurance',   'Insurance',   'Construction and builder risk insurance',         '#6366F1', 7, datetime('now'), datetime('now')),
  ('bc-contingency', 'Contingency', 'Reserve funds for unexpected costs',             '#EC4899', 8, datetime('now'), datetime('now')),
  ('bc-other',       'Other',       'Miscellaneous costs not covered by other categories', '#6B7280', 9, datetime('now'), datetime('now'));

-- Vendor/contractor database
CREATE TABLE vendors (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  specialty TEXT,
  phone TEXT,
  email TEXT,
  address TEXT,
  notes TEXT,
  created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_vendors_name ON vendors (name);

-- Invoice tracking per vendor
CREATE TABLE invoices (
  id TEXT PRIMARY KEY,
  vendor_id TEXT NOT NULL REFERENCES vendors(id) ON DELETE CASCADE,
  invoice_number TEXT,
  amount REAL NOT NULL,
  date TEXT NOT NULL,
  due_date TEXT,
  status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'paid', 'overdue')),
  notes TEXT,
  created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_invoices_vendor_id ON invoices (vendor_id);
CREATE INDEX idx_invoices_status ON invoices (status);
CREATE INDEX idx_invoices_date ON invoices (date);

-- Financing sources (bank loans, credit lines, savings, etc.)
CREATE TABLE budget_sources (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  source_type TEXT NOT NULL CHECK(source_type IN ('bank_loan', 'credit_line', 'savings', 'other', 'discretionary')),
  total_amount REAL NOT NULL,
  interest_rate REAL,
  terms TEXT,
  notes TEXT,
  status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'exhausted', 'closed')),
  is_discretionary INTEGER NOT NULL DEFAULT 0,
  created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Subsidy/incentive programs
CREATE TABLE subsidy_programs (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  eligibility TEXT,
  reduction_type TEXT NOT NULL CHECK(reduction_type IN ('percentage', 'fixed')),
  reduction_value REAL NOT NULL,
  application_status TEXT NOT NULL DEFAULT 'eligible' CHECK(application_status IN ('eligible', 'applied', 'approved', 'received', 'rejected')),
  application_deadline TEXT,
  notes TEXT,
  created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Junction: subsidy programs <-> budget categories (M:N)
CREATE TABLE subsidy_program_categories (
  subsidy_program_id TEXT NOT NULL REFERENCES subsidy_programs(id) ON DELETE CASCADE,
  budget_category_id TEXT NOT NULL REFERENCES budget_categories(id) ON DELETE CASCADE,
  PRIMARY KEY (subsidy_program_id, budget_category_id)
);

-- Junction: work items <-> vendors (M:N)
CREATE TABLE work_item_vendors (
  work_item_id TEXT NOT NULL REFERENCES work_items(id) ON DELETE CASCADE,
  vendor_id TEXT NOT NULL REFERENCES vendors(id) ON DELETE CASCADE,
  PRIMARY KEY (work_item_id, vendor_id)
);

CREATE INDEX idx_work_item_vendors_vendor_id ON work_item_vendors (vendor_id);

-- Junction: work items <-> subsidy programs (M:N)
CREATE TABLE work_item_subsidies (
  work_item_id TEXT NOT NULL REFERENCES work_items(id) ON DELETE CASCADE,
  subsidy_program_id TEXT NOT NULL REFERENCES subsidy_programs(id) ON DELETE CASCADE,
  PRIMARY KEY (work_item_id, subsidy_program_id)
);

CREATE INDEX idx_work_item_subsidies_subsidy_program_id ON work_item_subsidies (subsidy_program_id);

-- Rollback:
-- DROP INDEX IF EXISTS idx_work_item_subsidies_subsidy_program_id;
-- DROP TABLE IF EXISTS work_item_subsidies;
-- DROP INDEX IF EXISTS idx_work_item_vendors_vendor_id;
-- DROP TABLE IF EXISTS work_item_vendors;
-- DROP TABLE IF EXISTS subsidy_program_categories;
-- DROP TABLE IF EXISTS subsidy_programs;
-- DROP TABLE IF EXISTS budget_sources;
-- DROP INDEX IF EXISTS idx_invoices_date;
-- DROP INDEX IF EXISTS idx_invoices_status;
-- DROP INDEX IF EXISTS idx_invoices_vendor_id;
-- DROP TABLE IF EXISTS invoices;
-- DROP INDEX IF EXISTS idx_vendors_name;
-- DROP TABLE IF EXISTS vendors;
-- DROP TABLE IF EXISTS budget_categories;

Migration File (0004 -- superseded by 0005)

server/src/db/migrations/0004_add_work_item_budget_fields.sql

This migration added flat budget columns (planned_budget, actual_cost, confidence_percent, budget_category_id, budget_source_id) to the work_items table. These columns are removed by migration 0005 which replaces them with the work_item_budgets child table model.

-- EPIC-05 Story #147: Add budget fields to work items (SUPERSEDED by migration 0005)
ALTER TABLE work_items ADD COLUMN planned_budget REAL;
ALTER TABLE work_items ADD COLUMN actual_cost REAL;
ALTER TABLE work_items ADD COLUMN confidence_percent INTEGER;
ALTER TABLE work_items ADD COLUMN budget_category_id TEXT REFERENCES budget_categories(id) ON DELETE SET NULL;
ALTER TABLE work_items ADD COLUMN budget_source_id TEXT REFERENCES budget_sources(id) ON DELETE SET NULL;

CREATE INDEX idx_work_items_budget_category ON work_items(budget_category_id);
CREATE INDEX idx_work_items_budget_source ON work_items(budget_source_id);

Migration File (0005 -- Budget System Rework)

server/src/db/migrations/0005_budget_system_rework.sql

This migration performs the budget system rework (Story 5.9):

1. Creates the work_item_budgets table
2. Recreates work_items without the flat budget columns (SQLite requires table recreation for column removal)
3. Adds work_item_budget_id FK to invoices and changes the status enum from pending|paid|overdue to pending|paid|claimed (also requires table recreation)
4. Drops the work_item_vendors junction table

Note: Because SQLite does not support DROP COLUMN or ALTER COLUMN, migrations 0005 recreates the work_items and invoices tables by:

1. Creating a new table with the desired schema
2. Copying data from the old table
3. Dropping the old table
4. Renaming the new table
5. Recreating indexes and foreign key references

This is the standard SQLite pattern for schema changes that cannot be expressed as ALTER TABLE ADD COLUMN.

-- EPIC-05 Story 5.9: Budget System Rework
-- Replaces flat budget fields on work_items with work_item_budgets child table.
-- Adds work_item_budget_id to invoices, changes invoice status enum.
-- Drops work_item_vendors junction table.

-- 1. Create work_item_budgets table
CREATE TABLE work_item_budgets (
  id TEXT PRIMARY KEY,
  work_item_id TEXT NOT NULL REFERENCES work_items(id) ON DELETE CASCADE,
  description TEXT,
  planned_amount REAL NOT NULL DEFAULT 0 CHECK(planned_amount >= 0),
  confidence TEXT NOT NULL DEFAULT 'own_estimate'
    CHECK(confidence IN ('own_estimate', 'professional_estimate', 'quote', 'invoice')),
  budget_category_id TEXT REFERENCES budget_categories(id) ON DELETE SET NULL,
  budget_source_id TEXT REFERENCES budget_sources(id) ON DELETE SET NULL,
  vendor_id TEXT REFERENCES vendors(id) ON DELETE SET NULL,
  created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE INDEX idx_work_item_budgets_work_item_id ON work_item_budgets(work_item_id);
CREATE INDEX idx_work_item_budgets_vendor_id ON work_item_budgets(vendor_id);
CREATE INDEX idx_work_item_budgets_budget_category_id ON work_item_budgets(budget_category_id);
CREATE INDEX idx_work_item_budgets_budget_source_id ON work_item_budgets(budget_source_id);

-- 2. Recreate work_items without budget columns
--    (SQLite does not support DROP COLUMN for columns with FK constraints)
CREATE TABLE work_items_new (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'not_started'
    CHECK(status IN ('not_started', 'in_progress', 'completed', 'blocked')),
  start_date TEXT,
  end_date TEXT,
  duration_days INTEGER,
  start_after TEXT,
  start_before TEXT,
  assigned_user_id TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_by TEXT NOT NULL REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

INSERT INTO work_items_new (id, title, description, status, start_date, end_date,
  duration_days, start_after, start_before, assigned_user_id, created_by,
  created_at, updated_at)
SELECT id, title, description, status, start_date, end_date,
  duration_days, start_after, start_before, assigned_user_id, created_by,
  created_at, updated_at
FROM work_items;

DROP TABLE work_items;
ALTER TABLE work_items_new RENAME TO work_items;

CREATE INDEX idx_work_items_status ON work_items(status);
CREATE INDEX idx_work_items_assigned_user_id ON work_items(assigned_user_id);
CREATE INDEX idx_work_items_created_at ON work_items(created_at);

-- 3. Recreate invoices with work_item_budget_id FK and updated status enum
CREATE TABLE invoices_new (
  id TEXT PRIMARY KEY,
  vendor_id TEXT NOT NULL REFERENCES vendors(id) ON DELETE CASCADE,
  work_item_budget_id TEXT REFERENCES work_item_budgets(id) ON DELETE SET NULL,
  invoice_number TEXT,
  amount REAL NOT NULL,
  date TEXT NOT NULL,
  due_date TEXT,
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK(status IN ('pending', 'paid', 'claimed')),
  notes TEXT,
  created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO invoices_new (id, vendor_id, invoice_number, amount, date, due_date,
  status, notes, created_by, created_at, updated_at)
SELECT id, vendor_id, invoice_number, amount, date, due_date,
  CASE WHEN status = 'overdue' THEN 'pending' ELSE status END,
  notes, created_by, created_at, updated_at
FROM invoices;

DROP TABLE invoices;
ALTER TABLE invoices_new RENAME TO invoices;

CREATE INDEX idx_invoices_vendor_id ON invoices(vendor_id);
CREATE INDEX idx_invoices_status ON invoices(status);
CREATE INDEX idx_invoices_date ON invoices(date);
CREATE INDEX idx_invoices_work_item_budget_id ON invoices(work_item_budget_id);

-- 4. Drop work_item_vendors junction table (replaced by work_item_budgets.vendor_id)
DROP INDEX IF EXISTS idx_work_item_vendors_vendor_id;
DROP TABLE IF EXISTS work_item_vendors;

-- Rollback (requires reversing the table recreations):
-- This migration is not trivially reversible due to SQLite table recreation.
-- To rollback: restore from backup or rerun migrations 0001-0004 on a fresh database.

---

EPIC-06: Timeline, Gantt Chart & Dependency Management

EPIC-06 adds milestones, milestone-work-item associations, and lead/lag days for dependencies. These tables support the Gantt chart visualization, scheduling engine, and milestone tracking features.

milestones

Major project milestones that mark significant progress points in the construction project. Milestones have a target date and can be linked to work items that contribute to that milestone.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT	Auto-incrementing integer ID
title	TEXT	NOT NULL	Milestone title (e.g., "Foundation Complete")
description	TEXT	nullable	Detailed description of the milestone
target_date	TEXT	NOT NULL	Target completion date (ISO 8601 date, e.g., 2026-04-15)
is_completed	INTEGER	NOT NULL DEFAULT 0	Completion state (0 = not completed, 1 = completed)
completed_at	TEXT	nullable	ISO 8601 timestamp when the milestone was marked complete
color	TEXT	nullable	Hex color code (e.g., #FF5733) for Gantt chart display
created_by	TEXT	nullable, REFERENCES users(id) ON DELETE SET NULL	User who created this milestone
created_at	TEXT	NOT NULL DEFAULT CURRENT_TIMESTAMP	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL DEFAULT CURRENT_TIMESTAMP	ISO 8601 timestamp, updated on every modification

Indexes:

Index	Columns	Type	Rationale
idx_milestones_target_date	target_date	INDEX	Efficient sorting and filtering by target date

Design rationale:

• INTEGER PRIMARY KEY AUTOINCREMENT: Unlike other entities in the schema that use TEXT UUIDs, milestones use auto-incrementing integer IDs. Milestones are a smaller, less sensitive entity (no information leakage concern from sequential IDs), and integer PKs provide simpler, more efficient joins for the milestone_work_items junction table. This also aligns with the task description specifying INTEGER PK.
• is_completed + completed_at pattern: The boolean is_completed provides fast filtering, while completed_at records the exact timestamp for audit/display purposes. When is_completed is set to 1, completed_at is set to the current timestamp by the application. When set back to 0, completed_at is cleared to NULL.
• color for Gantt display: Milestones appear as diamond markers on the Gantt chart. The optional color allows users to visually categorize milestones (e.g., red for critical, blue for permits, green for inspections).
• created_by nullable with ON DELETE SET NULL: Consistent with the budget tables pattern. The SET NULL FK preserves the milestone if the creating user is removed; nullable avoids constraint violations on user deletion.
• No sort_order column: Milestones are naturally sorted by target_date. Unlike budget categories which have a custom display order, milestones are temporal and their position on the timeline is determined by their date.

milestone_work_items

Junction table linking milestones to work items (many-to-many). A milestone can be associated with multiple work items that contribute to it; a work item can be part of multiple milestones.

Column	Type	Constraints	Description
milestone_id	INTEGER	NOT NULL, REFERENCES milestones(id) ON DELETE CASCADE	The milestone
work_item_id	TEXT	NOT NULL, REFERENCES work_items(id) ON DELETE CASCADE	The work item

Primary key: Composite (milestone_id, work_item_id)

Indexes:

Index	Columns	Type	Rationale
idx_milestone_work_items_work_item_id	work_item_id	INDEX	Efficient lookup of all milestones for a given work item (the composite PK index covers milestone_id lookups)

Design rationale:

• Composite primary key: No surrogate ID needed. The combination of milestone_id + work_item_id is naturally unique. Consistent with other junction tables in the schema.
• CASCADE on both sides: Deleting a milestone removes its work item associations. Deleting a work item removes it from all milestones. Neither cascades to the other entity itself.
• Index on work_item_id: The composite PK index is ordered (milestone_id, work_item_id), which is efficient for "get all work items for a milestone." The separate work_item_id index supports the reverse query: "get all milestones for a work item" (used in work item detail views and timeline rendering).

Entity Relationship Diagram (EPIC-06 additions)

+---------------------+
| milestones          |
+---------------------+
| id (PK, INTEGER AI) |
| title               |
| description         |
| target_date         |
| is_completed        |
| completed_at        |
| color               |
| created_by FK ------+---> users
| created_at          |
| updated_at          |
+---------------------+
       |
       | M:N
       |
+------+-------------------+
| milestone_work_items     |
+--------------------------+
| milestone_id FK          |
| work_item_id FK ---------+---> work_items
+--------------------------+

work_item_dependencies (updated)
+---------------------+
| predecessor_id FK   |---+
| successor_id FK     |---+---> work_items
| dependency_type     |
| lead_lag_days       |   <-- NEW (EPIC-06)
+---------------------+

Key relationships (EPIC-06):

• milestones -> milestone_work_items -> work_items: Many-to-many. A milestone can be associated with multiple work items; a work item can belong to multiple milestones. Both sides cascade on delete.
• users -> milestones: One-to-many via created_by. ON DELETE SET NULL preserves milestones when the creating user is removed.
• work_item_dependencies.lead_lag_days: New column added to the existing dependencies table. Positive values represent lag (waiting time); negative values represent lead (overlap). Used by the scheduling engine (ADR-014) when computing the critical path and auto-scheduling dates.

Migration File

server/src/db/migrations/0006_milestones.sql

-- EPIC-06: Timeline, Gantt Chart & Dependency Management
-- Creates milestones and milestone_work_items tables.
-- Adds lead_lag_days column to work_item_dependencies.

-- Milestones for tracking major project progress points
CREATE TABLE milestones (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  description TEXT,
  target_date TEXT NOT NULL,
  is_completed INTEGER NOT NULL DEFAULT 0,
  completed_at TEXT,
  color TEXT,
  created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_milestones_target_date ON milestones(target_date);

-- Junction: milestones <-> work items (M:N)
CREATE TABLE milestone_work_items (
  milestone_id INTEGER NOT NULL REFERENCES milestones(id) ON DELETE CASCADE,
  work_item_id TEXT NOT NULL REFERENCES work_items(id) ON DELETE CASCADE,
  PRIMARY KEY (milestone_id, work_item_id)
);

CREATE INDEX idx_milestone_work_items_work_item_id ON milestone_work_items(work_item_id);

-- Add lead/lag days to work item dependencies for scheduling offsets
ALTER TABLE work_item_dependencies ADD COLUMN lead_lag_days INTEGER NOT NULL DEFAULT 0;

-- Rollback:
-- ALTER TABLE work_item_dependencies DROP COLUMN lead_lag_days;
-- DROP INDEX IF EXISTS idx_milestone_work_items_work_item_id;
-- DROP TABLE IF EXISTS milestone_work_items;
-- DROP INDEX IF EXISTS idx_milestones_target_date;
-- DROP TABLE IF EXISTS milestones;

---

EPIC-07: Actual Dates & Status Simplification

EPIC-07 adds actual start/end date tracking to work items and simplifies the status enum by removing the blocked value.

Migration File (0008 -- Actual Dates & Status)

server/src/db/migrations/0008_actual_dates_and_status.sql

Changes:

1. Adds actual_start_date (nullable TEXT) to work_items for tracking when work actually started
2. Adds actual_end_date (nullable TEXT) to work_items for tracking when work actually finished
3. Migrates existing blocked status rows to not_started (soft migration -- SQLite cannot ALTER CHECK constraints, so the application layer enforces the new three-value enum)

-- Migration 0008: Add actual start/end date columns and simplify work item status enum
--
-- Changes:
--   1. Add actual_start_date column to work_items (nullable TEXT, ISO date)
--   2. Add actual_end_date column to work_items (nullable TEXT, ISO date)
--   3. Migrate existing 'blocked' status rows to 'not_started'
--      (Note: SQLite does not support ALTER COLUMN or DROP CHECK, so we use a
--       soft migration — existing rows with 'blocked' are updated to 'not_started'.
--       The application layer enforces the new three-value enum.)
--
-- ROLLBACK:
--   ALTER TABLE work_items DROP COLUMN actual_start_date;
--   ALTER TABLE work_items DROP COLUMN actual_end_date;
--   (blocked status rollback is not reversible without original data)

ALTER TABLE work_items ADD COLUMN actual_start_date TEXT;
ALTER TABLE work_items ADD COLUMN actual_end_date TEXT;

-- Migrate any existing 'blocked' rows to 'not_started'
UPDATE work_items SET status = 'not_started' WHERE status = 'blocked';

---

EPIC-08: Paperless-ngx Document Integration

EPIC-08 adds document linking support, allowing users to reference Paperless-ngx documents from work items, household items, and invoices. See ADR-015 for the architectural rationale.

document_links

Polymorphic junction table linking Paperless-ngx documents to various Cornerstone entities. Uses entity_type discriminator + entity_id pattern instead of separate junction tables per entity type.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
entity_type	TEXT	NOT NULL CHECK(entity_type IN ('work_item', 'household_item', 'invoice'))	Type of the linked Cornerstone entity
entity_id	TEXT	NOT NULL	ID of the linked entity (work item, household item, or invoice)
paperless_document_id	INTEGER	NOT NULL	Paperless-ngx document ID (integer, from the external system)
created_by	TEXT	nullable, REFERENCES users(id) ON DELETE SET NULL	User who created the link
created_at	TEXT	NOT NULL	ISO 8601 timestamp, set on insert

Indexes:

Index	Columns	Type	Rationale
idx_document_links_unique	entity_type, entity_id, paperless_document_id	UNIQUE	Prevents duplicate links (same document linked twice to same entity)
idx_document_links_entity	entity_type, entity_id	INDEX	Fast lookup of all documents for a given entity
idx_document_links_paperless_doc	paperless_document_id	INDEX	Reverse lookup: find all entities linked to a given document

Design rationale:

• Polymorphic pattern: A single table with entity_type discriminator is chosen over three separate junction tables (work_item_documents, household_item_documents, invoice_documents) to reduce schema duplication and simplify the linking CRUD logic. See ADR-015 for the full trade-off analysis.
• No foreign key on entity_id: SQLite foreign keys cannot reference different tables conditionally based on another column's value. Referential integrity on the entity side is enforced at the application layer (entity existence check on insert; cascade-delete of links when an entity is deleted).
• INTEGER for paperless_document_id: Paperless-ngx uses auto-incrementing integer IDs for documents, not UUIDs.
• No updated_at: Document links are immutable once created. They can only be created or deleted, never modified.
• created_by with ON DELETE SET NULL: Preserves link history even if the user who created the link is later deactivated/deleted.

Entity type values:

Value	References	Description
work_item	work_items.id	Construction work item
household_item	household_items.id (EPIC-04)	Household item / furniture
invoice	invoices.id	Vendor invoice

Note: The household_item entity type is defined in the schema now but the household_items table does not exist until EPIC-04 is implemented. Application-layer validation should only allow household_item links when the household items feature is available.

Migration File (0009 -- Document Links)

server/src/db/migrations/0009_document_links.sql

Changes:

1. Creates the document_links table with polymorphic entity reference pattern
2. Creates composite unique index to prevent duplicate links
3. Creates entity lookup index and reverse lookup index

-- Migration 0009: Create document_links table for Paperless-ngx integration
--
-- EPIC-08: Paperless-ngx Document Integration
--
-- Creates a single polymorphic table for linking Paperless-ngx documents to
-- various entities (work items, household items, invoices). Uses entity_type
-- discriminator + entity_id pattern instead of separate junction tables.
--
-- See ADR-015 for design rationale.
--
-- ROLLBACK:
--   DROP INDEX IF EXISTS idx_document_links_entity;
--   DROP INDEX IF EXISTS idx_document_links_paperless_doc;
--   DROP TABLE IF EXISTS document_links;

CREATE TABLE document_links (
  id TEXT PRIMARY KEY,
  entity_type TEXT NOT NULL CHECK(entity_type IN ('work_item', 'household_item', 'invoice')),
  entity_id TEXT NOT NULL,
  paperless_document_id INTEGER NOT NULL,
  created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL
);

-- Composite unique constraint: prevent duplicate links
CREATE UNIQUE INDEX idx_document_links_unique
  ON document_links (entity_type, entity_id, paperless_document_id);

-- Fast lookup: find all documents for a given entity
CREATE INDEX idx_document_links_entity
  ON document_links (entity_type, entity_id);

-- Reverse lookup: find all entities linked to a given Paperless-ngx document
CREATE INDEX idx_document_links_paperless_doc
  ON document_links (paperless_document_id);

---

EPIC-04: Household Items & Furniture Management

Household items and furniture purchases tracked alongside the construction project. A distinct entity from work items (see ADR-016), with its own purchase status workflow, category system, delivery tracking, and budget integration.

household_item_categories

Lookup table for household item categories. Replaces the previous hard-coded CHECK constraint enum on household_items.category. Categories are user-manageable via CRUD API endpoints.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	Stable slug ID (e.g., hic-furniture)
name	TEXT	UNIQUE NOT NULL	Display name (1-100 chars)
color	TEXT	nullable	Hex color code (#RRGGBB) for UI
sort_order	INTEGER	NOT NULL DEFAULT 0	Display ordering (ascending)
created_at	TEXT	NOT NULL	ISO 8601 timestamp
updated_at	TEXT	NOT NULL	ISO 8601 timestamp

Indexes:

Index	Columns	Rationale
idx_household_item_categories_sort_order	sort_order	Ordered category listing
sqlite_autoindex_household_item_categories	name	Enforced by UNIQUE constraint

Seeded values (migration 0016):

ID	Name	Color	Sort Order	Description
hic-furniture	Furniture	#8B5CF6	0	Tables, chairs, beds, sofas, shelving, etc.
hic-appliances	Appliances	#3B82F6	1	Kitchen appliances, HVAC, washer/dryer, etc.
hic-fixtures	Fixtures	#06B6D4	2	Faucets, lighting, door hardware, etc.
hic-decor	Decor	#EC4899	3	Art, rugs, curtains, decorative accessories
hic-electronics	Electronics	#F59E0B	4	TVs, speakers, networking equipment, etc.
hic-outdoor	Outdoor	#22C55E	5	Patio furniture, garden tools, outdoor equipment
hic-storage	Storage	#F97316	6	Closet systems, shelving units, bins, etc.
hic-other	Other	#6B7280	7	Items that do not fit other categories

Design rationale:

• Lookup table instead of CHECK constraint: Allows users to add, rename, recolor, and reorder categories without database migrations. The original 8 enum values are seeded as default rows.
• ON DELETE RESTRICT on FK: Categories cannot be deleted while household items reference them. The API returns 409 CATEGORY_IN_USE with a count of referencing items.
• name UNIQUE: Prevents duplicate category names. Uniqueness is checked case-insensitively at the application layer.
• Slug-style IDs: Seeded categories use hic- prefixed slugs (e.g., hic-furniture). User-created categories use UUIDs.

household_items

Main entity table for household items and furniture.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID v4
name	TEXT	NOT NULL	Item name (1-500 chars)
description	TEXT	nullable	Detailed description/specs
category_id	TEXT	NOT NULL REFERENCES household_item_categories(id) ON DELETE RESTRICT	FK to household item category
status	TEXT	NOT NULL DEFAULT 'planned' CHECK(status IN ('planned','purchased','scheduled','arrived'))	Lifecycle status
vendor_id	TEXT	FK -> vendors(id) ON DELETE SET NULL	Supplier/vendor
url	TEXT	nullable	Product URL
room	TEXT	nullable	Delivery location/room
quantity	INTEGER	NOT NULL DEFAULT 1 CHECK(quantity >= 1)	Number of items
order_date	TEXT	nullable	Date ordered (YYYY-MM-DD)
earliest_delivery_date	TEXT	nullable	User-editable earliest delivery constraint (YYYY-MM-DD); used by scheduler to compute target_delivery_date
latest_delivery_date	TEXT	nullable	User-editable latest delivery constraint (YYYY-MM-DD); used by scheduler as hard deadline
target_delivery_date	TEXT	nullable	Computed target delivery date (YYYY-MM-DD); set by scheduling engine to balance dependencies and constraints
actual_delivery_date	TEXT	nullable	Actual delivery (YYYY-MM-DD)
is_late	INTEGER	NOT NULL DEFAULT 0	Flag (0=on-time, 1=late); set when actual_delivery_date exceeds target_delivery_date
created_by	TEXT	FK -> users(id) ON DELETE SET NULL	Creating user
created_at	TEXT	NOT NULL	ISO 8601 timestamp
updated_at	TEXT	NOT NULL	ISO 8601 timestamp

Indexes:

Index	Columns	Rationale
idx_household_items_category_id	category_id	Filter by category (FK join)
idx_household_items_status	status	Filter by purchase status
idx_household_items_room	room	Filter by delivery room
idx_household_items_vendor_id	vendor_id	Join to vendors
idx_household_items_created_at	created_at	Sort by creation time
idx_household_items_target_delivery	target_delivery_date	Sort by target delivery date (Gantt timeline view)

Status values:

Value	Description
planned	Item identified but not yet purchased
purchased	Order placed with vendor/supplier
scheduled	Delivery scheduled, en route or awaiting delivery
arrived	Item received at the delivery location

Design rationale:

• category_id FK to household_item_categories: Replaced the previous category CHECK constraint enum (migration 0016). Categories are now user-manageable via CRUD API, with ON DELETE RESTRICT preventing deletion of in-use categories.
• room as free text: Rooms vary greatly between homes (e.g., "Kitchen", "Master Bedroom", "Garage", "Guest Bathroom 2"). An enum would be too restrictive.
• quantity column: Allows tracking multiples (e.g., "6x dining chairs") without creating separate rows. Defaults to 1.
• url column: Product page links for reference during purchasing.
• vendor_id reuses EPIC-05 vendors table: Vendors serve as both contractors (work items) and suppliers (household items).
• No status transition enforcement: Users can set any valid status at any time to correct mistakes. The application does not enforce a linear workflow.
• Delivery date redesign (Migration 0015): expected_delivery_date was replaced with a three-part model:
  • earliest_delivery_date and latest_delivery_date are user-editable delivery constraints (e.g., "I want this by end of April, but not before March 15")
  • target_delivery_date is computed by the scheduling engine to balance dependency chains with user constraints
  • is_late flag indicates if actual_delivery_date exceeded target_delivery_date
  • This enables the Gantt timeline to visualize household item delivery dates and detect late items alongside work items and milestones

household_item_tags

Many-to-many junction between household items and the shared tags table (same tags used by work items).

Column	Type	Constraints	Description
household_item_id	TEXT	NOT NULL FK -> household_items(id) ON DELETE CASCADE	Household item
tag_id	TEXT	NOT NULL FK -> tags(id) ON DELETE CASCADE	Tag

Primary key: (household_item_id, tag_id)

Indexes:

Index	Columns	Rationale
idx_household_item_tags_tag_id	tag_id	Reverse lookup: find items by tag

household_item_notes

Notes/comments on household items. Follows the work_item_notes pattern.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID v4
household_item_id	TEXT	NOT NULL FK -> household_items(id) ON DELETE CASCADE	Parent item
content	TEXT	NOT NULL	Note text
created_by	TEXT	FK -> users(id) ON DELETE SET NULL	Author
created_at	TEXT	NOT NULL	ISO 8601
updated_at	TEXT	NOT NULL	ISO 8601

Indexes:

Index	Columns	Rationale
idx_household_item_notes_household_item_id	household_item_id	Fetch notes for a specific item

household_item_budgets

Budget lines for household items. Mirrors work_item_budgets exactly.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID v4
household_item_id	TEXT	NOT NULL FK -> household_items(id) ON DELETE CASCADE	Parent item
description	TEXT	nullable	Line description
planned_amount	REAL	NOT NULL DEFAULT 0 CHECK(planned_amount >= 0)	Planned cost
confidence	TEXT	NOT NULL DEFAULT 'own_estimate' CHECK(confidence IN ('own_estimate','professional_estimate','quote','invoice'))	Confidence level
budget_category_id	TEXT	FK -> budget_categories(id) ON DELETE SET NULL	Budget category
budget_source_id	TEXT	FK -> budget_sources(id) ON DELETE SET NULL	Financing source
vendor_id	TEXT	FK -> vendors(id) ON DELETE SET NULL	Vendor
created_by	TEXT	FK -> users(id) ON DELETE SET NULL	Creating user
created_at	TEXT	NOT NULL	ISO 8601
updated_at	TEXT	NOT NULL	ISO 8601

Indexes:

Index	Columns	Rationale
idx_household_item_budgets_household_item_id	household_item_id	Fetch budgets for an item
idx_household_item_budgets_vendor_id	vendor_id	Join to vendors
idx_household_item_budgets_budget_category_id	budget_category_id	Join to budget categories
idx_household_item_budgets_budget_source_id	budget_source_id	Join to budget sources

Design rationale:

• Same structure as work_item_budgets for consistency. Budget lines support confidence levels, categories, sources, and vendor references.
• Household item budget lines do not link to invoices. Invoice tracking is done through the vendor system (EPIC-05). The API response includes actualCost, actualCostPaid, invoiceCount, and invoices fields for shape consistency, but they are always zero/empty.
• Household item budget lines contribute to the project-level budget overview alongside work item budget lines.

household_item_work_items (DROPPED -- Migration 0012)

This table was dropped in migration 0012. The household item-to-work item relationship is now captured through household_item_deps, which supports full dependency types (FS/SS/FF/SF), lead/lag days, and milestone dependencies.

Previously, this was a simple many-to-many junction table linking household items to work items. Existing rows were migrated to household_item_deps as finish_to_start dependencies with 0-day lag.

household_item_deps

Dependency table linking household items to work items and milestones for delivery scheduling. Mirrors the work_item_dependencies structure but uses a polymorphic predecessor reference (work items or milestones). The scheduling engine uses these dependencies to compute earliest_delivery_date and latest_delivery_date on household items.

Column	Type	Constraints	Description
household_item_id	TEXT	NOT NULL FK -> household_items(id) ON DELETE CASCADE	Household item (the dependent/successor)
predecessor_type	TEXT	NOT NULL CHECK(predecessor_type IN ('work_item', 'milestone'))	Type of predecessor entity
predecessor_id	TEXT	NOT NULL	ID of the predecessor (work item UUID or milestone integer ID)
dependency_type	TEXT	NOT NULL DEFAULT 'finish_to_start' CHECK(dependency_type IN ('finish_to_start','start_to_start','finish_to_finish','start_to_finish'))	Scheduling dependency type
lead_lag_days	INTEGER	NOT NULL DEFAULT 0	Lead (negative) or lag (positive) days offset

Primary key: (household_item_id, predecessor_type, predecessor_id)

Indexes:

Index	Columns	Rationale
idx_hi_deps_predecessor	predecessor_type, predecessor_id	Reverse lookup: find household items by predecessor

Dependency types: Same as work_item_dependencies (see EPIC-03 section).

Design rationale:

• Polymorphic predecessor reference: Household items can depend on both work items and milestones. Using predecessor_type + predecessor_id avoids two separate junction tables. No DB-level FK on predecessor_id because SQLite cannot conditionally reference different tables.
• Full dependency types (FS/SS/FF/SF): Unlike the previous household_item_work_items junction, this table supports all four standard project management dependency types, enabling precise scheduling (e.g., "delivery must start when kitchen installation starts" = SS).
• lead_lag_days: Allows time offsets between the predecessor's date and the household item delivery window (e.g., +3 = order 3 days after work item finishes).
• Cascade on household_item_id: Deleting a household item removes all its dependencies.
• No cascade on predecessor: predecessor_id has no DB-level FK. Application-layer cleanup is required when work items or milestones are deleted (delete orphaned household_item_deps rows where predecessor_type and predecessor_id match the deleted entity).
• Three-column composite PK: A household item can depend on the same predecessor only once. The triple (household_item_id, predecessor_type, predecessor_id) is naturally unique.

household_item_subsidies

Links household items to subsidy programs (M:N). Follows the work_item_subsidies pattern.

Column	Type	Constraints	Description
household_item_id	TEXT	NOT NULL FK -> household_items(id) ON DELETE CASCADE	Household item
subsidy_program_id	TEXT	NOT NULL FK -> subsidy_programs(id) ON DELETE CASCADE	Subsidy

Primary key: (household_item_id, subsidy_program_id)

Indexes:

Index	Columns	Rationale
idx_household_item_subsidies_subsidy_program_id	subsidy_program_id	Reverse lookup: find items by subsidy

Migration File (0010 -- Household Items)

server/src/db/migrations/0010_household_items.sql

Changes:

1. Creates the household_items table with category/status CHECK constraints (category CHECK superseded by migration 0016 which replaces it with category_id FK)
2. Creates household_item_tags junction table (reuses shared tags table)
3. Creates household_item_notes table
4. Creates household_item_budgets table (mirrors work_item_budgets)
5. Creates household_item_work_items junction table (superseded by migration 0012)
6. Creates household_item_subsidies junction table

-- Migration 0010: Create household items tables
--
-- EPIC-04: Household Items & Furniture Management
--
-- Creates the household_items entity and all supporting tables:
--   - household_item_tags (M:N with shared tags table)
--   - household_item_notes (comments/notes)
--   - household_item_budgets (budget lines, mirrors work_item_budgets)
--   - household_item_work_items (M:N link to work items for coordination)
--   - household_item_subsidies (M:N with subsidy programs)
--
-- See ADR-016 for design rationale.
--
-- ROLLBACK:
--   DROP TABLE IF EXISTS household_item_subsidies;
--   DROP TABLE IF EXISTS household_item_work_items;
--   DROP TABLE IF EXISTS household_item_budgets;
--   DROP TABLE IF EXISTS household_item_notes;
--   DROP TABLE IF EXISTS household_item_tags;
--   DROP TABLE IF EXISTS household_items;

-- ── household_items ─────────────────────────────────────────────────────────

CREATE TABLE household_items (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  category TEXT NOT NULL DEFAULT 'other'
    CHECK(category IN ('furniture', 'appliances', 'fixtures', 'decor', 'electronics', 'outdoor', 'storage', 'other')),
  status TEXT NOT NULL DEFAULT 'planned'
    CHECK(status IN ('planned', 'purchased', 'scheduled', 'arrived')),
  vendor_id TEXT REFERENCES vendors(id) ON DELETE SET NULL,
  url TEXT,
  room TEXT,
  quantity INTEGER NOT NULL DEFAULT 1 CHECK(quantity >= 1),
  order_date TEXT,
  expected_delivery_date TEXT,
  actual_delivery_date TEXT,
  created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_household_items_category ON household_items(category);
CREATE INDEX idx_household_items_status ON household_items(status);
CREATE INDEX idx_household_items_room ON household_items(room);
CREATE INDEX idx_household_items_vendor_id ON household_items(vendor_id);
CREATE INDEX idx_household_items_created_at ON household_items(created_at);

-- ── household_item_tags ─────────────────────────────────────────────────────

CREATE TABLE household_item_tags (
  household_item_id TEXT NOT NULL REFERENCES household_items(id) ON DELETE CASCADE,
  tag_id TEXT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (household_item_id, tag_id)
);

CREATE INDEX idx_household_item_tags_tag_id ON household_item_tags(tag_id);

-- ── household_item_notes ────────────────────────────────────────────────────

CREATE TABLE household_item_notes (
  id TEXT PRIMARY KEY,
  household_item_id TEXT NOT NULL REFERENCES household_items(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_household_item_notes_household_item_id ON household_item_notes(household_item_id);

-- ── household_item_budgets ──────────────────────────────────────────────────

CREATE TABLE household_item_budgets (
  id TEXT PRIMARY KEY,
  household_item_id TEXT NOT NULL REFERENCES household_items(id) ON DELETE CASCADE,
  description TEXT,
  planned_amount REAL NOT NULL DEFAULT 0 CHECK(planned_amount >= 0),
  confidence TEXT NOT NULL DEFAULT 'own_estimate'
    CHECK(confidence IN ('own_estimate', 'professional_estimate', 'quote', 'invoice')),
  budget_category_id TEXT REFERENCES budget_categories(id) ON DELETE SET NULL,
  budget_source_id TEXT REFERENCES budget_sources(id) ON DELETE SET NULL,
  vendor_id TEXT REFERENCES vendors(id) ON DELETE SET NULL,
  created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_household_item_budgets_household_item_id ON household_item_budgets(household_item_id);
CREATE INDEX idx_household_item_budgets_vendor_id ON household_item_budgets(vendor_id);
CREATE INDEX idx_household_item_budgets_budget_category_id ON household_item_budgets(budget_category_id);
CREATE INDEX idx_household_item_budgets_budget_source_id ON household_item_budgets(budget_source_id);

-- ── household_item_work_items ───────────────────────────────────────────────

CREATE TABLE household_item_work_items (
  household_item_id TEXT NOT NULL REFERENCES household_items(id) ON DELETE CASCADE,
  work_item_id TEXT NOT NULL REFERENCES work_items(id) ON DELETE CASCADE,
  PRIMARY KEY (household_item_id, work_item_id)
);

CREATE INDEX idx_household_item_work_items_work_item_id ON household_item_work_items(work_item_id);

-- ── household_item_subsidies ────────────────────────────────────────────────

CREATE TABLE household_item_subsidies (
  household_item_id TEXT NOT NULL REFERENCES household_items(id) ON DELETE CASCADE,
  subsidy_program_id TEXT NOT NULL REFERENCES subsidy_programs(id) ON DELETE CASCADE,
  PRIMARY KEY (household_item_id, subsidy_program_id)
);

CREATE INDEX idx_household_item_subsidies_subsidy_program_id ON household_item_subsidies(subsidy_program_id);

Migration File (0012 -- Household Item Dependencies)

server/src/db/migrations/0012_household_item_deps.sql

Changes:

1. Adds earliest_delivery_date and latest_delivery_date columns to household_items
2. Creates household_item_deps table (polymorphic predecessor references to work items and milestones)
3. Migrates existing household_item_work_items rows as finish_to_start / 0-lag dependencies
4. Drops household_item_work_items junction table

-- Migration 0012: Replace household_item_work_items with household_item_deps dependency model
-- Adds CPM delivery date columns, creates deps table, migrates data, drops old table.

-- 1. Add computed delivery date columns to household_items
ALTER TABLE household_items ADD COLUMN earliest_delivery_date TEXT;
ALTER TABLE household_items ADD COLUMN latest_delivery_date TEXT;

-- 2. Create household_item_deps table
CREATE TABLE household_item_deps (
  household_item_id TEXT NOT NULL
    REFERENCES household_items(id) ON DELETE CASCADE,
  predecessor_type  TEXT NOT NULL CHECK (predecessor_type IN ('work_item', 'milestone')),
  predecessor_id    TEXT NOT NULL,
  dependency_type   TEXT NOT NULL DEFAULT 'finish_to_start'
    CHECK (dependency_type IN ('finish_to_start','start_to_start','finish_to_finish','start_to_finish')),
  lead_lag_days     INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (household_item_id, predecessor_type, predecessor_id)
);

CREATE INDEX idx_hi_deps_predecessor ON household_item_deps(predecessor_type, predecessor_id);

-- 3. Migrate existing household_item_work_items rows as FS/0-lag work_item deps
INSERT INTO household_item_deps
  (household_item_id, predecessor_type, predecessor_id, dependency_type, lead_lag_days)
SELECT
  household_item_id,
  'work_item',
  work_item_id,
  'finish_to_start',
  0
FROM household_item_work_items;

-- 4. Drop old junction table
DROP TABLE household_item_work_items;

-- ROLLBACK:
--   ALTER TABLE household_items DROP COLUMN earliest_delivery_date;
--   ALTER TABLE household_items DROP COLUMN latest_delivery_date;
--   DROP TABLE IF EXISTS household_item_deps;
--   CREATE TABLE household_item_work_items (
--     household_item_id TEXT NOT NULL REFERENCES household_items(id) ON DELETE CASCADE,
--     work_item_id TEXT NOT NULL REFERENCES work_items(id) ON DELETE CASCADE,
--     PRIMARY KEY (household_item_id, work_item_id)
--   );
--   CREATE INDEX idx_household_item_work_items_work_item_id ON household_item_work_items(work_item_id);

Migration File (0015 -- Household Item Delivery Date Redesign)

server/src/db/migrations/0015_hi_delivery_date_redesign.sql

Changes:

1. Adds target_delivery_date column (computed by scheduler from constraints and dependencies)
2. Adds is_late boolean flag (set when actual delivery exceeds target)
3. Migrates legacy expected_delivery_date values to earliest_delivery_date
4. Rebuilds table to remove now-obsolete expected_delivery_date column

Rationale:

The single expected_delivery_date field was insufficient for scheduling household item delivery alongside work items and milestones. The new three-part model:

• earliest_delivery_date & latest_delivery_date: User-editable constraints (e.g., "I want this by May 1, but not before April 15")
• target_delivery_date: Scheduler computes this to honor dependencies and user constraints (e.g., "Installation work finishes April 30, so deliver May 2")
• is_late: Flag set when actual delivery > target delivery, visible in Gantt chart

Legacy expected_delivery_date values are migrated to earliest_delivery_date to preserve user intent during the upgrade.

---

EPIC-09: Dashboard & Project Health Center

user_preferences

Stores per-user UI preferences as key-value pairs. Used for dashboard customization (e.g., hidden cards, theme selection) and any future user-specific settings.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT	Auto-incrementing integer ID
user_id	TEXT	NOT NULL REFERENCES users(id) ON DELETE CASCADE	Owning user
key	TEXT	NOT NULL	Preference key (e.g., dashboard.hiddenCards)
value	TEXT	NOT NULL	Preference value (JSON-encoded for complex types)
created_at	TEXT	NOT NULL	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL	ISO 8601 timestamp, updated on every modification

Constraints:

Constraint	Type	Columns	Description
sqlite_autoindex_user_preferences_1	UNIQUE	user_id, key	Each user can have at most one value per key

Indexes:

Index	Columns	Type	Rationale
idx_user_preferences_user_id	user_id	INDEX	Fast lookup of all preferences for a specific user

Design rationale:

• INTEGER PRIMARY KEY (AUTOINCREMENT): Unlike most other tables that use TEXT UUIDs, preferences are internal records that don't need globally unique identifiers or appear in URLs. Auto-increment is simpler and more efficient for a high-write key-value table.
• Key-value design: A flexible schema that accommodates any preference without schema migrations. New preference keys can be added at the application layer without database changes.
• TEXT value: All values are stored as TEXT. Complex values (arrays, objects) are JSON-encoded by the application layer. This avoids the need for multiple typed columns.
• UNIQUE(user_id, key): Enforces at most one value per preference key per user. Upsert operations use INSERT ... ON CONFLICT ... DO UPDATE.
• ON DELETE CASCADE: When a user is deleted, all their preferences are automatically cleaned up.
• No deactivated_at: Preferences are hard-deleted when removed. There is no audit trail for preference changes.

Migration File (0016 -- Household Item Categories)

server/src/db/migrations/0016_household_item_categories.sql

Changes:

1. Creates the household_item_categories lookup table
2. Seeds the 8 original enum values as default categories (hic-furniture, hic-appliances, etc.)
3. Adds category_id FK column to household_items
4. Backfills category_id from the old category string values
5. Drops the old category column and its index (idx_household_items_category)
6. Seeds bc-household-items budget category in budget_categories
7. Migrates all existing household_item_budgets to use bc-household-items as their budget_category_id
8. Creates indexes on household_item_categories(sort_order) and household_items(category_id)

-- EPIC-09: Create household_item_categories lookup table
-- Story #509: Unified Tags & Categories Management Page
-- Migrates household_items.category from hard-coded enum to foreign key

-- Create household_item_categories lookup table
CREATE TABLE household_item_categories (
  id TEXT PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  color TEXT,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

-- Seed the 8 original enum values
INSERT INTO household_item_categories (id, name, color, sort_order, created_at, updated_at) VALUES
  ('hic-furniture',    'Furniture',    '#8B5CF6', 0, datetime('now'), datetime('now')),
  ('hic-appliances',   'Appliances',   '#3B82F6', 1, datetime('now'), datetime('now')),
  ('hic-fixtures',     'Fixtures',     '#06B6D4', 2, datetime('now'), datetime('now')),
  ('hic-decor',        'Decor',        '#EC4899', 3, datetime('now'), datetime('now')),
  ('hic-electronics',  'Electronics',  '#F59E0B', 4, datetime('now'), datetime('now')),
  ('hic-outdoor',      'Outdoor',      '#22C55E', 5, datetime('now'), datetime('now')),
  ('hic-storage',      'Storage',      '#F97316', 6, datetime('now'), datetime('now')),
  ('hic-other',        'Other',        '#6B7280', 7, datetime('now'), datetime('now'));

-- Add new column category_id (nullable initially for migration)
ALTER TABLE household_items ADD COLUMN category_id TEXT REFERENCES household_item_categories(id) ON DELETE RESTRICT;

-- Backfill category_id from category string value
UPDATE household_items SET category_id = 'hic-furniture'   WHERE category = 'furniture';
UPDATE household_items SET category_id = 'hic-appliances'  WHERE category = 'appliances';
UPDATE household_items SET category_id = 'hic-fixtures'    WHERE category = 'fixtures';
UPDATE household_items SET category_id = 'hic-decor'       WHERE category = 'decor';
UPDATE household_items SET category_id = 'hic-electronics' WHERE category = 'electronics';
UPDATE household_items SET category_id = 'hic-outdoor'     WHERE category = 'outdoor';
UPDATE household_items SET category_id = 'hic-storage'     WHERE category = 'storage';
UPDATE household_items SET category_id = 'hic-other'       WHERE category = 'other';

-- Set default for category_id (backfill any nulls to 'hic-other')
UPDATE household_items SET category_id = 'hic-other' WHERE category_id IS NULL;

-- Drop old category column (SQLite 3.35.0+ supports DROP COLUMN)
-- Must drop the index first since SQLite cannot drop a column that has an index
DROP INDEX IF EXISTS idx_household_items_category;
ALTER TABLE household_items DROP COLUMN category;

-- Seed "Household Items" budget category
INSERT INTO budget_categories (id, name, description, color, sort_order, created_at, updated_at) VALUES
  ('bc-household-items', 'Household Items', 'Furniture, appliances, and other household purchases', '#8B5CF6', 10, datetime('now'), datetime('now'));

-- Migrate ALL existing household_item_budgets to bc-household-items
UPDATE household_item_budgets SET budget_category_id = 'bc-household-items';

-- Indexes
CREATE INDEX idx_household_item_categories_sort_order ON household_item_categories(sort_order);
CREATE INDEX idx_household_items_category_id ON household_items(category_id);

Migration File (0018 -- User Preferences)

server/src/db/migrations/0018_user_preferences.sql

-- EPIC-09: Dashboard & Project Health Center
-- Creates the user_preferences table for storing per-user UI preferences
-- (e.g., hidden dashboard cards, theme selection).

CREATE TABLE user_preferences (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  key TEXT NOT NULL,
  value TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  UNIQUE(user_id, key)
);

CREATE INDEX idx_user_preferences_user_id ON user_preferences(user_id);

---

EPIC-15: Budget-Line Invoice Linking Rework

EPIC-15 replaces the 1:1 FK model between invoices and budget lines with a many-to-many junction table (invoice_budget_lines). This enables a single invoice to be attributed across multiple budget categories with per-link itemized amounts. See ADR-018 for the full architectural rationale.

invoice_budget_lines

Junction table linking invoices to budget lines (work item or household item) with per-link itemized amounts. Each budget line can be linked to at most one invoice (exclusive linking). An invoice can have many budget line links.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
invoice_id	TEXT	NOT NULL, REFERENCES invoices(id) ON DELETE CASCADE	The parent invoice
work_item_budget_id	TEXT	nullable, REFERENCES work_item_budgets(id) ON DELETE CASCADE	Link to a work item budget line (mutually exclusive with HI)
household_item_budget_id	TEXT	nullable, REFERENCES household_item_budgets(id) ON DELETE CASCADE	Link to a household item budget line (mutually exclusive with WI)
itemized_amount	REAL	NOT NULL, CHECK(itemized_amount > 0)	Dollar amount attributed to this budget line from this invoice
created_at	TEXT	NOT NULL	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL	ISO 8601 timestamp, updated on every modification

CHECK constraint (XOR): Exactly one of work_item_budget_id or household_item_budget_id must be non-null:

CHECK (
  (work_item_budget_id IS NOT NULL AND household_item_budget_id IS NULL) OR
  (work_item_budget_id IS NULL AND household_item_budget_id IS NOT NULL)
)

Indexes:

Index	Columns	Type	Rationale
idx_invoice_budget_lines_invoice_id	invoice_id	INDEX	Fast lookup of all budget lines for an invoice
idx_invoice_budget_lines_work_item_budget_id	work_item_budget_id	INDEX	Fast lookup by WI budget line; supports actualCost aggregation
idx_invoice_budget_lines_household_item_budget_id	household_item_budget_id	INDEX	Fast lookup by HI budget line; supports actualCost aggregation
idx_invoice_budget_lines_unique_wib	work_item_budget_id	UNIQUE (partial: WHERE work_item_budget_id IS NOT NULL)	Exclusive linking: a WI budget line can appear in at most one invoice
idx_invoice_budget_lines_unique_hib	household_item_budget_id	UNIQUE (partial: WHERE household_item_budget_id IS NOT NULL)	Exclusive linking: a HI budget line can appear in at most one invoice

Design rationale:

• Surrogate id PK (TEXT UUID): Unlike other junction tables in the schema that use composite PKs, this table uses a surrogate ID. The junction rows carry meaningful data (itemized_amount) and need to be individually addressable for CRUD operations (PUT to update amount, DELETE to unlink). A composite PK is not viable because one of the budget FK columns is always NULL (XOR constraint).
• ON DELETE CASCADE from invoice_id: When an invoice is deleted, all its budget line links are removed. The links exist only because of the invoice.
• ON DELETE CASCADE from budget line FKs: When a budget line is deleted, the corresponding junction row is automatically removed. This avoids orphaned rows and is compatible with the XOR CHECK constraint (SET NULL would violate the XOR CHECK by nullifying the only non-null FK). See ADR-018 and Bug #611 for the rationale behind using CASCADE instead of SET NULL.
• Partial UNIQUE indexes for exclusive linking: SQLite supports partial indexes (since 3.8.0). This is the most robust approach to enforce that each budget line appears in at most one invoice, without relying on application-level enforcement alone.
• itemized_amount CHECK > 0: A zero or negative itemized amount is meaningless. The application layer additionally enforces that the sum of all itemized amounts for an invoice does not exceed the invoice's amount.
• XOR CHECK constraint: Ensures data integrity at the database level -- every junction row links to exactly one type of budget line. This is preferred over a polymorphic discriminator pattern because we have exactly two target tables with stable schemas, and typed FK columns provide stronger referential integrity.

Changes to invoices

The work_item_budget_id and household_item_budget_id columns are removed from the invoices table. Invoice-to-budget-line relationships are now exclusively managed through invoice_budget_lines.

Updated invoices table columns:

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
vendor_id	TEXT	NOT NULL, REFERENCES vendors(id) ON DELETE CASCADE	The vendor who issued this invoice
invoice_number	TEXT	nullable	Vendor's invoice number/reference
amount	REAL	NOT NULL	Invoice amount in the project's currency
date	TEXT	NOT NULL	Invoice date (ISO 8601 date)
due_date	TEXT	nullable	Payment due date (ISO 8601 date)
status	TEXT	NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','paid','claimed'))	Payment status
notes	TEXT	nullable	Free-form notes about this invoice
created_by	TEXT	nullable, REFERENCES users(id) ON DELETE SET NULL	User who recorded this invoice
created_at	TEXT	NOT NULL DEFAULT (datetime('now'))	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL DEFAULT (datetime('now'))	ISO 8601 timestamp, updated on every modification

Updated indexes (removed idx_invoices_work_item_budget_id and idx_invoices_household_item_budget_id):

Index	Columns	Type	Rationale
idx_invoices_vendor_id	vendor_id	INDEX	Fast lookup of vendor's invoices
idx_invoices_status	status	INDEX	Filter by payment status
idx_invoices_date	date	INDEX	Sort/filter by date

Entity Relationship Diagram (EPIC-15)

+---------------------+
| invoices            |
+---------------------+
| id (PK, UUID)       |
| vendor_id FK --------+---> vendors
| invoice_number      |
| amount              |
| date                |
| due_date            |
| status              |
| notes               |
| created_by FK       |
+---------------------+
       |
       | 1:N
       |
+------+---------------------+
| invoice_budget_lines       |
+----------------------------+
| id (PK, UUID)              |
| invoice_id FK              |
| work_item_budget_id FK ----+---> work_item_budgets (UNIQUE, exclusive)
| household_item_budget_id --+---> household_item_budgets (UNIQUE, exclusive)
| itemized_amount            |
| created_at                 |
| updated_at                 |
+----------------------------+
  XOR: exactly one budget FK is non-null

Key relationships (EPIC-15):

• invoices -> invoice_budget_lines: One-to-many. An invoice can have multiple budget line links. Links cascade-delete with their parent invoice.
• invoice_budget_lines -> work_item_budgets: Many-to-one (optional, exclusive). Each junction row can reference one WI budget line. ON DELETE SET NULL preserves the junction row if the budget line is removed. The partial unique index ensures each WI budget line appears in at most one junction row.
• invoice_budget_lines -> household_item_budgets: Many-to-one (optional, exclusive). Same pattern as WI budgets.
• Actual cost computation: Previously SUM(invoices.amount) WHERE work_item_budget_id = ?. Now: SUM(invoice_budget_lines.itemized_amount) WHERE work_item_budget_id = ? joined with the invoice status for paid/claimed filtering.

Migration File

server/src/db/migrations/0017_invoice_budget_lines.sql

-- Migration 0017: Invoice-Budget-Line Junction Table (EPIC-15)
--
-- Replaces the 1:1 FK model (invoices.work_item_budget_id and
-- invoices.household_item_budget_id) with a many-to-many junction table
-- (invoice_budget_lines) that links multiple budget lines to one invoice,
-- each with an itemized_amount.

-- 1. Create the junction table
CREATE TABLE invoice_budget_lines (
  id TEXT PRIMARY KEY,
  invoice_id TEXT NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
  work_item_budget_id TEXT REFERENCES work_item_budgets(id) ON DELETE SET NULL,
  household_item_budget_id TEXT REFERENCES household_item_budgets(id) ON DELETE SET NULL,
  itemized_amount REAL NOT NULL CHECK(itemized_amount > 0),
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,

  CHECK (
    (work_item_budget_id IS NOT NULL AND household_item_budget_id IS NULL) OR
    (work_item_budget_id IS NULL AND household_item_budget_id IS NOT NULL)
  )
);

CREATE INDEX idx_invoice_budget_lines_invoice_id
  ON invoice_budget_lines(invoice_id);
CREATE INDEX idx_invoice_budget_lines_work_item_budget_id
  ON invoice_budget_lines(work_item_budget_id);
CREATE INDEX idx_invoice_budget_lines_household_item_budget_id
  ON invoice_budget_lines(household_item_budget_id);

CREATE UNIQUE INDEX idx_invoice_budget_lines_unique_wib
  ON invoice_budget_lines(work_item_budget_id)
  WHERE work_item_budget_id IS NOT NULL;
CREATE UNIQUE INDEX idx_invoice_budget_lines_unique_hib
  ON invoice_budget_lines(household_item_budget_id)
  WHERE household_item_budget_id IS NOT NULL;

-- 2. Migrate existing FK data into the junction table
INSERT INTO invoice_budget_lines (id, invoice_id, work_item_budget_id,
  household_item_budget_id, itemized_amount, created_at, updated_at)
SELECT
  lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' ||
    substr(hex(randomblob(2)),2) || '-' ||
    substr('89ab', abs(random()) % 4 + 1, 1) ||
    substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6))),
  id, work_item_budget_id, NULL, amount, datetime('now'), datetime('now')
FROM invoices WHERE work_item_budget_id IS NOT NULL;

INSERT INTO invoice_budget_lines (id, invoice_id, work_item_budget_id,
  household_item_budget_id, itemized_amount, created_at, updated_at)
SELECT
  lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' ||
    substr(hex(randomblob(2)),2) || '-' ||
    substr('89ab', abs(random()) % 4 + 1, 1) ||
    substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6))),
  id, NULL, household_item_budget_id, amount, datetime('now'), datetime('now')
FROM invoices WHERE household_item_budget_id IS NOT NULL;

-- 3. Recreate invoices table WITHOUT the budget FK columns
CREATE TABLE invoices_new (
  id TEXT PRIMARY KEY,
  vendor_id TEXT NOT NULL REFERENCES vendors(id) ON DELETE CASCADE,
  invoice_number TEXT,
  amount REAL NOT NULL,
  date TEXT NOT NULL,
  due_date TEXT,
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK(status IN ('pending', 'paid', 'claimed')),
  notes TEXT,
  created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO invoices_new (id, vendor_id, invoice_number, amount, date, due_date,
  status, notes, created_by, created_at, updated_at)
SELECT id, vendor_id, invoice_number, amount, date, due_date,
  status, notes, created_by, created_at, updated_at
FROM invoices;

DROP TABLE invoices;
ALTER TABLE invoices_new RENAME TO invoices;

CREATE INDEX idx_invoices_vendor_id ON invoices(vendor_id);
CREATE INDEX idx_invoices_status ON invoices(status);
CREATE INDEX idx_invoices_date ON invoices(date);

-- ROLLBACK (non-trivial due to table recreation — restore from backup):
--   DROP TABLE IF EXISTS invoice_budget_lines;
--   Then recreate invoices with the old FK columns via full table recreation

---

Migration File (0021 -- Discretionary Budget Source)

server/src/db/migrations/0021_discretionary_budget_source.sql

PRAGMA foreign_keys = OFF;

-- 1. Recreate budget_sources to add is_discretionary column and 'discretionary' to source_type CHECK
CREATE TABLE budget_sources_new (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  source_type TEXT NOT NULL CHECK(source_type IN ('bank_loan', 'credit_line', 'savings', 'other', 'discretionary')),
  total_amount REAL NOT NULL,
  interest_rate REAL,
  terms TEXT,
  notes TEXT,
  status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'exhausted', 'closed')),
  is_discretionary INTEGER NOT NULL DEFAULT 0,
  created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

INSERT INTO budget_sources_new
  SELECT id, name, source_type, total_amount, interest_rate, terms, notes, status,
         0, created_by, created_at, updated_at
  FROM budget_sources;

DROP TABLE budget_sources;
ALTER TABLE budget_sources_new RENAME TO budget_sources;

-- 2. Seed the Discretionary Funding system row
INSERT INTO budget_sources (id, name, source_type, total_amount, is_discretionary, status, created_at, updated_at)
SELECT
  'discretionary-system',
  'Discretionary Funding',
  'discretionary',
  0,
  1,
  'active',
  datetime('now'),
  datetime('now')
WHERE NOT EXISTS (
  SELECT 1 FROM budget_sources WHERE is_discretionary = 1
);

PRAGMA foreign_keys = ON;

1. Recreates budget_sources to add is_discretionary column and expand the source_type CHECK constraint to include 'discretionary'
2. Migrates existing rows with is_discretionary = 0
3. Seeds the system "Discretionary Funding" row with a well-known id (discretionary-system)
4. Uses WHERE NOT EXISTS guard to be idempotent

---

EPIC-13: Construction Diary (Bautagebuch)

diary_entries

Stores construction diary entries — both manual entries created by users and automatic system events generated by state changes in the application. Type-specific structured fields are stored in a JSON metadata column, validated at the application layer.

See ADR-020 for the full architectural rationale.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
entry_type	TEXT	NOT NULL CHECK(entry_type IN ('daily_log', 'site_visit', 'delivery', 'issue', 'general_note', 'work_item_status', 'invoice_status', 'milestone_delay', 'budget_breach', 'auto_reschedule', 'subsidy_status'))	Entry type — determines metadata shape and immutability rules
entry_date	TEXT	NOT NULL	ISO 8601 date (e.g., 2026-03-15) — the date the entry refers to
title	TEXT	nullable	Optional short title
body	TEXT	NOT NULL	Free-text content of the entry
metadata	TEXT	nullable	JSON string with type-specific structured fields
is_automatic	INTEGER	NOT NULL DEFAULT 0	Boolean: 0 = manual, 1 = system-generated
source_entity_type	TEXT	nullable	For auto entries: type of entity that triggered the event
source_entity_id	TEXT	nullable	For auto entries: ID of the entity that triggered the event
created_by	TEXT	REFERENCES users(id) ON DELETE SET NULL	User who created the entry (NULL for system or deleted users)
created_at	TEXT	NOT NULL	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL	ISO 8601 timestamp, updated on every modification

Entry types:

Value	Category	Description
daily_log	Manual	Daily site activity log with weather, workers, signature
site_visit	Manual	Inspection or site visit record with outcome, signature
delivery	Manual	Material/equipment delivery record
issue	Manual	Problem or defect report with severity tracking
general_note	Manual	Free-form note (no required metadata)
work_item_status	Automatic	Work item status change event
invoice_status	Automatic	Invoice status change event
milestone_delay	Automatic	Milestone target date change event
budget_breach	Automatic	Budget threshold exceeded event
auto_reschedule	Automatic	Scheduling engine auto-reschedule event
subsidy_status	Automatic	Subsidy application status change event

Metadata JSON shapes (per entry type):

• daily_log: { weather?, temperatureCelsius?, workersOnSite?, hasSignature?, signatureDataUrl? }
• site_visit: { inspectorName?, outcome?, hasSignature?, signatureDataUrl? }
• delivery: { vendor?, materials?, deliveryConfirmed? }
• issue: { severity?, resolutionStatus? }
• general_note: No required fields (any JSON object accepted)
• Automatic entries: { changeSummary?, previousValue?, newValue?, ...additionalContext }

Source entity types (for automatic entries):

Value	Referenced Table
work_item	work_items
invoice	invoices
milestone	milestones
budget_source	budget_sources
subsidy_program	subsidy_programs

Indexes:

Index	Columns	Type	Rationale
idx_diary_entries_entry_date	entry_date DESC, created_at DESC	INDEX	Timeline view: entries sorted by date (newest first)
idx_diary_entries_entry_type	entry_type	INDEX	Filter entries by type
idx_diary_entries_is_automatic	is_automatic	INDEX	Filter manual vs system-generated entries
idx_diary_entries_source_entity	source_entity_type, source_entity_id	INDEX (partial: WHERE NOT NULL)	Find diary entries for a specific source entity

Immutability rules:

• Manual entries are editable: title, body, metadata, entry_date can be updated. entry_type and is_automatic cannot be changed after creation.
• Automatic entries are completely immutable: cannot be updated or deleted. They serve as an audit trail.

Photo attachments:

Photos are attached to diary entries via the existing photos table using entity_type = 'diary_entry' and entity_id = diary_entries.id. No additional schema is needed. When a diary entry is deleted, its associated photos (records and files) are cascade-deleted via the photo service.

Design rationale:

• JSON metadata column: Avoids creating 11 separate tables or 15+ nullable columns. SQLite handles JSON well. New entry types or metadata fields can be added without schema migrations. Application-layer validation ensures data integrity. See ADR-020.
• Polymorphic source entity references: Same pattern as document_links and photos. No FK on source_entity_id — referential integrity enforced at application layer. Diary entries intentionally survive source entity deletion (historical audit trail).
• is_automatic flag: Enables simple filtering and controls immutability rules at the application layer. Combined with entry_type, the CHECK constraint implicitly validates that only valid type/automatic combinations are used.
• entry_date separate from created_at: The diary date a user assigns (e.g., "what happened on March 15") is distinct from when the record was created. Auto events set entry_date to the current date.

Migration File

server/src/db/migrations/0024_diary_entries.sql

-- Migration 0024: Create diary_entries table for construction diary (Bautagebuch)
--
-- EPIC-13: Construction Diary

CREATE TABLE diary_entries (
  id TEXT PRIMARY KEY,
  entry_type TEXT NOT NULL CHECK(entry_type IN (
    'daily_log', 'site_visit', 'delivery', 'issue', 'general_note',
    'work_item_status', 'invoice_status', 'milestone_delay',
    'budget_breach', 'auto_reschedule', 'subsidy_status'
  )),
  entry_date TEXT NOT NULL,
  title TEXT,
  body TEXT NOT NULL,
  metadata TEXT,
  is_automatic INTEGER NOT NULL DEFAULT 0,
  source_entity_type TEXT,
  source_entity_id TEXT,
  created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE INDEX idx_diary_entries_entry_date
  ON diary_entries (entry_date DESC, created_at DESC);

CREATE INDEX idx_diary_entries_entry_type
  ON diary_entries (entry_type);

CREATE INDEX idx_diary_entries_is_automatic
  ON diary_entries (is_automatic);

CREATE INDEX idx_diary_entries_source_entity
  ON diary_entries (source_entity_type, source_entity_id)
  WHERE source_entity_type IS NOT NULL;

-- Rollback:
-- DROP INDEX IF EXISTS idx_diary_entries_source_entity;
-- DROP INDEX IF EXISTS idx_diary_entries_is_automatic;
-- DROP INDEX IF EXISTS idx_diary_entries_entry_type;
-- DROP INDEX IF EXISTS idx_diary_entries_entry_date;
-- DROP TABLE IF EXISTS diary_entries;

---

EPIC-18: Area & Trade Structured Dimensions

EPIC-18 replaces the generic tagging system with two purpose-built dimensions for organizing work items and household items. See ADR-028 for detailed rationale.

areas

Hierarchical spatial organization of the construction project. Areas represent physical locations in the house (e.g., "Kitchen", "2nd Floor > Master Bedroom"). Supports arbitrary nesting depth via self-referencing parent_id.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
name	TEXT	NOT NULL	Area display name (1-200 chars)
parent_id	TEXT	nullable, REFERENCES areas(id) ON DELETE CASCADE	Parent area for hierarchy; NULL = top-level area
color	TEXT	nullable	Hex color code (#RRGGBB) for UI display
description	TEXT	nullable	Optional description of the area
sort_order	INTEGER	NOT NULL DEFAULT 0	Display order among siblings (ascending)
created_at	TEXT	NOT NULL	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL	ISO 8601 timestamp, updated on every modification

Constraints:

• UNIQUE(name, parent_id) — prevents duplicate sibling names under the same parent. Top-level areas (NULL parent) are enforced unique by application logic (SQLite treats NULLs as distinct in UNIQUE constraints).

Indexes:

Index	Columns	Type	Rationale
idx_areas_parent_id	parent_id	INDEX	Fast child area lookups for tree construction

Design rationale:

• Self-referencing parent_id: Enables arbitrary hierarchy depth without enforcing fixed levels. A kitchen island bench can be "Kitchen > Island > Bench" without schema changes.
• ON DELETE CASCADE for parent_id: Deleting a parent area cascades to all descendants. The API prevents deletion if any area in the subtree is referenced by work items or household items (409 CONFLICT), so the cascade only fires for unreferenced subtrees.
• UNIQUE(name, parent_id) partial: Two areas under the same parent cannot share a name. Areas under different parents may share names (e.g., "Closet" in both "Master Bedroom" and "Guest Bedroom").
• No created_by: Areas are a shared organizational resource (same as budget categories), not user-authored content.
• sort_order among siblings: Controls display order within a parent. Separate from alphabetical sorting, enabling custom ordering.

trades

Flat classification of craft/skill types. Trades represent the kind of work performed (e.g., "Plumbing", "Electrical", "Carpentry"). Assigned to vendors rather than directly to work items.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID generated by crypto.randomUUID()
name	TEXT	UNIQUE NOT NULL	Trade display name (1-200 chars)
color	TEXT	nullable	Hex color code (#RRGGBB) for UI display
description	TEXT	nullable	Optional description of the trade
sort_order	INTEGER	NOT NULL DEFAULT 0	Display order (ascending)
created_at	TEXT	NOT NULL	ISO 8601 timestamp, set on insert
updated_at	TEXT	NOT NULL	ISO 8601 timestamp, updated on every modification

Indexes:

Index	Columns	Type	Rationale
sqlite_autoindex_trades_1	name	UNIQUE	Enforced by UNIQUE constraint; prevents duplicate trade names

Seeded values (15 defaults):

Name	Description
Plumbing	Water supply, drainage, gas piping
HVAC	Heating, ventilation, air conditioning
Electrical	Wiring, panels, fixtures, smart home
Drywall	Wall/ceiling installation and finishing
Carpentry	Framing, trim, cabinetry, woodwork
Masonry	Brick, stone, concrete block work
Painting	Interior/exterior painting and finishing
Roofing	Roof installation, repair, gutters
Flooring	Hardwood, tile, carpet, vinyl installation
Tiling	Wall and floor tile installation
Landscaping	Outdoor grading, planting, hardscape
Excavation	Site preparation, grading, trenching
General Contractor	Overall project coordination
Architect / Design	Architectural and interior design services
Other	Trades not covered by other categories

Design rationale:

• Flat list (no hierarchy): Trades do not have a natural parent-child relationship. "Plumbing" is not a sub-trade of anything. A flat list is simpler to manage and query.
• name UNIQUE: Trade names must be globally unique. Case-insensitive uniqueness enforced at application layer.
• Assigned to vendors, not work items: A trade describes the craftsperson's skill, not the task. Work items inherit their trade transitively via work_items.assigned_vendor_id -> vendors.trade_id.
• 15 seeded defaults: Covers the most common construction trades. Users can add, rename, or delete them.

Table Modifications

vendors — Drop specialty, Add trade_id

Change	Column	Type	Constraints	Description
DROP	specialty	TEXT	(was nullable)	Replaced by structured trade_id
ADD	trade_id	TEXT	nullable, REFERENCES trades(id) ON DELETE SET NULL	FK to the trades table

New index: idx_vendors_trade_id on trade_id — supports filtering/joining vendors by trade.

Design rationale: The free-text specialty field provided no structure for aggregation or filtering. The trade_id FK enables structured trade-based queries. ON DELETE SET NULL preserves vendor records if a trade is deleted (though the API prevents trade deletion while vendors reference it).

work_items — Add area_id, assigned_vendor_id, CHECK constraint

Change	Column	Type	Constraints	Description
ADD	area_id	TEXT	nullable, REFERENCES areas(id) ON DELETE SET NULL	Spatial location of this work item
ADD	assigned_vendor_id	TEXT	nullable, REFERENCES vendors(id) ON DELETE SET NULL	Vendor responsible for this work item

CHECK constraint: CHECK(assigned_user_id IS NULL OR assigned_vendor_id IS NULL) — enforces mutual exclusivity. A work item can be assigned to a user OR a vendor, but not both simultaneously.

New indexes:

• idx_work_items_area_id on area_id — supports area-based filtering
• idx_work_items_assigned_vendor_id on assigned_vendor_id — supports vendor assignment filtering

Design rationale:

• area_id ON DELETE SET NULL: If an area is deleted, work items are unassigned from that area but preserved. However, the API prevents area deletion while items reference it (409 CONFLICT), so SET NULL is a safety net.
• assigned_vendor_id ON DELETE SET NULL: Consistent with assigned_user_id behavior. If a vendor is deleted, the work item loses its vendor assignment but survives.
• Mutual exclusivity CHECK: Models the real-world distinction that either you (user) do the work, or a contractor (vendor) does. The API validates this before the database constraint fires, returning a descriptive error.

household_items — Drop room, Add area_id

Change	Column	Type	Constraints	Description
DROP	room	TEXT	(was nullable)	Replaced by structured area_id
ADD	area_id	TEXT	nullable, REFERENCES areas(id) ON DELETE SET NULL	Spatial location of this household item

New index: idx_household_items_area_id on area_id — replaces idx_household_items_room.

Dropped index: idx_household_items_room — no longer needed.

Design rationale: The free-text room field is replaced by the structured area_id FK, enabling consistent spatial organization between work items and household items. Users can represent any room (or sub-room) as an area in the hierarchy.

Dropped Tables

tags (DROPPED — EPIC-18)

This table was dropped in migration 0018. Tags are replaced by the areas and trades tables, which provide structured dimensions instead of generic labels.

work_item_tags (DROPPED — EPIC-18)

This table was dropped in migration 0018. The work item tagging relationship is replaced by work_items.area_id (spatial) and transitive trade lookup via work_items.assigned_vendor_id -> vendors.trade_id.

household_item_tags (DROPPED — EPIC-18)

This table was dropped in migration 0018. The household item tagging relationship is replaced by household_items.area_id.

Default Data Changes (Migration 0018)

Budget categories:

• ADD: bc-waste ("Waste") — waste disposal and recycling costs
• CONDITIONAL DELETE (only if 0 references): bc-equipment, bc-landscaping, bc-utilities, bc-insurance, bc-contingency

Household item categories:

• ADD: hic-equipment ("Equipment") — tools and construction equipment
• CONDITIONAL DELETE (only if 0 references): hic-outdoor, hic-storage

Entity Relationship Diagram (EPIC-18)

+------------------+
|     areas        |
+------------------+
| id (PK, UUID)   |<--+  (self-ref: parent_id)
| name             |   |
| parent_id FK ----+---+
| color            |
| description      |
| sort_order       |
| created_at       |
| updated_at       |
+------------------+
    |          |
    | 1:N      | 1:N
    |          |
    v          v
work_items   household_items
(area_id)    (area_id)


+------------------+
|     trades       |
+------------------+
| id (PK, UUID)   |
| name (UNIQUE)    |
| color            |
| description      |
| sort_order       |
| created_at       |
| updated_at       |
+------------------+
    |
    | 1:N
    v
  vendors (trade_id)
    |
    | 1:N (assigned_vendor_id)
    v
  work_items

Key relationships:

• areas -> work_items: One-to-many (optional). Each work item can belong to one area. ON DELETE SET NULL.
• areas -> household_items: One-to-many (optional). Each household item can belong to one area. ON DELETE SET NULL.
• areas -> areas: Self-referential one-to-many (parent-child). ON DELETE CASCADE for subtree cleanup.
• trades -> vendors: One-to-many (optional). Each vendor can have one trade. ON DELETE SET NULL.
• vendors -> work_items: One-to-many via assigned_vendor_id (optional). A vendor can be assigned to many work items. ON DELETE SET NULL. Mutually exclusive with assigned_user_id.

Migration File

server/src/db/migrations/0018_areas_trades.sql

-- EPIC-18: Area & Trade Structured Dimensions
-- Replaces the generic tagging system with purpose-built areas and trades.
-- See ADR-028 for detailed rationale.

-- ── New tables ──────────────────────────────────────────────────────

CREATE TABLE areas (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  parent_id TEXT REFERENCES areas(id) ON DELETE CASCADE,
  color TEXT,
  description TEXT,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  UNIQUE(name, parent_id)
);

CREATE INDEX idx_areas_parent_id ON areas(parent_id);

CREATE TABLE trades (
  id TEXT PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  color TEXT,
  description TEXT,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

-- ── Seed default trades ─────────────────────────────────────────────

INSERT INTO trades (id, name, color, description, sort_order, created_at, updated_at) VALUES
  ('trade-plumbing',    'Plumbing',            '#0EA5E9', 'Water supply, drainage, gas piping',          0,  datetime('now'), datetime('now')),
  ('trade-hvac',        'HVAC',                '#8B5CF6', 'Heating, ventilation, air conditioning',      1,  datetime('now'), datetime('now')),
  ('trade-electrical',  'Electrical',          '#F59E0B', 'Wiring, panels, fixtures, smart home',        2,  datetime('now'), datetime('now')),
  ('trade-drywall',     'Drywall',             '#A3A3A3', 'Wall/ceiling installation and finishing',      3,  datetime('now'), datetime('now')),
  ('trade-carpentry',   'Carpentry',           '#92400E', 'Framing, trim, cabinetry, woodwork',          4,  datetime('now'), datetime('now')),
  ('trade-masonry',     'Masonry',             '#78716C', 'Brick, stone, concrete block work',            5,  datetime('now'), datetime('now')),
  ('trade-painting',    'Painting',            '#EC4899', 'Interior/exterior painting and finishing',     6,  datetime('now'), datetime('now')),
  ('trade-roofing',     'Roofing',             '#DC2626', 'Roof installation, repair, gutters',           7,  datetime('now'), datetime('now')),
  ('trade-flooring',    'Flooring',            '#65A30D', 'Hardwood, tile, carpet, vinyl installation',   8,  datetime('now'), datetime('now')),
  ('trade-tiling',      'Tiling',              '#0891B2', 'Wall and floor tile installation',              9,  datetime('now'), datetime('now')),
  ('trade-landscaping', 'Landscaping',         '#22C55E', 'Outdoor grading, planting, hardscape',        10,  datetime('now'), datetime('now')),
  ('trade-excavation',  'Excavation',          '#A16207', 'Site preparation, grading, trenching',        11,  datetime('now'), datetime('now')),
  ('trade-gc',          'General Contractor',  '#3B82F6', 'Overall project coordination',                12,  datetime('now'), datetime('now')),
  ('trade-design',      'Architect / Design',  '#6366F1', 'Architectural and interior design services',  13,  datetime('now'), datetime('now')),
  ('trade-other',       'Other',               '#6B7280', 'Trades not covered by other categories',      14,  datetime('now'), datetime('now'));

-- ── Modify vendors: drop specialty, add trade_id ────────────────────

ALTER TABLE vendors ADD COLUMN trade_id TEXT REFERENCES trades(id) ON DELETE SET NULL;
CREATE INDEX idx_vendors_trade_id ON vendors(trade_id);

-- Note: SQLite does not support DROP COLUMN in older versions.
-- The specialty column is left in place but ignored by the application.
-- A future migration can rebuild the table to physically remove it.
-- Application code stops reading/writing specialty after this migration.

-- ── Modify work_items: add area_id, assigned_vendor_id ──────────────

ALTER TABLE work_items ADD COLUMN area_id TEXT REFERENCES areas(id) ON DELETE SET NULL;
ALTER TABLE work_items ADD COLUMN assigned_vendor_id TEXT REFERENCES vendors(id) ON DELETE SET NULL;

CREATE INDEX idx_work_items_area_id ON work_items(area_id);
CREATE INDEX idx_work_items_assigned_vendor_id ON work_items(assigned_vendor_id);

-- Note: SQLite does not support ADD CHECK on existing tables via ALTER.
-- The CHECK(assigned_user_id IS NULL OR assigned_vendor_id IS NULL) constraint
-- is enforced at the application layer. New work_items created after this migration
-- will have the constraint in the Drizzle schema definition.

-- ── Modify household_items: add area_id ─────────────────────────────

ALTER TABLE household_items ADD COLUMN area_id TEXT REFERENCES areas(id) ON DELETE SET NULL;
CREATE INDEX idx_household_items_area_id ON household_items(area_id);

-- Note: The room column is left in place but ignored by the application.
-- A future migration can rebuild the table to physically remove it.
-- The idx_household_items_room index is also left in place (harmless).

-- ── Drop tag tables ─────────────────────────────────────────────────

DROP INDEX IF EXISTS idx_household_item_tags_tag_id;
DROP TABLE IF EXISTS household_item_tags;

DROP INDEX IF EXISTS idx_work_item_tags_tag_id;
DROP TABLE IF EXISTS work_item_tags;

DROP TABLE IF EXISTS tags;

-- ── Budget category default changes ─────────────────────────────────

INSERT OR IGNORE INTO budget_categories (id, name, description, color, sort_order, created_at, updated_at)
VALUES ('bc-waste', 'Waste', 'Waste disposal and recycling costs', '#A16207', 10, datetime('now'), datetime('now'));

-- Conditionally delete unused budget category defaults
DELETE FROM budget_categories WHERE id = 'bc-equipment'
  AND NOT EXISTS (SELECT 1 FROM work_item_budgets WHERE budget_category_id = 'bc-equipment')
  AND NOT EXISTS (SELECT 1 FROM household_item_budgets WHERE budget_category_id = 'bc-equipment');

DELETE FROM budget_categories WHERE id = 'bc-landscaping'
  AND NOT EXISTS (SELECT 1 FROM work_item_budgets WHERE budget_category_id = 'bc-landscaping')
  AND NOT EXISTS (SELECT 1 FROM household_item_budgets WHERE budget_category_id = 'bc-landscaping');

DELETE FROM budget_categories WHERE id = 'bc-utilities'
  AND NOT EXISTS (SELECT 1 FROM work_item_budgets WHERE budget_category_id = 'bc-utilities')
  AND NOT EXISTS (SELECT 1 FROM household_item_budgets WHERE budget_category_id = 'bc-utilities');

DELETE FROM budget_categories WHERE id = 'bc-insurance'
  AND NOT EXISTS (SELECT 1 FROM work_item_budgets WHERE budget_category_id = 'bc-insurance')
  AND NOT EXISTS (SELECT 1 FROM household_item_budgets WHERE budget_category_id = 'bc-insurance');

DELETE FROM budget_categories WHERE id = 'bc-contingency'
  AND NOT EXISTS (SELECT 1 FROM work_item_budgets WHERE budget_category_id = 'bc-contingency')
  AND NOT EXISTS (SELECT 1 FROM household_item_budgets WHERE budget_category_id = 'bc-contingency');

-- ── Household item category default changes ─────────────────────────

INSERT OR IGNORE INTO household_item_categories (id, name, color, sort_order, created_at, updated_at)
VALUES ('hic-equipment', 'Equipment', '#78716C', 8, datetime('now'), datetime('now'));

DELETE FROM household_item_categories WHERE id = 'hic-outdoor'
  AND NOT EXISTS (SELECT 1 FROM household_items WHERE category_id = 'hic-outdoor');

DELETE FROM household_item_categories WHERE id = 'hic-storage'
  AND NOT EXISTS (SELECT 1 FROM household_items WHERE category_id = 'hic-storage');

-- Rollback:
-- DELETE FROM household_item_categories WHERE id = 'hic-equipment';
-- INSERT OR IGNORE INTO household_item_categories (id, name, color, sort_order, created_at, updated_at)
--   VALUES ('hic-outdoor', 'Outdoor', '#22C55E', 5, datetime('now'), datetime('now'));
-- INSERT OR IGNORE INTO household_item_categories (id, name, color, sort_order, created_at, updated_at)
--   VALUES ('hic-storage', 'Storage', '#F97316', 6, datetime('now'), datetime('now'));
-- DELETE FROM budget_categories WHERE id = 'bc-waste';
-- DROP INDEX IF EXISTS idx_household_items_area_id;
-- ALTER TABLE household_items DROP COLUMN area_id;  -- requires table rebuild in SQLite
-- DROP INDEX IF EXISTS idx_work_items_assigned_vendor_id;
-- DROP INDEX IF EXISTS idx_work_items_area_id;
-- ALTER TABLE work_items DROP COLUMN assigned_vendor_id;  -- requires table rebuild
-- ALTER TABLE work_items DROP COLUMN area_id;  -- requires table rebuild
-- DROP INDEX IF EXISTS idx_vendors_trade_id;
-- ALTER TABLE vendors DROP COLUMN trade_id;  -- requires table rebuild
-- CREATE TABLE tags (...);
-- CREATE TABLE work_item_tags (...);
-- CREATE TABLE household_item_tags (...);
-- DROP TABLE IF EXISTS trades;
-- DROP INDEX IF EXISTS idx_areas_parent_id;
-- DROP TABLE IF EXISTS areas;

Migration notes:

• SQLite ALTER TABLE limitations: SQLite does not support DROP COLUMN in versions before 3.35.0 or ADD CHECK constraints via ALTER. The migration leaves vendors.specialty and household_items.room columns physically in place but the application ignores them. The CHECK constraint on work_items for mutual exclusivity is enforced at the application layer.
• Tag table drops are safe: All tag data is permanently deleted. Since tags are being replaced by structured dimensions, there is no data migration path — the concepts are fundamentally different.
• Conditional category deletes: Budget categories and household item categories are only deleted if zero budget lines or household items reference them, preventing data integrity issues for active projects.

---

Deviation Log

Date	Page Section	Deviation	Resolution
2026-02-27	work_items table, Status values, Migration File	PR #308 added actual_start_date/actual_end_date columns and removed blocked status. Wiki was not updated alongside the implementation.	Updated work_items table to include new columns, updated status CHECK constraint and status values table, added auto-population documentation, added Migration 0008 section.
2026-03-03	household_items, household_item_work_items, EPIC-04	PR #416 replaced household_item_work_items with household_item_deps, added delivery date columns to household_items. Wiki was not updated alongside the implementation.	Added earliest_delivery_date/latest_delivery_date to household_items table, replaced household_item_work_items docs with household_item_deps, added Migration 0012 section.
2026-03-04	household_items table, Delivery Date Columns	PR #456 replaced expected_delivery_date with three-part model: target_delivery_date (computed), earliest_delivery_date/latest_delivery_date (user-editable constraints), and is_late flag. Wiki had outdated column docs and was missing Migration 0015.	Updated household_items table schema to reflect new columns, updated design rationale, added Migration 0015 section, updated indexes.
2026-03-06	household_items table, Migration 0016, EPIC-09	PR #516 (Issue #509) replaced household_items.category CHECK constraint enum with category_id FK to new household_item_categories lookup table. Migration 0016 was previously documented as user_preferences but the actual 0016 is household_item_categories. Also seeds bc-household-items budget category. Wiki was not updated alongside the implementation.	Added household_item_categories table, updated household_items to show category_id instead of category, replaced migration 0016 with household_item_categories, renumbered user_preferences to 0017 (not yet implemented). Added HI category CRUD endpoints to API Contract.
2026-03-09	invoice_budget_lines FK constraints, Migration number	Two deviations: (1) Schema.md documented ON DELETE SET NULL for work_item_budget_id and household_item_budget_id FKs, but actual migration 0017 and Drizzle schema use ON DELETE CASCADE (required due to XOR CHECK constraint incompatibility with SET NULL, see Bug #611). (2) user_preferences migration was documented as 0017 but 0017 is invoice_budget_lines; actual number is 0018.	Fixed FK constraints to ON DELETE CASCADE with rationale. Renumbered user_preferences migration to 0018.