Database Prompts

Prompts for database schema design, queries, and backend operations. Works with both Supabase (PostgreSQL) and Convex.

Schema Design

Design a Table

Supabase
Convex

Design a database table for [feature/entity].Requirements:

[List what data needs to be stored]
[Any relationships to other tables]
[Any constraints or validations]

I’m using Supabase (PostgreSQL). Provide:

CREATE TABLE statement
Row Level Security policies (users can only access their own data)
Any necessary indexes
TypeScript interface matching the schema

Design a database table for [feature/entity].Requirements:

[List what data needs to be stored]
[Any relationships to other tables]
[Any constraints or validations]

I’m using Convex. Provide:

Schema definition in convex/schema.ts
Query and mutation functions with auth checks
Any necessary indexes
TypeScript types are auto-generated

Supabase
Convex

Design a database schema for [feature] with these entities:

Relationship: [describe relationship]

Provide:

All CREATE TABLE statements
Foreign key relationships
RLS policies for each table
TypeScript interfaces
Example queries for common operations

Design a database schema for [feature] with these entities:

Relationship: [describe relationship]

Provide:

Schema definitions in convex/schema.ts
Indexes for relationships
Query functions with proper auth
Example usage in components

Add Column to Existing Table

Supabase
Convex

I need to add a new column to the [table_name] table:

Column name: [name]
Type: [type]
Nullable: [yes/no]
Default: [value or none]

Provide:

ALTER TABLE statement
Update to TypeScript interface
Migration to backfill existing rows (if needed)

Security

Supabase RLS Policy

Create Row Level Security policies for the [table_name] table.Rules:

Users can only see their own rows
Users can create rows for themselves
Users can update their own rows
Users can delete their own rows

The table has a user_id column that references auth.users.

Convex Auth Check

Add authentication checks to the [function_name] query/mutation.Requirements:

Only authenticated users can access
Users can only access their own data
Return appropriate error for unauthorized access

Use ctx.auth.getUserIdentity() to check auth.

Role-Based Access

Supabase
Convex

Create RLS policies for [table_name] with role-based access:Roles:

Admin: Can see and modify all rows
User: Can only see/modify their own rows
Guest: Read-only access to public rows

Assume we have a user_roles table with user_id and role columns.

CRUD Operations

Create Service Functions

Supabase
Convex

Create TypeScript service functions for the [table_name] table.Operations needed:

getAll() - Fetch all user’s records
getById(id) - Fetch single record
create(data) - Create new record
update(id, data) - Update record
delete(id) - Delete record

Follow these patterns:

Use the Supabase client from @/services/supabase
Include proper TypeScript types
Handle errors appropriately
Return typed data

Put in: app/services/[name]Service.ts

Create Convex functions for the [table_name] table.Operations needed:

list - Query all user’s documents
get - Query single document by ID
create - Mutation to create document
update - Mutation to update document
remove - Mutation to delete document

Follow these patterns:

Add to convex/[name].ts
Use proper argument validation with v. validators
Include auth checks in each function
Types are auto-generated

Show component usage with useQuery and useMutation.

Fetch with Filters

Supabase
Convex

Create a function to fetch [items] from Supabase with filters:Filters needed:

Search by: [fields]
Sort by: [field] ascending/descending
Pagination: page, pageSize

Return both the data and total count for pagination.

Create a query to fetch [items] from Convex with filters:Filters needed:

Search by: [fields]
Sort by: [field] ascending/descending
Pagination: cursor-based

Use .withIndex() for efficient filtering and .paginate() for pagination.

Fetch with Relationships

Supabase
Convex

Create a function to fetch [items] with related data:Main table: [table_name] Related data:

[related_table] via [foreign_key]
[another_table] via [foreign_key]

Use Supabase’s select with nested queries. Include TypeScript types for the nested response.

Create a query to fetch [items] with related data:Main table: [table_name] Related data:

[related_table] via [foreign_key]
[another_table] via [foreign_key]

Fetch related documents using ctx.db.get() for each relationship. Consider using a helper function for common relationship patterns.

Real-time Subscriptions

Supabase
Convex

Create a real-time subscription for changes to [table_name].When a record is:

Inserted: [what should happen]
Updated: [what should happen]
Deleted: [what should happen]

Include:

Subscription setup function
Cleanup function for unmount
TypeScript types for payload
Usage example in a component

Convex queries are automatically real-time!Create a query for [table_name] that will auto-update when data changes.Show usage with useQuery hook:

const items = useQuery(api.items.list)
// items automatically updates when data changes

No manual subscription management needed.

Live Query Hook

Supabase
Convex

Create a React hook that provides real-time data from [table_name].Features:

Initial data fetch
Real-time updates via subscription
Loading and error states
Automatic cleanup on unmount

Usage: const { data, loading, error } = useLive[Items]()

Convex queries are already live! Just use useQuery:

function MyComponent() {
  const items = useQuery(api.items.list)

  if (items === undefined) return <Loading />

  return <ItemList items={items} />
}

The component re-renders automatically when data changes.

Migrations

Create Migration

Supabase
Convex

Create a Supabase migration for: [Describe the change]Include:

The migration SQL
A descriptive migration name
Any seed data if needed
Rollback SQL (if possible)

Create a Convex migration for: [Describe the change]Convex handles schema changes automatically. For data migrations:

Create a migration function in convex/migrations.ts
Run it once via the dashboard or a one-time action
Document what the migration does

Schema changes are applied on npx convex dev.

Performance

Add Indexes

Supabase
Convex

Suggest indexes for the [table_name] table to optimize these queries:

[Describe query 1]
[Describe query 2]
[Describe query 3]

Provide CREATE INDEX statements and explain why each index helps.

Add indexes to [table_name] for these queries:

[Describe query 1]
[Describe query 2]

Add indexes in schema.ts:

.index("by_user", ["userId"])
.index("by_status_and_date", ["status", "createdAt"])

Then use .withIndex() in queries.

Common Patterns

Soft Delete

Supabase
Convex

Implement soft delete for [table_name]:

Add deleted_at timestamp column
Update RLS to filter deleted rows by default
Create service functions:
- softDelete(id)
- restore(id)
- getDeleted()
- permanentDelete(id)

Update existing queries to exclude soft-deleted rows.

Implement soft delete for [table_name]:

Add deletedAt optional field to schema
Update queries to filter deleted documents
Create functions:
- softDelete mutation
- restore mutation
- listDeleted query
- permanentDelete mutation

All queries should check deletedAt field.

Timestamps

Supabase
Convex

Add automatic timestamp handling to [table_name]:

created_at: Set on insert
updated_at: Set on insert and update

Include:

Column definitions with defaults
Trigger for auto-updating updated_at
TypeScript interface updates

Add timestamp handling to [table_name]:

createdAt: Set in create mutation
updatedAt: Set in create and update mutations

Use Date.now() in mutations:

await ctx.db.insert("items", {
  ...args,
  createdAt: Date.now(),
  updatedAt: Date.now(),
})

Quick Reference

Supabase

Operation	Method
Select all	`.from('table').select('*')`
Select with filter	`.eq('column', value)`
Select single	`.single()`
Insert	`.insert(data)`
Update	`.update(data).eq('id', id)`
Delete	`.delete().eq('id', id)`
Upsert	`.upsert(data)`
Count	`.select('*', { count: 'exact' })`
Join	`.select(', related_table()')`

Convex

Operation	Method
Query all	`ctx.db.query("table").collect()`
Query with filter	`.filter((q) => q.eq(q.field("field"), value))`
Query with index	`.withIndex("index_name", (q) => q.eq("field", value))`
Get by ID	`ctx.db.get(id)`
Insert	`ctx.db.insert("table", data)`
Update	`ctx.db.patch(id, data)`
Replace	`ctx.db.replace(id, data)`
Delete	`ctx.db.delete(id)`
Paginate	`.paginate(opts)`

Introduction

Getting Started

Core Features

Development

Deployment

Support

Database Prompts

Database Prompts

Schema Design

Design a Table

Add Column to Existing Table

Security

Supabase RLS Policy

Convex Auth Check

Role-Based Access

CRUD Operations

Create Service Functions

Fetch with Filters

Fetch with Relationships

Real-time Subscriptions

Live Query Hook

Migrations

Create Migration

Performance

Add Indexes

Common Patterns

Soft Delete

Timestamps

Quick Reference

Supabase

Convex

Introduction

Getting Started

Core Features

Development

Deployment

Support

​Database Prompts

​Schema Design

​Design a Table

​Design Related Tables

​Add Column to Existing Table

​Security

​Supabase RLS Policy

​Convex Auth Check

​Role-Based Access

​CRUD Operations

​Create Service Functions

​Fetch with Filters

​Fetch with Relationships

​Real-time Subscriptions

​Subscribe to Changes

​Live Query Hook

​Migrations

​Create Migration

​Performance

​Add Indexes

​Common Patterns

​Soft Delete

​Timestamps

​Quick Reference

​Supabase

​Convex

Database Prompts

Schema Design

Design a Table

Design Related Tables

Add Column to Existing Table

Security

Supabase RLS Policy

Convex Auth Check

Role-Based Access

CRUD Operations

Create Service Functions

Fetch with Filters

Fetch with Relationships

Real-time Subscriptions

Subscribe to Changes

Live Query Hook

Migrations

Create Migration

Performance

Add Indexes

Common Patterns

Soft Delete

Timestamps

Quick Reference

Supabase

Convex