Database Manager

• Tracks instruction step completion
• Real-time synced across clients

Schema Files

• Primary: /supabase-schema.sql
• Migrations: /supabase-migration-*.sql

Table Schemas

ingredient_checks

CREATE TABLE ingredient_checks (
    recipe_slug TEXT NOT NULL,
    ingredient_index INTEGER NOT NULL,
    component_name TEXT NOT NULL,
    ingredient_text TEXT,
    is_checked BOOLEAN DEFAULT FALSE,
    updated_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (recipe_slug, ingredient_index, component_name)
);

step_checks

CREATE TABLE step_checks (
    recipe_slug TEXT NOT NULL,
    step_index INTEGER NOT NULL,
    step_text TEXT,
    is_checked BOOLEAN DEFAULT FALSE,
    updated_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (recipe_slug, step_index)
);

recipe_status

CREATE TABLE recipe_status (
    recipe_slug TEXT PRIMARY KEY,
    status TEXT CHECK (status IN ('gathered', 'complete', 'plated', 'packed')),
    updated_at TIMESTAMP DEFAULT NOW()
);

recipe_order_counts

CREATE TABLE recipe_order_counts (
    recipe_slug TEXT PRIMARY KEY,
    order_count INTEGER DEFAULT 1 CHECK (order_count >= 1 AND order_count <= 50),
    updated_at TIMESTAMP DEFAULT NOW()
);

recipe_chef_names

CREATE TABLE recipe_chef_names (
    recipe_slug TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    color TEXT NOT NULL DEFAULT '#9333ea',
    updated_at TIMESTAMP DEFAULT NOW()
);

Row Level Security (RLS)

CookMode V2 currently uses permissive RLS - all users can read/write all data.

-- Enable RLS
ALTER TABLE ingredient_checks ENABLE ROW LEVEL SECURITY;

-- Allow all operations (current policy)
CREATE POLICY "Enable all access" ON ingredient_checks
    FOR ALL USING (true);

Note: This is suitable for trusted kitchen environments. For multi-tenant setups, implement user-specific policies.

Real-Time Subscriptions

Tables with real-time sync enabled:

• ingredient_checks
• step_checks
• recipe_status
• recipe_order_counts
• recipe_chef_names

Configured in /js/hooks/useRealtime.js:15-80

Migration Best Practices

Creating a Migration

1. Name convention: supabase-migration-{feature-name}.sql
2. Include rollback: Add comments for manual rollback steps
3. Test locally: Verify migration before applying

Migration Template

-- Migration: Add new feature
-- Date: 2025-01-XX
-- Description: Brief description of changes

-- ============================================
-- NEW TABLE
-- ============================================

CREATE TABLE IF NOT EXISTS new_table (
    id SERIAL PRIMARY KEY,
    recipe_slug TEXT NOT NULL,
    data TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- INDEXES
-- ============================================

CREATE INDEX idx_new_table_recipe ON new_table(recipe_slug);

-- ============================================
-- ROW LEVEL SECURITY
-- ============================================

ALTER TABLE new_table ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Enable all access" ON new_table
    FOR ALL USING (true);

-- ============================================
-- ROLLBACK (Manual)
-- ============================================
-- DROP TABLE IF EXISTS new_table CASCADE;

Common Database Operations

Adding a New Table

1. Define schema with constraints
2. Add indexes for performance
3. Enable RLS and create policies
4. Document in this skill
5. Update hooks if real-time needed

Modifying Existing Table

-- Add new column
ALTER TABLE recipe_status
ADD COLUMN priority INTEGER DEFAULT 0;

-- Modify column
ALTER TABLE recipe_chef_names
ALTER COLUMN color SET DEFAULT '#10b981';

-- Add constraint
ALTER TABLE recipe_order_counts
ADD CONSTRAINT valid_count CHECK (order_count > 0);

Querying Data

Use Supabase client in hooks:

// Select
const { data, error } = await supabase
    .from('recipe_status')
    .select('*')
    .eq('recipe_slug', 'truffle-mashed-potatoes');

// Upsert
const { error } = await supabase
    .from('recipe_order_counts')
    .upsert({
        recipe_slug: 'chocolate-cake',
        order_count: 5
    }, {
        onConflict: 'recipe_slug'
    });

// Delete
const { error } = await supabase
    .from('step_checks')
    .delete()
    .eq('recipe_slug', 'old-recipe');

Database Connection

Configuration

Supabase connection configured in /js/hooks/useSupabase.js:

• URL: From environment or config
• Anon Key: Public key for client-side access
• Real-time: WebSocket connection for live updates

Initialization Flow

1. useSupabase() creates client
2. Returns {supabase, isSupabaseConnected}
3. App checks connection before operations

Troubleshooting

Common Issues

Issue: Changes not syncing

• Check real-time subscription in useRealtime.js
• Verify table has RLS policy
• Check browser console for Supabase errors

Issue: Constraint violation

• Review table constraints (CHECK, UNIQUE, FK)
• Validate data before insert/update

Issue: RLS blocking queries

• Verify policies allow operation
• Check user authentication status

Debug Queries

-- Check table structure
\d+ ingredient_checks

-- View all policies
SELECT * FROM pg_policies WHERE tablename = 'recipe_status';

-- Check real-time configuration
SELECT * FROM pg_publication_tables WHERE pubname = 'supabase_realtime';

Performance Considerations

Indexes

Current indexes target:

• Primary keys (automatic)
• Foreign key columns
• Frequently filtered columns (recipe_slug)

Optimistic Updates

UI updates immediately, syncs to DB asynchronously:

// Optimistic update
setCompletedIngredients(prev => ({ ...prev, [key]: true }));

// Then sync to Supabase
await supabase.from('ingredient_checks').upsert(...);

Schema Evolution

When modifying schema:

1. Never drop data without backup
2. Use migrations for all changes
3. Test with realistic data volumes
4. Update hooks if data access changes
5. Document changes in CLAUDE.md

Example: Adding Recipe Notes Table

-- Migration: Add recipe notes feature
CREATE TABLE recipe_notes (
    id SERIAL PRIMARY KEY,
    recipe_slug TEXT NOT NULL,
    note_text TEXT NOT NULL,
    created_by TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_recipe_notes_slug ON recipe_notes(recipe_slug);

ALTER TABLE recipe_notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable all access" ON recipe_notes FOR ALL USING (true);

Then update /js/hooks/useRecipeData.js to fetch and manage notes.

Remember: Keep the database simple and cook-friendly, just like the UI!