Initial Control system

15 files changed, 492 insertions, 0 deletions

diff --git a/makima/migrations/20250102000000_create_mesh_tables.sql b/makima/migrations/20250102000000_create_mesh_tables.sql
new file mode 100644
index 0000000..5b01d57
--- /dev/null
+++ b/makima/migrations/20250102000000_create_mesh_tables.sql
@@ -0,0 +1,83 @@
+-- Create tasks table for orchestrating Claude Code instances
+CREATE TABLE IF NOT EXISTS tasks (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    owner_id UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000002',
+    parent_task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
+    name VARCHAR(255) NOT NULL,
+    description TEXT,
+    status VARCHAR(32) NOT NULL DEFAULT 'pending',  -- pending/running/paused/blocked/done/failed/merged
+    priority INTEGER NOT NULL DEFAULT 0,
+    plan TEXT NOT NULL,
+
+    -- Daemon/container info
+    daemon_id UUID,
+    container_id VARCHAR(255),           -- Docker/Podman container ID
+    overlay_path VARCHAR(512),           -- Path to overlay upper layer on daemon
+
+    -- Repository info
+    repository_url VARCHAR(512),
+    base_branch VARCHAR(255),            -- Branch overlay is based on
+    target_branch VARCHAR(255),          -- Branch to merge into when complete
+
+    -- Merge settings
+    merge_mode VARCHAR(32) DEFAULT 'pr', -- 'pr' (create PR), 'auto' (auto-merge), 'manual'
+    pr_url VARCHAR(512),                 -- URL if PR created
+
+    -- Progress tracking
+    progress_summary TEXT,
+    last_output TEXT,
+    error_message TEXT,
+
+    -- Timestamps
+    started_at TIMESTAMPTZ,
+    completed_at TIMESTAMPTZ,
+    version INTEGER NOT NULL DEFAULT 1,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+-- Create indexes for tasks
+CREATE INDEX idx_tasks_owner_id ON tasks(owner_id);
+CREATE INDEX idx_tasks_parent_id ON tasks(parent_task_id);
+CREATE INDEX idx_tasks_status ON tasks(status);
+CREATE INDEX idx_tasks_daemon_id ON tasks(daemon_id);
+CREATE INDEX idx_tasks_created_at ON tasks(created_at DESC);
+
+-- Create trigger to auto-update updated_at (reuse existing function)
+CREATE TRIGGER update_tasks_updated_at
+    BEFORE UPDATE ON tasks
+    FOR EACH ROW
+    EXECUTE FUNCTION update_updated_at_column();
+
+-- Create task events log for audit and state changes
+CREATE TABLE IF NOT EXISTS task_events (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
+    event_type VARCHAR(64) NOT NULL,  -- 'status_change', 'output', 'progress', 'sibling_broadcast', etc.
+    previous_status VARCHAR(32),
+    new_status VARCHAR(32),
+    event_data JSONB,  -- Flexible event payload
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE INDEX idx_task_events_task_id ON task_events(task_id);
+CREATE INDEX idx_task_events_created_at ON task_events(created_at DESC);
+
+-- Create connected daemons registry
+CREATE TABLE IF NOT EXISTS daemons (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    owner_id UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000002',
+    connection_id VARCHAR(255) NOT NULL UNIQUE,  -- WebSocket session ID
+    hostname VARCHAR(255),
+    machine_id VARCHAR(255),  -- For identifying the machine
+    max_concurrent_tasks INTEGER NOT NULL DEFAULT 1,
+    current_task_count INTEGER NOT NULL DEFAULT 0,
+    status VARCHAR(32) NOT NULL DEFAULT 'connected',  -- connected, disconnected, unhealthy
+    last_heartbeat_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    connected_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    disconnected_at TIMESTAMPTZ
+);
+
+CREATE INDEX idx_daemons_owner_id ON daemons(owner_id);
+CREATE INDEX idx_daemons_status ON daemons(status);
+CREATE INDEX idx_daemons_connection_id ON daemons(connection_id);
diff --git a/makima/migrations/20250104000000_create_mesh_chat_history.sql b/makima/migrations/20250104000000_create_mesh_chat_history.sql
new file mode 100644
index 0000000..2617855
--- /dev/null
+++ b/makima/migrations/20250104000000_create_mesh_chat_history.sql
@@ -0,0 +1,34 @@
+-- Create mesh_chat_conversations table for storing global conversation threads
+CREATE TABLE IF NOT EXISTS mesh_chat_conversations (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    owner_id UUID NOT NULL DEFAULT '00000000-0000-0000-0000-000000000002',
+    name VARCHAR(255),
+    is_active BOOLEAN NOT NULL DEFAULT true,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE INDEX idx_mesh_chat_conversations_owner ON mesh_chat_conversations(owner_id);
+CREATE INDEX idx_mesh_chat_conversations_active ON mesh_chat_conversations(is_active);
+
+CREATE TRIGGER update_mesh_chat_conversations_updated_at
+    BEFORE UPDATE ON mesh_chat_conversations
+    FOR EACH ROW
+    EXECUTE FUNCTION update_updated_at_column();
+
+-- Create mesh_chat_messages table for individual messages
+CREATE TABLE IF NOT EXISTS mesh_chat_messages (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    conversation_id UUID NOT NULL REFERENCES mesh_chat_conversations(id) ON DELETE CASCADE,
+    role VARCHAR(16) NOT NULL CHECK (role IN ('user', 'assistant', 'error')),
+    content TEXT NOT NULL,
+    context_type VARCHAR(32) NOT NULL DEFAULT 'mesh',  -- mesh/task/subtask
+    context_task_id UUID REFERENCES tasks(id) ON DELETE SET NULL,
+    tool_calls JSONB,
+    pending_questions JSONB,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE INDEX idx_mesh_chat_messages_conversation ON mesh_chat_messages(conversation_id);
+CREATE INDEX idx_mesh_chat_messages_created ON mesh_chat_messages(created_at);
+CREATE INDEX idx_mesh_chat_messages_context_task ON mesh_chat_messages(context_task_id);
diff --git a/makima/migrations/20250106000000_add_task_depth.sql b/makima/migrations/20250106000000_add_task_depth.sql
new file mode 100644
index 0000000..e0fdd03
--- /dev/null
+++ b/makima/migrations/20250106000000_add_task_depth.sql
@@ -0,0 +1,21 @@
+-- Add depth column for task hierarchy (0=top-level, 1=subtask, 2=sub-subtask)
+-- Max depth is 2 (3 levels total)
+
+ALTER TABLE tasks ADD COLUMN depth INTEGER NOT NULL DEFAULT 0;
+ALTER TABLE tasks ADD CONSTRAINT tasks_depth_check CHECK (depth >= 0 AND depth < 3);
+
+-- Backfill existing tasks based on parent chain
+WITH RECURSIVE task_depth AS (
+    SELECT id, parent_task_id, 0 as calculated_depth
+    FROM tasks
+    WHERE parent_task_id IS NULL
+    UNION ALL
+    SELECT t.id, t.parent_task_id, td.calculated_depth + 1
+    FROM tasks t
+    JOIN task_depth td ON t.parent_task_id = td.id
+)
+UPDATE tasks SET depth = task_depth.calculated_depth
+FROM task_depth WHERE tasks.id = task_depth.id;
+
+-- Index for depth queries
+CREATE INDEX idx_tasks_depth ON tasks(depth);
diff --git a/makima/migrations/20250107000000_simplify_task_depth.sql b/makima/migrations/20250107000000_simplify_task_depth.sql
new file mode 100644
index 0000000..73e4ba0
--- /dev/null
+++ b/makima/migrations/20250107000000_simplify_task_depth.sql
@@ -0,0 +1,18 @@
+-- Simplify task hierarchy to only 2 levels: orchestrator (depth 0) and subtasks (depth 1)
+-- Subtasks cannot have their own children
+
+-- First, check for any existing depth-2 tasks and fail if found
+DO $$
+BEGIN
+    IF EXISTS (SELECT 1 FROM tasks WHERE depth >= 2) THEN
+        RAISE EXCEPTION 'Cannot migrate: tasks with depth >= 2 exist. Please flatten or delete these tasks first.';
+    END IF;
+END $$;
+
+-- Drop the old constraint
+ALTER TABLE tasks DROP CONSTRAINT IF EXISTS tasks_depth_check;
+
+-- Add new stricter constraint (max depth 1)
+ALTER TABLE tasks ADD CONSTRAINT tasks_depth_check CHECK (depth >= 0 AND depth < 2);
+
+COMMENT ON COLUMN tasks.depth IS 'Task hierarchy depth: 0=orchestrator (top-level), 1=subtask. Maximum depth is 1.';
diff --git a/makima/migrations/20250108000000_add_completion_actions.sql b/makima/migrations/20250108000000_add_completion_actions.sql
new file mode 100644
index 0000000..ba025cf
--- /dev/null
+++ b/makima/migrations/20250108000000_add_completion_actions.sql
@@ -0,0 +1,13 @@
+-- Add completion action fields to tasks table
+-- These control what happens when a task completes successfully
+
+-- Path to user's local repository (outside ~/.makima)
+-- Overrides daemon's default_target_repo if set
+ALTER TABLE tasks ADD COLUMN target_repo_path VARCHAR(512);
+
+-- Action to perform on completion: "none", "branch", "merge", or "pr"
+-- - branch: Push task branch to target repo (default)
+-- - merge: Auto-merge branch into target_branch in target repo
+-- - pr: Create GitHub pull request
+-- - none: Keep work in worktree only
+ALTER TABLE tasks ADD COLUMN completion_action VARCHAR(32) DEFAULT 'branch';
diff --git a/makima/migrations/20250109000000_add_continue_from_task_id.sql b/makima/migrations/20250109000000_add_continue_from_task_id.sql
new file mode 100644
index 0000000..50bf8ca
--- /dev/null
+++ b/makima/migrations/20250109000000_add_continue_from_task_id.sql
@@ -0,0 +1,11 @@
+-- Add continue_from_task_id column for task continuation
+-- This allows subtasks to start from another task's worktree
+
+ALTER TABLE tasks ADD COLUMN continue_from_task_id UUID REFERENCES tasks(id) ON DELETE SET NULL;
+
+-- Add index for efficient lookup
+CREATE INDEX idx_tasks_continue_from ON tasks(continue_from_task_id) WHERE continue_from_task_id IS NOT NULL;
+
+-- Add copy_files column for copying specific files from parent worktree
+-- This is a JSON array of file paths relative to the worktree root
+ALTER TABLE tasks ADD COLUMN copy_files JSONB DEFAULT NULL;
diff --git a/makima/migrations/20250110000000_create_owners_table.sql b/makima/migrations/20250110000000_create_owners_table.sql
new file mode 100644
index 0000000..7b0d696
--- /dev/null
+++ b/makima/migrations/20250110000000_create_owners_table.sql
@@ -0,0 +1,25 @@
+-- Create owners table for multi-tenant support
+-- Owners are the logical entities that own resources (files, tasks, daemons)
+-- Users belong to owners via groups
+
+CREATE TABLE IF NOT EXISTS owners (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    name VARCHAR(255) NOT NULL,
+    -- 'personal' for individual users, 'organization' for teams
+    owner_type VARCHAR(32) NOT NULL DEFAULT 'personal',
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+
+    CONSTRAINT valid_owner_type CHECK (owner_type IN ('personal', 'organization'))
+);
+
+CREATE INDEX idx_owners_owner_type ON owners(owner_type);
+
+-- Trigger to update updated_at timestamp (reuse existing function)
+CREATE TRIGGER update_owners_updated_at
+    BEFORE UPDATE ON owners
+    FOR EACH ROW
+    EXECUTE FUNCTION update_updated_at_column();
+
+COMMENT ON TABLE owners IS 'Logical entities that own resources. Users access owners via group membership.';
+COMMENT ON COLUMN owners.owner_type IS 'Type of owner: personal (single user) or organization (team)';
diff --git a/makima/migrations/20250110000001_create_users_table.sql b/makima/migrations/20250110000001_create_users_table.sql
new file mode 100644
index 0000000..cb82807
--- /dev/null
+++ b/makima/migrations/20250110000001_create_users_table.sql
@@ -0,0 +1,27 @@
+-- Create users table as local mirror of Supabase auth.users
+-- This allows faster queries and storing additional user metadata
+
+CREATE TABLE IF NOT EXISTS users (
+    -- Matches Supabase auth.users(id)
+    id UUID PRIMARY KEY,
+    email VARCHAR(255) NOT NULL,
+    display_name VARCHAR(255),
+    avatar_url TEXT,
+    -- User's personal owner (created on signup)
+    default_owner_id UUID REFERENCES owners(id),
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE UNIQUE INDEX idx_users_email ON users(email);
+CREATE INDEX idx_users_default_owner_id ON users(default_owner_id);
+
+-- Trigger to update updated_at timestamp (reuse existing function)
+CREATE TRIGGER update_users_updated_at
+    BEFORE UPDATE ON users
+    FOR EACH ROW
+    EXECUTE FUNCTION update_updated_at_column();
+
+COMMENT ON TABLE users IS 'Local mirror of Supabase auth.users with additional metadata';
+COMMENT ON COLUMN users.id IS 'Matches Supabase auth.users(id) - set by trigger on signup';
+COMMENT ON COLUMN users.default_owner_id IS 'User personal owner, created automatically on signup';
diff --git a/makima/migrations/20250110000002_create_groups_tables.sql b/makima/migrations/20250110000002_create_groups_tables.sql
new file mode 100644
index 0000000..88e00a1
--- /dev/null
+++ b/makima/migrations/20250110000002_create_groups_tables.sql
@@ -0,0 +1,53 @@
+-- Create groups table for organizing users under an owner
+CREATE TABLE IF NOT EXISTS groups (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    owner_id UUID NOT NULL REFERENCES owners(id) ON DELETE CASCADE,
+    name VARCHAR(255) NOT NULL,
+    description TEXT,
+    -- True for the auto-created personal group on signup
+    is_default BOOLEAN NOT NULL DEFAULT false,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+
+    -- Each owner can only have one group with a given name
+    UNIQUE(owner_id, name)
+);
+
+CREATE INDEX idx_groups_owner_id ON groups(owner_id);
+CREATE INDEX idx_groups_is_default ON groups(is_default) WHERE is_default = true;
+
+CREATE TRIGGER update_groups_updated_at
+    BEFORE UPDATE ON groups
+    FOR EACH ROW
+    EXECUTE FUNCTION update_updated_at_column();
+
+-- Create group_members junction table
+CREATE TABLE IF NOT EXISTS group_members (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    group_id UUID NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
+    -- References Supabase auth.users(id) via our users table
+    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    -- Role within the group
+    role VARCHAR(32) NOT NULL DEFAULT 'member',
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+
+    -- Each user can only be in a group once
+    UNIQUE(group_id, user_id),
+
+    CONSTRAINT valid_role CHECK (role IN ('owner', 'admin', 'member'))
+);
+
+CREATE INDEX idx_group_members_group_id ON group_members(group_id);
+CREATE INDEX idx_group_members_user_id ON group_members(user_id);
+CREATE INDEX idx_group_members_role ON group_members(role);
+
+CREATE TRIGGER update_group_members_updated_at
+    BEFORE UPDATE ON group_members
+    FOR EACH ROW
+    EXECUTE FUNCTION update_updated_at_column();
+
+COMMENT ON TABLE groups IS 'Groups organize users under an owner for access control';
+COMMENT ON COLUMN groups.is_default IS 'True for the auto-created personal group on signup';
+COMMENT ON TABLE group_members IS 'Junction table for user membership in groups';
+COMMENT ON COLUMN group_members.role IS 'Role in group: owner (full control), admin (manage members), member (access only)';
diff --git a/makima/migrations/20250110000003_create_api_keys_table.sql b/makima/migrations/20250110000003_create_api_keys_table.sql
new file mode 100644
index 0000000..33832e8
--- /dev/null
+++ b/makima/migrations/20250110000003_create_api_keys_table.sql
@@ -0,0 +1,30 @@
+-- Create API keys table for programmatic access (daemons, CLI, integrations)
+CREATE TABLE IF NOT EXISTS api_keys (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    -- SHA-256 hash of the key (never store plain key)
+    key_hash VARCHAR(64) NOT NULL,
+    -- First 8 chars of key for display/identification (e.g., "mk_abc123")
+    key_prefix VARCHAR(16) NOT NULL,
+    -- User-provided label for the key
+    name VARCHAR(255),
+    -- Tracking
+    last_used_at TIMESTAMPTZ,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    -- Null if active, timestamp if revoked
+    revoked_at TIMESTAMPTZ
+);
+
+-- Index for key lookup (only active keys)
+CREATE INDEX idx_api_keys_key_hash ON api_keys(key_hash) WHERE revoked_at IS NULL;
+CREATE INDEX idx_api_keys_user_id ON api_keys(user_id);
+CREATE INDEX idx_api_keys_key_prefix ON api_keys(key_prefix);
+
+-- Partial unique index: only one active key per user
+CREATE UNIQUE INDEX idx_api_keys_one_active_per_user
+    ON api_keys(user_id) WHERE revoked_at IS NULL;
+
+COMMENT ON TABLE api_keys IS 'API keys for programmatic access. Max one active key per user.';
+COMMENT ON COLUMN api_keys.key_hash IS 'SHA-256 hash of the full API key';
+COMMENT ON COLUMN api_keys.key_prefix IS 'First 8 chars of key for identification (e.g., "mk_abc123")';
+COMMENT ON COLUMN api_keys.revoked_at IS 'NULL if active, timestamp when revoked';
diff --git a/makima/migrations/20250110000004_create_api_key_events_table.sql b/makima/migrations/20250110000004_create_api_key_events_table.sql
new file mode 100644
index 0000000..cb07bb3
--- /dev/null
+++ b/makima/migrations/20250110000004_create_api_key_events_table.sql
@@ -0,0 +1,20 @@
+-- Audit log for API key operations
+CREATE TABLE IF NOT EXISTS api_key_events (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    api_key_id UUID NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
+    event_type VARCHAR(32) NOT NULL,
+    -- Request metadata
+    ip_address INET,
+    user_agent TEXT,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+
+    CONSTRAINT valid_event_type CHECK (
+        event_type IN ('created', 'used', 'revoked', 'refreshed')
+    )
+);
+
+CREATE INDEX idx_api_key_events_api_key_id ON api_key_events(api_key_id);
+CREATE INDEX idx_api_key_events_created_at ON api_key_events(created_at DESC);
+CREATE INDEX idx_api_key_events_event_type ON api_key_events(event_type);
+
+COMMENT ON TABLE api_key_events IS 'Audit log for API key lifecycle events';
diff --git a/makima/migrations/20250110000005_create_placeholder_owners.sql b/makima/migrations/20250110000005_create_placeholder_owners.sql
new file mode 100644
index 0000000..05c946e
--- /dev/null
+++ b/makima/migrations/20250110000005_create_placeholder_owners.sql
@@ -0,0 +1,18 @@
+-- Create placeholder owners for existing data with hardcoded owner_ids
+-- These will be migrated to real owners when users sign up
+
+-- Note: The existing codebase uses the same UUID (00000000-0000-0000-0000-000000000002)
+-- for both files, tasks, and daemons based on the existing migrations.
+
+-- Placeholder owner for existing legacy data (used in files, tasks, daemons)
+INSERT INTO owners (id, name, owner_type)
+VALUES ('00000000-0000-0000-0000-000000000002', 'Legacy Owner', 'personal')
+ON CONFLICT (id) DO NOTHING;
+
+-- Additional placeholder in case files migration used different ID
+-- (keeping both for backward compatibility)
+INSERT INTO owners (id, name, owner_type)
+VALUES ('00000000-0000-0000-0000-000000000001', 'Legacy Files Owner', 'personal')
+ON CONFLICT (id) DO NOTHING;
+
+COMMENT ON TABLE owners IS 'NOTE: IDs 00000000-0000-0000-0000-00000000000[12] are legacy placeholders';
diff --git a/makima/migrations/20250110000006_add_owner_foreign_keys.sql b/makima/migrations/20250110000006_add_owner_foreign_keys.sql
new file mode 100644
index 0000000..c4fb53a
--- /dev/null
+++ b/makima/migrations/20250110000006_add_owner_foreign_keys.sql
@@ -0,0 +1,30 @@
+-- Add foreign key constraints to existing tables
+-- This links all data to the owners table
+-- NOTE: This migration depends on placeholder owners existing (migration 005)
+
+-- Files table
+ALTER TABLE files
+    ADD CONSTRAINT fk_files_owner
+        FOREIGN KEY (owner_id) REFERENCES owners(id);
+
+-- Tasks table
+ALTER TABLE tasks
+    ADD CONSTRAINT fk_tasks_owner
+        FOREIGN KEY (owner_id) REFERENCES owners(id);
+
+-- Daemons table
+ALTER TABLE daemons
+    ADD CONSTRAINT fk_daemons_owner
+        FOREIGN KEY (owner_id) REFERENCES owners(id);
+
+-- Mesh chat conversations table
+ALTER TABLE mesh_chat_conversations
+    ADD CONSTRAINT fk_mesh_chat_conversations_owner
+        FOREIGN KEY (owner_id) REFERENCES owners(id);
+
+-- Verify indexes exist (they should from original migrations)
+-- If not, add them for query performance:
+CREATE INDEX IF NOT EXISTS idx_files_owner_id ON files(owner_id);
+CREATE INDEX IF NOT EXISTS idx_tasks_owner_id ON tasks(owner_id);
+CREATE INDEX IF NOT EXISTS idx_daemons_owner_id ON daemons(owner_id);
+CREATE INDEX IF NOT EXISTS idx_mesh_chat_conversations_owner_id ON mesh_chat_conversations(owner_id);
diff --git a/makima/migrations/20250110000007_create_auth_trigger.sql b/makima/migrations/20250110000007_create_auth_trigger.sql
new file mode 100644
index 0000000..3b3fec9
--- /dev/null
+++ b/makima/migrations/20250110000007_create_auth_trigger.sql
@@ -0,0 +1,63 @@
+-- Function to handle new user signup from Supabase Auth
+-- This runs when a user is inserted into auth.users
+
+CREATE OR REPLACE FUNCTION public.handle_new_user()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+SECURITY DEFINER  -- Run with elevated privileges
+SET search_path = public
+AS $$
+DECLARE
+    new_owner_id UUID;
+    new_group_id UUID;
+    user_name TEXT;
+BEGIN
+    -- Extract display name from metadata, fallback to email
+    user_name := COALESCE(
+        NEW.raw_user_meta_data->>'full_name',
+        NEW.raw_user_meta_data->>'name',
+        split_part(NEW.email, '@', 1)
+    );
+
+    -- Create personal owner for this user
+    INSERT INTO public.owners (name, owner_type)
+    VALUES (user_name, 'personal')
+    RETURNING id INTO new_owner_id;
+
+    -- Create local user record
+    INSERT INTO public.users (id, email, display_name, default_owner_id)
+    VALUES (
+        NEW.id,
+        NEW.email,
+        user_name,
+        new_owner_id
+    );
+
+    -- Create default group for personal owner
+    INSERT INTO public.groups (owner_id, name, is_default)
+    VALUES (new_owner_id, 'Personal', true)
+    RETURNING id INTO new_group_id;
+
+    -- Add user as owner of their personal group
+    INSERT INTO public.group_members (group_id, user_id, role)
+    VALUES (new_group_id, NEW.id, 'owner');
+
+    RETURN NEW;
+EXCEPTION WHEN OTHERS THEN
+    -- Log error but don't fail the signup
+    RAISE WARNING 'handle_new_user failed for %: %', NEW.id, SQLERRM;
+    RETURN NEW;
+END;
+$$;
+
+-- NOTE: The trigger on auth.users must be created in the Supabase dashboard
+-- or via supabase CLI because we can't directly access auth schema in migrations.
+--
+-- Run this SQL in Supabase SQL Editor to create the trigger:
+--
+-- CREATE TRIGGER on_auth_user_created
+--     AFTER INSERT ON auth.users
+--     FOR EACH ROW
+--     EXECUTE FUNCTION public.handle_new_user();
+
+COMMENT ON FUNCTION handle_new_user IS 'Creates owner, user, group, and membership when a new user signs up via Supabase Auth';
diff --git a/makima/migrations/20250110000008_remove_owner_defaults.sql b/makima/migrations/20250110000008_remove_owner_defaults.sql
new file mode 100644
index 0000000..307f077
--- /dev/null
+++ b/makima/migrations/20250110000008_remove_owner_defaults.sql
@@ -0,0 +1,46 @@
+-- Remove default owner_id values from tables
+-- This should be done after application code is updated to always provide owner_id
+
+-- IMPORTANT: Only apply this migration after updating all code to provide owner_id
+-- Otherwise, inserts will fail
+
+-- Remove default values (they are no longer needed since code provides owner_id)
+ALTER TABLE files
+    ALTER COLUMN owner_id DROP DEFAULT;
+
+ALTER TABLE tasks
+    ALTER COLUMN owner_id DROP DEFAULT;
+
+ALTER TABLE daemons
+    ALTER COLUMN owner_id DROP DEFAULT;
+
+ALTER TABLE mesh_chat_conversations
+    ALTER COLUMN owner_id DROP DEFAULT;
+
+-- Make owner_id NOT NULL explicit (should already be, but ensure)
+-- Note: These columns were created with NOT NULL, so this is a no-op validation
+-- The SET NOT NULL will succeed if all existing rows have non-null values
+
+-- Files already has NOT NULL in original migration (20241222000000)
+-- Tasks already has NOT NULL in original migration (20250102000000)
+-- Daemons already has NOT NULL in original migration (20250102000000)
+-- Mesh chat conversations already has NOT NULL in original migration (20250104000000)
+
+-- However, we explicitly restate it for clarity and documentation
+ALTER TABLE files
+    ALTER COLUMN owner_id SET NOT NULL;
+
+ALTER TABLE tasks
+    ALTER COLUMN owner_id SET NOT NULL;
+
+ALTER TABLE daemons
+    ALTER COLUMN owner_id SET NOT NULL;
+
+ALTER TABLE mesh_chat_conversations
+    ALTER COLUMN owner_id SET NOT NULL;
+
+-- Add comments documenting the constraint
+COMMENT ON COLUMN files.owner_id IS 'Owner ID is required - no default value, must be provided by application';
+COMMENT ON COLUMN tasks.owner_id IS 'Owner ID is required - no default value, must be provided by application';
+COMMENT ON COLUMN daemons.owner_id IS 'Owner ID is required - no default value, must be provided by application';
+COMMENT ON COLUMN mesh_chat_conversations.owner_id IS 'Owner ID is required - no default value, must be provided by application';