archived-sql-files.md•26.8 kB
# Archived SQL Files
All SQL files moved to archive after consolidation into `00-consolidated-minimal.sql`
## 01-original-schema.sql
```sql
-- ORIGINAL SCHEMA - Run this first
-- This is your working database schema
-- Enable extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Original tables that work
CREATE TABLE IF NOT EXISTS public.user_profiles (
id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
display_name TEXT,
role TEXT CHECK (role IN ('owner', 'worker')) DEFAULT 'worker',
is_active BOOLEAN DEFAULT true,
created_by UUID REFERENCES auth.users(id),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS public.products (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
name TEXT NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
cost DECIMAL(10,2),
stock INTEGER NOT NULL DEFAULT 0,
category TEXT,
image_url TEXT,
is_active BOOLEAN DEFAULT true,
created_by UUID REFERENCES auth.users(id),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS public.sales (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
sale_number SERIAL,
items JSONB NOT NULL,
subtotal DECIMAL(10,2) NOT NULL,
tax DECIMAL(10,2) DEFAULT 0,
total DECIMAL(10,2) NOT NULL,
payment DECIMAL(10,2) NOT NULL,
change_due DECIMAL(10,2) NOT NULL DEFAULT 0,
payment_method TEXT DEFAULT 'cash',
worker_id UUID REFERENCES auth.users(id),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS public.expenses (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
description TEXT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
category TEXT NOT NULL DEFAULT 'general',
receipt_url TEXT,
worker_id UUID REFERENCES auth.users(id),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
```
## 02-notes-table.sql
```sql
-- NOTES TABLE - Simple version without status column
CREATE TABLE IF NOT EXISTS public.notes (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
content TEXT NOT NULL,
user_role TEXT CHECK (user_role IN ('owner', 'worker')),
parsed_data JSONB,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
```
## 03-minimal-additions.sql
```sql
-- MINIMAL ADDITIONS - Only what's needed for AI workflow
CREATE TABLE IF NOT EXISTS public.branch_stock (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
branch_id UUID NOT NULL,
product_name TEXT NOT NULL,
allocated NUMERIC NOT NULL DEFAULT 0,
used NUMERIC NOT NULL DEFAULT 0,
leftover NUMERIC NOT NULL DEFAULT 0,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS public.summaries (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
branch_id UUID,
date DATE NOT NULL,
summary JSONB NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
```
## 04-missing-chatgpt-tables.sql
```sql
-- Missing tables from original ChatGPT plan
-- Enable pgvector for embeddings
CREATE EXTENSION IF NOT EXISTS vector;
-- Owners table
CREATE TABLE IF NOT EXISTS public.owners (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Branches table
CREATE TABLE IF NOT EXISTS public.branches (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id UUID REFERENCES owners(id),
name TEXT NOT NULL,
location TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Operations table (from ChatGPT plan)
CREATE TABLE IF NOT EXISTS public.operations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
local_uuid TEXT UNIQUE NOT NULL,
lot_id UUID,
branch_id UUID REFERENCES branches(id),
worker_id UUID REFERENCES user_profiles(id),
op_type TEXT NOT NULL CHECK (op_type IN ('purchase', 'receive', 'package', 'transfer_out', 'transfer_in', 'cook', 'sale', 'waste')),
quantity_parts INTEGER,
quantity_bags INTEGER,
metadata JSONB,
created_at TIMESTAMPTZ DEFAULT NOW(),
synced BOOLEAN DEFAULT FALSE
);
-- Lots table (from ChatGPT plan)
CREATE TABLE IF NOT EXISTS public.lots (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
product_id UUID REFERENCES products(id),
origin_branch_id UUID REFERENCES branches(id),
created_at TIMESTAMPTZ DEFAULT NOW(),
bag_count INTEGER,
parts_per_bag INTEGER,
lot_tag TEXT
);
-- Note embeddings for RAG
CREATE TABLE IF NOT EXISTS public.note_embeddings (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
note_id UUID REFERENCES notes(id),
embedding VECTOR(1536),
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Indexes
CREATE INDEX IF NOT EXISTS idx_operations_type ON operations(op_type);
CREATE INDEX IF NOT EXISTS idx_operations_branch ON operations(branch_id);
CREATE INDEX IF NOT EXISTS idx_lots_product ON lots(product_id);
CREATE INDEX IF NOT EXISTS idx_note_embeddings_note ON note_embeddings(note_id);
```
## 05-similarity-search-function.sql
```sql
-- Similarity search function for RAG (ChatGPT plan requirement)
CREATE OR REPLACE FUNCTION match_notes(
query_embedding VECTOR(1536),
match_threshold FLOAT DEFAULT 0.7,
match_count INT DEFAULT 5
)
RETURNS TABLE (
id UUID,
content TEXT,
similarity FLOAT
)
LANGUAGE SQL STABLE
AS $$
SELECT
n.id,
n.content,
1 - (ne.embedding <=> query_embedding) AS similarity
FROM notes n
JOIN note_embeddings ne ON n.id = ne.note_id
WHERE 1 - (ne.embedding <=> query_embedding) > match_threshold
ORDER BY ne.embedding <=> query_embedding
LIMIT match_count;
$$;
```
## add-missing-tables.sql
```sql
-- Missing tables for full implementation
CREATE TABLE IF NOT EXISTS branch_stock (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
branch_id uuid NOT NULL,
product_name text NOT NULL,
allocated numeric NOT NULL DEFAULT 0,
used numeric NOT NULL DEFAULT 0,
leftover numeric NOT NULL DEFAULT 0,
created_at timestamptz DEFAULT now()
);
CREATE TABLE IF NOT EXISTS leftovers (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
branch_id uuid NOT NULL,
product_name text NOT NULL,
quantity numeric NOT NULL,
price_per_unit numeric,
date date NOT NULL DEFAULT CURRENT_DATE,
created_at timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_branch_stock_branch ON branch_stock(branch_id);
CREATE INDEX IF NOT EXISTS idx_leftovers_date ON leftovers(date);
```
## create-chat-history-table.sql
```sql
-- Create chat_history table for storing AI assistant conversations
CREATE TABLE IF NOT EXISTS chat_history (
id BIGSERIAL PRIMARY KEY,
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
message TEXT NOT NULL,
sender VARCHAR(10) NOT NULL CHECK (sender IN ('user', 'ai')),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Create index for faster queries
CREATE INDEX IF NOT EXISTS idx_chat_history_user_id ON chat_history(user_id);
CREATE INDEX IF NOT EXISTS idx_chat_history_created_at ON chat_history(created_at);
-- Enable RLS
ALTER TABLE chat_history ENABLE ROW LEVEL SECURITY;
-- Create policy for users to access only their own chat history
CREATE POLICY "Users can access their own chat history" ON chat_history
FOR ALL USING (auth.uid() = user_id);
```
## create-summaries-table.sql
```sql
-- Summaries table for daily aggregated insights
CREATE TABLE IF NOT EXISTS summaries (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
branch_id uuid,
date date NOT NULL,
summary jsonb NOT NULL,
created_at timestamptz DEFAULT now()
);
-- AI audit logs for tracking interactions
CREATE TABLE IF NOT EXISTS ai_audit_logs (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
prompt text NOT NULL,
response text,
model_used text DEFAULT 'gemini-2.0-flash',
user_id uuid,
created_at timestamptz DEFAULT now()
);
```
## create-workers-table.sql
```sql
-- Create optimized workers table for fast lookups (WORKERS ONLY)
CREATE TABLE IF NOT EXISTS workers (
id UUID PRIMARY KEY,
name TEXT NOT NULL,
email TEXT,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Populate ONLY workers from user_profiles
INSERT INTO workers (id, name, email, is_active)
SELECT
id,
COALESCE(display_name, split_part(email, '@', 1), 'Worker') as name,
email,
true
FROM user_profiles
WHERE role = 'worker'
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
email = EXCLUDED.email;
-- Create trigger to auto-sync with user_profiles
CREATE OR REPLACE FUNCTION sync_worker_data()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' AND NEW.role = 'worker' THEN
INSERT INTO workers (id, name, email)
VALUES (
NEW.id,
COALESCE(NEW.display_name, split_part(NEW.email, '@', 1), 'Worker'),
NEW.email
);
RETURN NEW;
END IF;
IF TG_OP = 'UPDATE' THEN
IF NEW.role = 'worker' THEN
INSERT INTO workers (id, name, email)
VALUES (
NEW.id,
COALESCE(NEW.display_name, split_part(NEW.email, '@', 1), 'Worker'),
NEW.email
)
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
email = EXCLUDED.email;
ELSE
-- Remove from workers if role changed from worker
DELETE FROM workers WHERE id = NEW.id;
END IF;
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Create triggers
DROP TRIGGER IF EXISTS sync_workers_on_profile_change ON user_profiles;
CREATE TRIGGER sync_workers_on_profile_change
AFTER INSERT OR UPDATE ON user_profiles
FOR EACH ROW EXECUTE FUNCTION sync_worker_data();
```
## database-improvements.sql
```sql
-- DATABASE PERFORMANCE IMPROVEMENTS
-- Run this after the main setup
-- 1. Add composite indexes for common queries
CREATE INDEX IF NOT EXISTS idx_sales_worker_date ON public.sales(worker_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_expenses_worker_date ON public.expenses(worker_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_sales_date_total ON public.sales(created_at DESC, total);
-- 2. Create materialized view for dashboard (faster queries)
CREATE MATERIALIZED VIEW IF NOT EXISTS public.dashboard_summary AS
SELECT
DATE(created_at) as date,
COUNT(*) as transaction_count,
SUM(total) as daily_revenue,
AVG(total) as avg_transaction,
COUNT(DISTINCT worker_id) as active_workers
FROM public.sales
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY date DESC;
-- 3. Create function to refresh dashboard data
CREATE OR REPLACE FUNCTION refresh_dashboard_summary()
RETURNS void AS $$
BEGIN
REFRESH MATERIALIZED VIEW public.dashboard_summary;
END;
$$ LANGUAGE plpgsql;
-- 4. Add AI conversation storage with proper indexing
CREATE TABLE IF NOT EXISTS public.ai_sessions (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id UUID REFERENCES auth.users(id),
session_data JSONB,
last_activity TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_ai_sessions_user_activity ON public.ai_sessions(user_id, last_activity DESC);
-- 5. Create optimized business data function
CREATE OR REPLACE FUNCTION get_business_summary(
user_id UUID DEFAULT auth.uid(),
days_back INTEGER DEFAULT 30
) RETURNS JSONB AS $$
DECLARE
result JSONB;
BEGIN
SELECT jsonb_build_object(
'revenue', COALESCE(SUM(s.total), 0),
'transactions', COUNT(s.id),
'expenses', COALESCE(SUM(e.amount), 0),
'top_products', (
SELECT jsonb_agg(jsonb_build_object('name', item->>'productName', 'count', COUNT(*)))
FROM public.sales s2, jsonb_array_elements(s2.items) item
WHERE s2.created_at >= CURRENT_DATE - INTERVAL '1 day' * days_back
GROUP BY item->>'productName'
ORDER BY COUNT(*) DESC
LIMIT 5
)
) INTO result
FROM public.sales s
LEFT JOIN public.expenses e ON DATE(s.created_at) = DATE(e.created_at)
WHERE s.created_at >= CURRENT_DATE - INTERVAL '1 day' * days_back;
RETURN COALESCE(result, '{}'::jsonb);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
```
## database-schema.sql
```sql
-- Database schema for ChatGPT plan implementation
-- Operations and summaries tables for stock management
-- Operations table for storing all stock operations
CREATE TABLE IF NOT EXISTS operations (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
local_uuid uuid UNIQUE NOT NULL,
type text NOT NULL CHECK (type IN ('purchase', 'production', 'transfer', 'cook', 'sale')),
data jsonb NOT NULL,
timestamp timestamptz NOT NULL,
synced_at timestamptz DEFAULT now(),
created_at timestamptz DEFAULT now()
);
-- Lots table for tracking stock batches
CREATE TABLE IF NOT EXISTS lots (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
product_name text NOT NULL,
quantity numeric NOT NULL,
unit text NOT NULL DEFAULT 'kg',
received_date date NOT NULL,
supplier text,
notes text,
created_at timestamptz DEFAULT now()
);
-- Summaries table for daily aggregated data
CREATE TABLE IF NOT EXISTS summaries (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
branch_id uuid,
date date NOT NULL,
summary jsonb NOT NULL,
created_at timestamptz DEFAULT now(),
UNIQUE(branch_id, date)
);
-- Notes table for free-text input
CREATE TABLE IF NOT EXISTS notes (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
content text NOT NULL,
parsed_operations jsonb,
status text DEFAULT 'pending' CHECK (status IN ('pending', 'parsed', 'synced')),
created_at timestamptz DEFAULT now()
);
-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_operations_type ON operations(type);
CREATE INDEX IF NOT EXISTS idx_operations_timestamp ON operations(timestamp);
CREATE INDEX IF NOT EXISTS idx_lots_received_date ON lots(received_date);
CREATE INDEX IF NOT EXISTS idx_summaries_date ON summaries(date);
CREATE INDEX IF NOT EXISTS idx_notes_status ON notes(status);
```
## fix-database-policies.sql
```sql
-- FIX INFINITE RECURSION IN RLS POLICIES
-- This fixes the core backend database connection issues
-- Drop all existing policies that cause recursion
DROP POLICY IF EXISTS "Users can manage own profile" ON public.user_profiles;
DROP POLICY IF EXISTS "Owners can manage all profiles" ON public.user_profiles;
DROP POLICY IF EXISTS "Owners can manage all products" ON public.products;
DROP POLICY IF EXISTS "Workers can view active products" ON public.products;
DROP POLICY IF EXISTS "Workers can create sales" ON public.sales;
DROP POLICY IF EXISTS "Workers can view own sales" ON public.sales;
DROP POLICY IF EXISTS "Owners can view all sales" ON public.sales;
DROP POLICY IF EXISTS "Workers can create expenses" ON public.expenses;
DROP POLICY IF EXISTS "Workers can view own expenses" ON public.expenses;
DROP POLICY IF EXISTS "Owners can view all expenses" ON public.expenses;
DROP POLICY IF EXISTS "Users can manage own notes" ON public.notes;
DROP POLICY IF EXISTS "Owners can view all notes" ON public.notes;
DROP POLICY IF EXISTS "Users can manage own AI data" ON public.ai_analysis;
DROP POLICY IF EXISTS "Users can manage own AI conversations" ON public.ai_conversations;
-- Create simple, non-recursive policies
-- User profiles - simple access
CREATE POLICY "Enable read access for authenticated users" ON public.user_profiles
FOR SELECT USING (auth.uid() IS NOT NULL);
CREATE POLICY "Enable insert for authenticated users" ON public.user_profiles
FOR INSERT WITH CHECK (auth.uid() = id);
CREATE POLICY "Enable update for own profile" ON public.user_profiles
FOR UPDATE USING (auth.uid() = id);
-- Products - simple access
CREATE POLICY "Enable read access for all authenticated users" ON public.products
FOR SELECT USING (auth.uid() IS NOT NULL);
CREATE POLICY "Enable insert for authenticated users" ON public.products
FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);
CREATE POLICY "Enable update for authenticated users" ON public.products
FOR UPDATE USING (auth.uid() IS NOT NULL);
-- Sales - simple access
CREATE POLICY "Enable read access for authenticated users" ON public.sales
FOR SELECT USING (auth.uid() IS NOT NULL);
CREATE POLICY "Enable insert for authenticated users" ON public.sales
FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);
-- Expenses - simple access
CREATE POLICY "Enable read access for authenticated users" ON public.expenses
FOR SELECT USING (auth.uid() IS NOT NULL);
CREATE POLICY "Enable insert for authenticated users" ON public.expenses
FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);
-- Notes - simple access
CREATE POLICY "Enable all access for authenticated users" ON public.notes
FOR ALL USING (auth.uid() IS NOT NULL);
-- AI tables - simple access
CREATE POLICY "Enable all access for authenticated users" ON public.ai_analysis
FOR ALL USING (auth.uid() IS NOT NULL);
CREATE POLICY "Enable all access for authenticated users" ON public.ai_conversations
FOR ALL USING (auth.uid() IS NOT NULL);
```
## fix-database-schema.sql
```sql
-- FIX DATABASE SCHEMA ISSUES
-- Ensure all required columns exist with correct names
-- Fix sales table - ensure change_due column exists
ALTER TABLE public.sales
ADD COLUMN IF NOT EXISTS change_due DECIMAL(10,2) DEFAULT 0;
-- Update any existing 'change' column to 'change_due'
DO $$
BEGIN
IF EXISTS (SELECT 1 FROM information_schema.columns
WHERE table_name = 'sales' AND column_name = 'change') THEN
ALTER TABLE public.sales RENAME COLUMN change TO change_due;
END IF;
END $$;
-- Ensure worker_name column exists in sales
ALTER TABLE public.sales
ADD COLUMN IF NOT EXISTS worker_name TEXT;
-- Ensure worker_name column exists in expenses
ALTER TABLE public.expenses
ADD COLUMN IF NOT EXISTS worker_name TEXT;
-- Ensure category column exists in expenses
ALTER TABLE public.expenses
ADD COLUMN IF NOT EXISTS category TEXT DEFAULT 'general';
-- Fix any missing constraints
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints
WHERE constraint_name = 'sales_change_due_check' AND table_name = 'sales') THEN
ALTER TABLE public.sales ADD CONSTRAINT sales_change_due_check CHECK (change_due >= 0);
END IF;
END $$;
-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_sales_worker_id_date ON public.sales(worker_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_expenses_worker_id_date ON public.expenses(worker_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_products_active ON public.products(is_active) WHERE is_active = true;
```
## fix-foreign-keys.sql
```sql
-- Fix foreign key relationships for user_profiles table
-- First, ensure user_profiles table exists with correct structure
CREATE TABLE IF NOT EXISTS user_profiles (
id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
email TEXT,
display_name TEXT,
role TEXT DEFAULT 'worker',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Add foreign key constraints if they don't exist
DO $$
BEGIN
-- Add foreign key for sales.worker_id -> user_profiles.id
IF NOT EXISTS (
SELECT 1 FROM information_schema.table_constraints
WHERE constraint_name = 'sales_worker_id_fkey'
AND table_name = 'sales'
) THEN
ALTER TABLE sales
ADD CONSTRAINT sales_worker_id_fkey
FOREIGN KEY (worker_id) REFERENCES user_profiles(id);
END IF;
-- Add foreign key for expenses.worker_id -> user_profiles.id
IF NOT EXISTS (
SELECT 1 FROM information_schema.table_constraints
WHERE constraint_name = 'expenses_worker_id_fkey'
AND table_name = 'expenses'
) THEN
ALTER TABLE expenses
ADD CONSTRAINT expenses_worker_id_fkey
FOREIGN KEY (worker_id) REFERENCES user_profiles(id);
END IF;
END $$;
-- Create function to automatically populate user_profiles when auth.users is created
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO public.user_profiles (id, email, display_name, role)
VALUES (
NEW.id,
NEW.email,
COALESCE(NEW.raw_user_meta_data->>'display_name', NEW.raw_user_meta_data->>'full_name', split_part(NEW.email, '@', 1)),
COALESCE(NEW.raw_user_meta_data->>'role', 'worker')
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Create trigger to automatically create user profile
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();
-- Update existing records to have proper worker_name from user_profiles
UPDATE sales
SET worker_name = COALESCE(up.display_name, split_part(up.email, '@', 1), 'Worker')
FROM user_profiles up
WHERE sales.worker_id = up.id AND (sales.worker_name IS NULL OR sales.worker_name = 'Unknown');
UPDATE expenses
SET worker_name = COALESCE(up.display_name, split_part(up.email, '@', 1), 'Worker')
FROM user_profiles up
WHERE expenses.worker_id = up.id AND (expenses.worker_name IS NULL OR expenses.worker_name = 'Unknown');
```
## fix-user-profiles.sql
```sql
-- Fix user_profiles table population
-- Run this in Supabase SQL Editor
-- First, check if any users exist in auth.users but not in user_profiles
INSERT INTO public.user_profiles (id, email, display_name, role)
SELECT
au.id,
au.email,
COALESCE(
au.raw_user_meta_data->>'display_name',
au.raw_user_meta_data->>'full_name',
split_part(au.email, '@', 1)
) as display_name,
CASE
WHEN (SELECT COUNT(*) FROM public.user_profiles WHERE role = 'owner') = 0 THEN 'owner'
ELSE COALESCE(au.raw_user_meta_data->>'role', 'worker')
END as role
FROM auth.users au
WHERE au.id NOT IN (SELECT id FROM public.user_profiles)
ON CONFLICT (id) DO NOTHING;
-- Update existing profiles with proper display names if missing
UPDATE public.user_profiles
SET display_name = COALESCE(
(SELECT au.raw_user_meta_data->>'display_name' FROM auth.users au WHERE au.id = user_profiles.id),
(SELECT au.raw_user_meta_data->>'full_name' FROM auth.users au WHERE au.id = user_profiles.id),
(SELECT split_part(au.email, '@', 1) FROM auth.users au WHERE au.id = user_profiles.id),
'User'
)
WHERE display_name IS NULL OR display_name = '';
```
## fix-worker-names.sql
```sql
-- Fix worker_name fields in all tables to use display_name
-- Run this in Supabase SQL Editor
-- Update expenses table - set worker_name from user_profiles display_name
UPDATE public.expenses
SET worker_name = up.display_name
FROM public.user_profiles up
WHERE expenses.worker_id = up.id
AND (expenses.worker_name IS NULL OR expenses.worker_name = 'Worker' OR expenses.worker_name = 'User');
-- Update sales table - set worker_name from user_profiles display_name
UPDATE public.sales
SET worker_name = up.display_name
FROM public.user_profiles up
WHERE sales.worker_id = up.id
AND (sales.worker_name IS NULL OR sales.worker_name = 'Worker' OR sales.worker_name = 'User');
-- Create function to auto-update worker_name on insert/update
CREATE OR REPLACE FUNCTION update_worker_name()
RETURNS TRIGGER AS $$
BEGIN
-- Get display_name from user_profiles
SELECT display_name INTO NEW.worker_name
FROM public.user_profiles
WHERE id = NEW.worker_id;
-- Fallback if no profile found
IF NEW.worker_name IS NULL THEN
NEW.worker_name := 'Worker';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Add triggers to auto-update worker_name
DROP TRIGGER IF EXISTS update_expenses_worker_name ON public.expenses;
CREATE TRIGGER update_expenses_worker_name
BEFORE INSERT OR UPDATE ON public.expenses
FOR EACH ROW EXECUTE FUNCTION update_worker_name();
DROP TRIGGER IF EXISTS update_sales_worker_name ON public.sales;
CREATE TRIGGER update_sales_worker_name
BEFORE INSERT OR UPDATE ON public.sales
FOR EACH ROW EXECUTE FUNCTION update_worker_name();
```
## fix-worker-product-access.sql
```sql
-- Fix worker access to products table
-- Workers need to see products to record sales
-- Drop existing restrictive policies
DROP POLICY IF EXISTS "products_policy" ON products;
-- Create simple policy allowing all authenticated users to read products
CREATE POLICY "products_read_policy" ON products
FOR SELECT
TO authenticated
USING (true);
-- Ensure workers can also read user_profiles for worker names
DROP POLICY IF EXISTS "user_profiles_policy" ON user_profiles;
CREATE POLICY "user_profiles_read_policy" ON user_profiles
FOR SELECT
TO authenticated
USING (true);
-- Allow workers to insert sales
DROP POLICY IF EXISTS "sales_policy" ON sales;
CREATE POLICY "sales_insert_policy" ON sales
FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = worker_id);
CREATE POLICY "sales_read_policy" ON sales
FOR SELECT
TO authenticated
USING (true);
-- Allow workers to insert expenses
DROP POLICY IF EXISTS "expenses_policy" ON expenses;
CREATE POLICY "expenses_insert_policy" ON expenses
FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = worker_id);
CREATE POLICY "expenses_read_policy" ON expenses
FOR SELECT
TO authenticated
USING (true);
```
## simple-notes-table.sql
```sql
-- Notes table with AI parsing support - Part B
CREATE TABLE IF NOT EXISTS notes (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
content text NOT NULL,
user_role text CHECK (user_role IN ('owner', 'worker')),
parsed_data jsonb,
status text DEFAULT 'pending' CHECK (status IN ('pending', 'parsed', 'applied')),
created_at timestamptz DEFAULT now()
);
```
## update-existing-users.sql
```sql
-- Update existing users with display names
-- Run this in Supabase SQL Editor
-- Update auth.users metadata for existing users who don't have display_name
UPDATE auth.users
SET raw_user_meta_data = raw_user_meta_data || jsonb_build_object('display_name', split_part(email, '@', 1))
WHERE raw_user_meta_data->>'display_name' IS NULL;
-- Also populate user_profiles table
INSERT INTO public.user_profiles (id, email, display_name, role)
SELECT
au.id,
au.email,
COALESCE(
au.raw_user_meta_data->>'display_name',
au.raw_user_meta_data->>'full_name',
split_part(au.email, '@', 1)
) as display_name,
CASE
WHEN (SELECT COUNT(*) FROM public.user_profiles WHERE role = 'owner') = 0 THEN 'owner'
ELSE COALESCE(au.raw_user_meta_data->>'role', 'worker')
END as role
FROM auth.users au
WHERE au.id NOT IN (SELECT id FROM public.user_profiles)
ON CONFLICT (id) DO UPDATE SET
display_name = EXCLUDED.display_name,
email = EXCLUDED.email;
```
---
**All files archived. Use `00-consolidated-minimal.sql` for new deployments.**