Design and write Supabase/PostgreSQL database migrations. Use when creating or modifying database schema, tables, indexes, or constraints.
When creating database migrations for the quinipolo project:
File Location
quinipolo-be/migrations/create_notification_tables.sqlSchema Design
created_at, updated_atMigration Pattern
-- Create table
CREATE TABLE IF NOT EXISTS table_name (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
field_name TEXT NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
-- Create indexes
CREATE INDEX idx_table_field ON table_name(field_name);
-- Add comments
COMMENT ON TABLE table_name IS 'Description of what this table stores';
Best Practices
IF NOT EXISTS to make migrations idempotentON DELETE CASCADE where appropriatejsonb for flexible data structurescreated_at timestampsCommon Field Types
UUID DEFAULT uuid_generate_v4()UUID REFERENCES table(id)TEXT (variable length) or VARCHAR(n) (limited)JSONB (binary JSON, indexed)BOOLEAN DEFAULT falseTIMESTAMP DEFAULT NOW()Notification Tokens Table:
CREATE TABLE IF NOT EXISTS notification_tokens (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
expo_push_token TEXT UNIQUE NOT NULL,
device_info JSONB,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
last_used_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_notification_tokens_user_id ON notification_tokens(user_id);
CREATE INDEX idx_notification_tokens_active ON notification_tokens(is_active);
Notifications History Table:
CREATE TABLE IF NOT EXISTS notifications (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
type TEXT NOT NULL,
quinipolo_id UUID REFERENCES quinipolos(id),
league_id UUID REFERENCES leagues(id),
title TEXT NOT NULL,
body TEXT NOT NULL,
data JSONB,
read_status BOOLEAN DEFAULT false,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_notifications_user_id ON notifications(user_id);
CREATE INDEX idx_notifications_read_status ON notifications(read_status);
CREATE INDEX idx_notifications_created_at ON notifications(created_at DESC);