Supabase Database Tables — Design PostgreSQL Schemas in Supabase

-- Create users table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE NOT NULL,
    full_name TEXT,
    avatar_url TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create profiles table with foreign key
CREATE TABLE profiles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    bio TEXT,
    website TEXT,
    company TEXT
);

-- Create files table
CREATE TABLE files (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    size_bytes BIGINT,
    mime_type TEXT,
    storage_path TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Add indexes
CREATE INDEX idx_files_user_id ON files(user_id);
CREATE INDEX idx_files_created_at ON files(created_at DESC);

# table_designer.py
# Understanding table editor options

def table_editor_options():
    options = {
        "Name": "Descriptive, snake_case table name (e.g., user_profiles)",
        "Columns": "Add columns with names and data types",
        "Default Value": "Set default values (e.g., NOW() for timestamps)",
        "Primary Key": "Choose a column or composite key as primary key",
        "Foreign Keys": "Reference columns from other tables",
        "Indexes": "Add performance indexes on frequently queried columns",
        "RLS Enabled": "Enable Row Level Security for the table",
        "Realtime Enabled": "Enable realtime subscriptions for this table",
    }
    
    print("Table Editor Options:")
    for option, desc in options.items():
        print(f"  {option:20s} | {desc}")

table_editor_options()

# data_types.py
# PostgreSQL data types for Supabase

def common_data_types():
    types = {
        "UUID": "Primary keys and unique identifiers (gen_random_uuid())",
        "TEXT": "Variable-length strings for names, descriptions, content",
        "VARCHAR(n)": "Fixed-length strings like emails or phone numbers",
        "INTEGER": "Whole numbers for counts and IDs",
        "BIGINT": "Large whole numbers for file sizes",
        "NUMERIC(p,s)": "Precise decimal numbers for prices",
        "BOOLEAN": "True/false values for flags",
        "TIMESTAMPTZ": "Timestamps with timezone for created_at/updated_at",
        "JSONB": "Flexible JSON data for dynamic fields",
        "BYTEA": "Binary data for small files or hashes",
    }
    
    print("Common PostgreSQL Data Types:")
    for dtype, desc in types.items():
        print(f"  {dtype:15s} | {desc}")

common_data_types()

CREATE TABLE file_sources (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    bucket TEXT NOT NULL DEFAULT 'files',
    path TEXT NOT NULL,
    size_bytes BIGINT,
    content_hash TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE processing_results (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    file_id UUID REFERENCES file_sources(id) ON DELETE CASCADE,
    status TEXT CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
    result_data JSONB,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    error_message TEXT
);

-- Enable RLS
ALTER TABLE file_sources ENABLE ROW LEVEL SECURITY;
ALTER TABLE processing_results ENABLE ROW LEVEL SECURITY;

def task_app_schema():
    tables = [
        "users (id UUID PK, email TEXT, full_name TEXT, avatar_url TEXT)",
        "projects (id UUID PK, user_id UUID FK->users, name TEXT, description TEXT, created_at TIMESTAMPTZ)",
        "tasks (id UUID PK, project_id UUID FK->projects, title TEXT, status TEXT, priority TEXT, assignee_id UUID FK->users, due_date DATE)",
        "comments (id UUID PK, task_id UUID FK->tasks, user_id UUID FK->users, content TEXT, created_at TIMESTAMPTZ)",
        "attachments (id UUID PK, task_id UUID FK->tasks, file_path TEXT, file_name TEXT, file_size BIGINT)",
    ]
    
    print("Task Management Schema:")
    for table in tables:
        print(f"  {table}")

task_app_schema()