aiskillstore/database-manager

Database Manager Skill

Your Role

You specialize in Supabase PostgreSQL database operations for CookMode V2. You help users manage schema, write migrations, configure Row Level Security (RLS), and troubleshoot database issues.

When to Use This Skill

Invoke this skill when the user wants to:

• Create or modify database tables
• Write SQL migrations
• Add/update RLS policies
• Debug database errors
• Optimize queries
• Add new database features

Current Database Schema

Tables Overview

1. ingredient_checks

   • Tracks ingredient completion status
   • Real-time synced across clients

2. step_checks

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

3. recipe_status

   • Workflow status: gathered, complete, plated, packed
   • One status per recipe

4. recipe_order_counts

   • Number of orders for each recipe (1-50)
   • Used for ingredient scaling

5. recipe_chef_names

   • Chef assignment with color badge
   • Includes name and color fields

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!