DevFlow Snippets

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT, -- Stores hashed password for email/password auth
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    supabase_id UUID UNIQUE REFERENCES auth.users(id) ON DELETE CASCADE, -- Link to Supabase auth.users table
    stripe_customer_id TEXT UNIQUE, -- Stripe customer ID for billing
    subscription_status TEXT DEFAULT 'free' -- 'free', 'pro', 'cancelled'
);

CREATE TABLE snippets (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES public.users(id) ON DELETE CASCADE NOT NULL,
    content TEXT NOT NULL, -- The main content of the snippet (text, code, or link URL)
    type TEXT NOT NULL DEFAULT 'text', -- 'text', 'code', 'link'
    title TEXT, -- Optional short title, could be AI-generated or user-provided
    url TEXT, -- If type is 'link', stores the URL
    language TEXT, -- If type is 'code', stores the programming language (e.g., 'javascript', 'python')
    ai_tags TEXT[] DEFAULT ARRAY[]::TEXT[], -- AI-generated tags, e.g., ['bug', 'feature_idea', 'refactor']
    is_actionable BOOLEAN DEFAULT FALSE, -- User flag to mark snippet for follow-up
    is_archived BOOLEAN DEFAULT FALSE, -- Soft delete / archive flag
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for better query performance
CREATE INDEX idx_snippets_user_id ON snippets (user_id);
CREATE INDEX idx_snippets_ai_tags ON snippets USING GIN (ai_tags); -- GIN index for efficient array searching
CREATE INDEX idx_snippets_created_at ON snippets (created_at DESC);
CREATE INDEX idx_snippets_is_actionable ON snippets (is_actionable);