1 files changed, 32 insertions, 0 deletions
diff --git a/makima/migrations/20260216100000_orders_remove_contract_add_directive_name.sql b/makima/migrations/20260216100000_orders_remove_contract_add_directive_name.sql
new file mode 100644
index 0000000..cacd7a7
--- /dev/null
+++ b/makima/migrations/20260216100000_orders_remove_contract_add_directive_name.sql
@@ -0,0 +1,32 @@
+-- Remove contract_id from orders (orders are tied only to directives)
+ALTER TABLE orders DROP COLUMN IF EXISTS contract_id;
+DROP INDEX IF EXISTS idx_orders_contract_id;
+
+-- Add directive_name as a denormalized field for searchability
+ALTER TABLE orders ADD COLUMN IF NOT EXISTS directive_name VARCHAR(500);
+
+-- Make directive_id required for new orders (but keep existing NULLs)
+-- We use a trigger approach to populate directive_name automatically
+CREATE OR REPLACE FUNCTION update_order_directive_name()
+RETURNS TRIGGER AS $$
+BEGIN
+  IF NEW.directive_id IS NOT NULL THEN
+    SELECT title INTO NEW.directive_name FROM directives WHERE id = NEW.directive_id;
+  ELSE
+    NEW.directive_name := NULL;
+  END IF;
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_order_directive_name
+  BEFORE INSERT OR UPDATE OF directive_id ON orders
+  FOR EACH ROW
+  EXECUTE FUNCTION update_order_directive_name();
+
+-- Backfill directive_name for existing orders
+UPDATE orders o SET directive_name = d.title
+FROM directives d WHERE o.directive_id = d.id;
+
+-- Index for searching by directive_name
+CREATE INDEX IF NOT EXISTS idx_orders_directive_name ON orders(directive_name);