-- Orders system: card-based issue tracker (similar to Linear/Jira/GitHub Issues).
-- Orders represent planned work items (features, bugs, spikes) that can later be
-- attached to directives (as steps) or contracts for execution.
CREATE TABLE IF NOT EXISTS orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id UUID NOT NULL REFERENCES owners(id) ON DELETE CASCADE,
title VARCHAR(500) NOT NULL,
description TEXT,
-- Priority: critical > high > medium > low > none
priority VARCHAR(32) NOT NULL DEFAULT 'medium'
CHECK (priority IN ('critical', 'high', 'medium', 'low', 'none')),
-- Status lifecycle: open -> in_progress -> done | archived
status VARCHAR(32) NOT NULL DEFAULT 'open'
CHECK (status IN ('open', 'in_progress', 'done', 'archived')),
-- Type of work item
order_type VARCHAR(32) NOT NULL DEFAULT 'feature'
CHECK (order_type IN ('feature', 'bug', 'spike', 'chore', 'improvement')),
-- Flexible labels stored as JSON array of strings
labels JSONB NOT NULL DEFAULT '[]',
-- Optional links to directives, directive steps, and contracts
directive_id UUID REFERENCES directives(id) ON DELETE SET NULL,
directive_step_id UUID REFERENCES directive_steps(id) ON DELETE SET NULL,
contract_id UUID REFERENCES contracts(id) ON DELETE SET NULL,
-- Repository context
repository_url VARCHAR(512),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Index for listing orders by owner
CREATE INDEX IF NOT EXISTS idx_orders_owner_id ON orders(owner_id);
-- Composite index for filtering by owner + status (common query pattern)
CREATE INDEX IF NOT EXISTS idx_orders_owner_status ON orders(owner_id, status);
-- Index for looking up orders linked to a directive
CREATE INDEX IF NOT EXISTS idx_orders_directive_id ON orders(directive_id);
-- Index for looking up orders linked to a contract
CREATE INDEX IF NOT EXISTS idx_orders_contract_id ON orders(contract_id);
