feat: makima.jp: Create database migration for orders tablemakima/makima-jp--create-database-migration-for-orders-ta-35c83406

1 files changed, 43 insertions, 0 deletions

diff --git a/makima/migrations/20260212000000_create_orders.sql b/makima/migrations/20260212000000_create_orders.sql
new file mode 100644
index 0000000..0cf0863
--- /dev/null
+++ b/makima/migrations/20260212000000_create_orders.sql
@@ -0,0 +1,43 @@
+-- Orders: Makima's commands - work items for future features, bugs, and spikes
+-- These can be linked to directives or contracts once work begins
+
+CREATE TABLE 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,
+    order_type VARCHAR(32) NOT NULL DEFAULT 'feature'
+        CHECK (order_type IN ('feature', 'bug', 'spike', 'chore')),
+    status VARCHAR(32) NOT NULL DEFAULT 'backlog'
+        CHECK (status IN ('backlog', 'todo', 'in_progress', 'done', 'archived')),
+    priority VARCHAR(32) NOT NULL DEFAULT 'medium'
+        CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
+    directive_id UUID REFERENCES directives(id) ON DELETE SET NULL,
+    contract_id UUID REFERENCES contracts(id) ON DELETE SET NULL,
+    repository_url VARCHAR(512),
+    version INTEGER NOT NULL DEFAULT 1,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE INDEX idx_orders_owner_id ON orders(owner_id);
+CREATE INDEX idx_orders_status ON orders(owner_id, status);
+CREATE INDEX idx_orders_directive_id ON orders(directive_id);
+CREATE INDEX idx_orders_contract_id ON orders(contract_id);
+CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
+CREATE INDEX idx_orders_priority ON orders(owner_id, priority);
+
+CREATE TABLE order_labels (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    owner_id UUID NOT NULL REFERENCES owners(id) ON DELETE CASCADE,
+    name VARCHAR(100) NOT NULL,
+    color VARCHAR(7) NOT NULL DEFAULT '#75aafc',
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    UNIQUE(owner_id, name)
+);
+
+CREATE TABLE order_label_assignments (
+    order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
+    label_id UUID NOT NULL REFERENCES order_labels(id) ON DELETE CASCADE,
+    PRIMARY KEY (order_id, label_id)
+);