Supabase Database Builder

I have generated a normalized schema for your task management application. This includes user-isolated RLS policies, automated timestamp triggers, and optimized indexes.

Supabase Database Schema

-- Enable Extensions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Table: profiles
CREATE TABLE profiles (
  id uuid REFERENCES auth.users ON DELETE CASCADE PRIMARY KEY,
  username text UNIQUE,
  updated_at timestamptz DEFAULT now()
);

-- Table: projects
CREATE TABLE projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id uuid REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  title text NOT NULL,
  description text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Table: tasks
CREATE TABLE tasks (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid REFERENCES projects(id) ON DELETE CASCADE NOT NULL,
  title text NOT NULL,
  status text DEFAULT 'todo' CHECK (status IN ('todo', 'in_progress', 'done')),
  priority int DEFAULT 1,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Indexes
CREATE INDEX idx_projects_owner_id ON projects(owner_id);
CREATE INDEX idx_tasks_project_id ON tasks(project_id);

-- RLS Policies
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own profile" ON profiles FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users can manage own projects" ON projects FOR ALL USING (auth.uid() = owner_id);
CREATE POLICY "Users can manage tasks in their projects" ON tasks FOR ALL USING (
  EXISTS (
    SELECT 1 FROM projects 
    WHERE projects.id = tasks.project_id 
    AND projects.owner_id = auth.uid()
  )
);

-- Updated At Trigger Function
CREATE OR REPLACE FUNCTION handle_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER tr_projects_updated_at BEFORE UPDATE ON projects FOR EACH ROW EXECUTE PROCEDURE handle_updated_at();
CREATE TRIGGER tr_tasks_updated_at BEFORE UPDATE ON tasks FOR EACH ROW EXECUTE PROCEDURE handle_updated_at();

🔗 Relationships:

• profiles.id -> auth.users.id
• projects.owner_id -> profiles.id
• tasks.project_id -> projects.id

📝 Usage Examples:

-- Insert a new project
INSERT INTO projects (owner_id, title) VALUES (auth.uid(), 'Work Sprint');

-- Get all tasks for a specific project with owner check
SELECT t.* FROM tasks t
JOIN projects p ON t.project_id = p.id
WHERE p.owner_id = auth.uid();

Next steps

1. Copy the SQL block into your Supabase SQL Editor.
2. Run the migration to create tables and RLS policies.
3. Test the RLS policies by logging in as a user and attempting to query tasks.