diff options
Diffstat (limited to 'makima/migrations')
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'; |