Standardmäßig ist der Prompt ausgewählt, der zuerst die Quelle prüft. Sie können zu einem direkten Befehl wechseln oder eine lokale Kopie herunterladen.
Quelldateien prüfen
Lesen Sie SKILL.md und alle von SkillsMP angezeigten Begleitdateien, bevor Sie sich für eine Installation entscheiden.
Mit Codex oder Claude installieren Kopieren Sie diesen Prompt, fügen Sie ihn in Codex, Claude oder einen anderen Assistant ein und lassen Sie die Skill-Seite prüfen und installieren.
Ein direkter Befehl überspringt den Prüf-Prompt. Prüfen Sie die Quelle, bevor Sie ihn ausführen.
Guides Supabase database migrations for CircleTel - creates migrations, RLS policies, validates schema changes, and handles rollbacks
version
1.0.0
dependencies
python>=3.8
Database Migration Manager Skill
A comprehensive skill for managing Supabase database migrations in the CircleTel project. Handles migration creation, RLS policy setup, schema validation, and deployment workflows.
-- Migration: [Description]-- Created: [Date]-- Purpose: [What this migration does]-- ============================================-- STEP 1: Create Tables-- ============================================CREATE TABLE IF NOTEXISTS public.table_name (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
created_at TIMESTAMPTZ NOT NULLDEFAULT now(),
updated_at TIMESTAMPTZ NOT NULLDEFAULT now(),
-- Add your columns here
);
-- ============================================-- STEP 2: Create Indexes-- ============================================CREATE INDEX IF NOTEXISTS idx_table_name_column
ON public.table_name(column_name);
-- ============================================-- STEP 3: Add Foreign Keys-- ============================================ALTER TABLE public.table_name
ADD CONSTRAINT fk_table_name_reference
FOREIGN KEY (reference_id) REFERENCES public.other_table(id)
ONDELETE CASCADE;
-- ============================================-- STEP 4: Enable RLS-- ============================================ALTER TABLE public.table_name ENABLE ROW LEVEL SECURITY;
-- ============================================-- STEP 5: Create RLS Policies-- ============================================-- Policy: Users can read their own recordsCREATE POLICY "policy_name_select" ON public.table_name
FORSELECTUSING (auth.uid() = user_id);
-- Policy: Users can insert their own recordsCREATE POLICY "policy_name_insert" ON public.table_name
FORINSERTWITHCHECK (auth.uid() = user_id);
-- ============================================-- STEP 6: Create Triggers (if needed)-- ============================================-- Trigger: Update updated_at timestampCREATEOR REPLACE FUNCTION public.update_timestamp()
RETURNSTRIGGERAS $$
BEGIN
NEW.updated_at = now();
RETURNNEW;
END;
$$ LANGUAGE plpgsql;
CREATETRIGGER trigger_update_timestamp
BEFORE UPDATEON public.table_name
FOREACHROWEXECUTEFUNCTION public.update_timestamp();
-- ============================================-- STEP 7: Add Comments-- ============================================
COMMENT ONTABLE public.table_name IS'Description of what this table stores';
COMMENT ONCOLUMN public.table_name.column IS'Description of this column';
3. CircleTel-Specific Patterns
Customer Dashboard Tables
-- Customer services with lifecycle trackingCREATE TABLE IF NOTEXISTS public.customer_services (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
customer_id UUID NOT NULLREFERENCES public.customers(id) ONDELETE CASCADE,
account_number TEXT UNIQUENOT NULL, -- CT-YYYY-NNNNN format
service_package_id UUID REFERENCES public.service_packages(id),
status TEXT NOT NULLCHECK (status IN ('pending', 'active', 'suspended', 'cancelled')),
activation_date DATE,
suspension_date DATE,
cancellation_date DATE,
created_at TIMESTAMPTZ NOT NULLDEFAULT now(),
updated_at TIMESTAMPTZ NOT NULLDEFAULT now()
);
B2B Quote-to-Contract Tables
-- KYC sessions with JSONB dataCREATE TABLE IF NOTEXISTS public.kyc_sessions (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
quote_id UUID NOT NULLREFERENCES public.business_quotes(id),
session_id TEXT UNIQUENOT NULL,
status TEXT NOT NULLCHECK (status IN ('pending', 'in_progress', 'completed', 'failed')),
extracted_data JSONB, -- Didit AI extracted data
risk_score INTEGERCHECK (risk_score BETWEEN0AND100),
verified_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULLDEFAULT now()
);
Partner Compliance Tables
-- Partner compliance documentsCREATE TABLE IF NOTEXISTS public.partner_compliance_documents (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
partner_id UUID NOT NULLREFERENCES public.partners(id) ONDELETE CASCADE,
document_category TEXT NOT NULL, -- 13 FICA/CIPC categories
document_url TEXT NOT NULL,
document_number TEXT,
verification_status TEXT DEFAULT'pending'CHECK (verification_status IN ('pending', 'approved', 'rejected')),
is_required BOOLEANDEFAULTfalse,
is_sensitive BOOLEANDEFAULTfalse,
expiry_date DATE,
created_at TIMESTAMPTZ NOT NULLDEFAULT now()
);
4. RLS Policy Patterns
Customer Data Access
-- Customers can only see their own dataCREATE POLICY "customers_select_own" ON public.customers
FORSELECTUSING (auth.uid() = id);
-- Admins can see all customer dataCREATE POLICY "admins_select_all_customers" ON public.customers
FORSELECTUSING (
EXISTS (
SELECT1FROM public.admin_users
WHERE id = auth.uid()
)
);
Service Role Bypass (for API routes)
-- Service role can do everythingCREATE POLICY "service_role_all" ON public.table_name
FORALLUSING (auth.jwt() ->>'role'='service_role');
Partner Portal Access
-- Partners can only access their own recordsCREATE POLICY "partners_select_own" ON public.partner_compliance_documents
FORSELECTUSING (
partner_id IN (
SELECT id FROM public.partners
WHERE user_id = auth.uid()
)
);
5. Testing Migrations
Step 1: Validate SQL Syntax
# Check syntax without executing
python .claude/skills/database-migration/scripts/validate_migration.py supabase/migrations/20251108120000_create_customer_invoices_table.sql
Step 2: Test on Local Supabase
# Apply migration locally
npx supabase db reset
npx supabase migration up
Step 3: Verify Schema
# Check table was created
npx supabase db dump --schema public
Step 4: Test RLS Policies
-- Test as authenticated userSETLOCAL ROLE authenticated;
SETLOCAL "request.jwt.claims" ='{"sub":"test-user-id"}';
SELECT*FROM public.table_name; -- Should only see own records
# Check tables
npx supabase db dump --schema public
# Check RLS policies
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;
-- 20251108130000_rollback_customer_invoices.sql-- Drop policiesDROP POLICY IF EXISTS "policy_name" ON public.table_name;
-- Disable RLSALTER TABLE public.table_name DISABLE ROW LEVEL SECURITY;
-- Drop triggersDROPTRIGGER IF EXISTS trigger_name ON public.table_name;
-- Drop indexesDROP INDEX IF EXISTS idx_table_name_column;
-- Drop tableDROPTABLE IF EXISTS public.table_name;
Method 2: Supabase Dashboard
Go to Supabase Dashboard → SQL Editor
Execute rollback SQL manually
Create migration file for record
Method 3: Reset to Previous State
# Local only - NOT for production!
npx supabase db reset
8. Common Migration Patterns
Add Column to Existing Table
-- Add column with default valueALTER TABLE public.customers
ADDCOLUMN IF NOTEXISTS phone_verified BOOLEANDEFAULTfalse;
-- Backfill existing records (if needed)UPDATE public.customers
SET phone_verified =falseWHERE phone_verified ISNULL;
Modify Column Type
-- Change column type (careful with data loss!)ALTER TABLE public.orders
ALTERCOLUMN total_amount TYPE DECIMAL(10,2)
USING total_amount::DECIMAL(10,2);
Add Enum Type
-- Create enum type
DO $$ BEGINCREATE TYPE order_status AS ENUM (
'pending', 'payment', 'kyc', 'installation', 'active', 'cancelled'
);
EXCEPTION
WHEN duplicate_object THENnull;
END $$;
-- Use enum in tableCREATE TABLE public.consumer_orders (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
status order_status DEFAULT'pending'
);
Create Junction Table (Many-to-Many)
-- Junction table for products and categoriesCREATE TABLE IF NOTEXISTS public.product_categories (
product_id UUID NOT NULLREFERENCES public.products(id) ONDELETE CASCADE,
category_id UUID NOT NULLREFERENCES public.categories(id) ONDELETE CASCADE,
created_at TIMESTAMPTZ NOT NULLDEFAULT now(),
PRIMARY KEY (product_id, category_id)
);
CREATE INDEX idx_product_categories_product ON public.product_categories(product_id);
CREATE INDEX idx_product_categories_category ON public.product_categories(category_id);
9. Performance Optimization
Index Strategies
-- Single column indexCREATE INDEX idx_orders_customer_id ON public.orders(customer_id);
-- Composite index (order matters!)CREATE INDEX idx_orders_customer_status ON public.orders(customer_id, status);
-- Partial index (for specific queries)CREATE INDEX idx_orders_active ON public.orders(customer_id)
WHERE status ='active';
-- Text search indexCREATE INDEX idx_products_name_search ON public.products
USING GIN (to_tsvector('english', name));
Query Optimization
-- Add index for foreign keyCREATE INDEX idx_customer_services_customer_id
ON public.customer_services(customer_id);
-- Add index for status filteringCREATE INDEX idx_customer_services_status
ON public.customer_services(status)
WHERE status IN ('active', 'suspended');
All tables have created_at and updated_at timestamps
All foreign keys have ON DELETE behavior specified
RLS is enabled on all tables with user data
RLS policies exist for SELECT, INSERT, UPDATE, DELETE
Service role policy exists for API access
Indexes created for all foreign keys
Indexes created for commonly filtered columns
Comments added to tables and important columns
Migration tested locally with npx supabase db reset
RLS policies tested with different user roles
No hardcoded IDs or sensitive data in migration
Rollback plan documented
11. Troubleshooting
Issue: RLS policy blocks all access
-- Check current policiesSELECT*FROM pg_policies WHERE tablename ='your_table';
-- Temporarily disable RLS for debugging (local only!)ALTER TABLE public.your_table DISABLE ROW LEVEL SECURITY;
-- Fix: Ensure service role policy existsCREATE POLICY "service_role_all" ON public.your_table
FORALLUSING (auth.jwt() ->>'role'='service_role');
Issue: Migration fails with "relation already exists"
-- Always use IF NOT EXISTSCREATE TABLE IF NOTEXISTS public.table_name (...);
CREATE INDEX IF NOTEXISTS idx_name ON public.table_name(column);
Issue: Foreign key constraint violation
-- Check for orphaned records before adding FKSELECT*FROM public.child_table
WHERE parent_id NOTIN (SELECT id FROM public.parent_table);
-- Delete or update orphaned recordsDELETEFROM public.child_table
WHERE parent_id NOTIN (SELECT id FROM public.parent_table);
-- Then add constraintALTER TABLE public.child_table
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id) REFERENCES public.parent_table(id);
12. CircleTel Migration History
Core Tables (Existing):
service_packages - Products/packages
coverage_leads - Coverage check results
customers - Customer accounts
consumer_orders - B2C orders
admin_users - Admin accounts with RBAC
B2B Tables (Recent):
kyc_sessions - Didit KYC verification
contracts - Generated contracts (CT-YYYY-NNN)
invoices - Invoices (INV-YYYY-NNN)
payment_transactions - Payment records
rica_submissions - RICA submissions
Partner Tables (Recent):
partners - Partner business details
partner_compliance_documents - FICA/CIPC docs
Customer Dashboard Tables (In Development):
customer_services - Service lifecycle
customer_billing - Billing configuration
customer_invoices - Generated invoices
usage_history - Interstellio sync
Quick Reference Commands
# Generate new migration
python .claude/skills/database-migration/scripts/generate_migration.py "description"# Validate migration
python .claude/skills/database-migration/scripts/validate_migration.py supabase/migrations/[file].sql
# Apply migrations locally
npx supabase db reset
npx supabase migration up
# Check migration status
npx supabase migration list
# Dump current schema
npx supabase db dump --schema public
# Test RLS policies
python .claude/skills/database-migration/scripts/test_rls.py
Resources
Templates: See templates/ directory for migration templates
Examples: See examples/ directory for real CircleTel migrations
Scripts: See scripts/ directory for automation tools
