π Supabase Annotations
A powerful, type-safe code generator for creating Supabase/PostgreSQL database schemas from Dart model classes. Build production-ready database schemas with Row Level Security (RLS), indexes, foreign keys, migrations, and table partitioning - all from your Dart code.
π Table of Contents
- β¨ Features
- π Quick Start
- π Core Annotations
- π§ Configuration
- ποΈ Column Types & Constraints
- π Security & RLS Policies
- β‘ Performance & Indexing
- π Migration Support
- π― Advanced Examples
- π Best Practices
- π οΈ Development
- π€ Contributing
- π License
β¨ Features
ποΈ Schema Generation
- Type-Safe SQL Generation - Convert Dart classes to PostgreSQL schemas
- Full PostgreSQL Support - All column types, constraints, and features
- Automatic Documentation - Generate SQL comments from Dart documentation
π Security First
- Row Level Security (RLS) - Declarative RLS policy generation
- Fine-Grained Permissions - Control access at the row and column level
- Authentication Integration - Built-in Supabase auth helpers
β‘ Performance Optimization
- Smart Indexing - Automatic and custom index generation
- Query Optimization - Composite indexes and partial indexes
- Table Partitioning - Range and hash partitioning support
π Migration & Evolution
- Safe Schema Evolution - Multiple migration strategies
- Zero-Downtime Updates - ADD COLUMN and ALTER TABLE support
- Rollback Support - Safe migration with fallback options
π― Developer Experience
- IDE Integration - Full IntelliSense and code completion
- Comprehensive Validation - Catch errors at build time
- Rich Documentation - Inline help and examples
π Quick Start
1οΈβ£ Installation
Add to your pubspec.yaml
:
dependencies:
supabase_annotations: ^1.1.1
dev_dependencies:
build_runner: ^2.4.8
source_gen: ^1.5.0
2οΈβ£ Configuration
Create build.yaml
in your project root:
targets:
$default:
builders:
supabase_annotations|schema_builder:
enabled: true
generate_for:
include:
- lib/**.dart
- example/**.dart
exclude:
- lib/**.g.dart
- lib/**.schema.dart
options:
# π Migration Strategy
migration_mode: 'createOrAlter' # Safe schema evolution
enable_column_adding: true # Add missing columns
generate_do_blocks: true # PostgreSQL DO blocks
# π Security Configuration
enable_rls_by_default: false # RLS on all tables
# π Code Generation
generate_comments: true # Include documentation
validate_schema: true # Schema validation
format_sql: true # Format output
3οΈβ£ Define Your Model
import 'package:supabase_annotations/supabase_annotations.dart';
@DatabaseTable(
name: 'users',
enableRLS: true,
comment: 'Application users with authentication',
)
@RLSPolicy(
name: 'users_own_data',
type: RLSPolicyType.all,
condition: 'auth.uid() = id',
)
@DatabaseIndex(
name: 'users_email_idx',
columns: ['email'],
isUnique: true,
)
class User {
@DatabaseColumn(
type: ColumnType.uuid,
isPrimaryKey: true,
defaultValue: DefaultValue.generateUuid,
)
String? id;
@DatabaseColumn(
type: ColumnType.text,
isUnique: true,
isNullable: false,
)
late String email;
@DatabaseColumn(
type: ColumnType.varchar(100),
isNullable: false,
)
late String fullName;
@DatabaseColumn(
type: ColumnType.timestampWithTimeZone,
defaultValue: DefaultValue.currentTimestamp,
)
late DateTime createdAt;
@DatabaseColumn(
type: ColumnType.timestampWithTimeZone,
defaultValue: DefaultValue.currentTimestamp,
)
late DateTime updatedAt;
}
4οΈβ£ Generate Schema
# Generate SQL schema files
dart run build_runner build
# Watch for changes and regenerate
dart run build_runner watch
5οΈβ£ Generated Output
-- π Generated: lib/models/user.schema.sql
-- ποΈ Create table with RLS enabled
CREATE TABLE IF NOT EXISTS users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT UNIQUE NOT NULL,
full_name VARCHAR(100) NOT NULL,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
-- π Enable Row Level Security
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
-- π‘οΈ Create RLS policy
CREATE POLICY users_own_data ON users
FOR ALL
USING (auth.uid() = id);
-- β‘ Create performance indexes
CREATE UNIQUE INDEX users_email_idx ON users(email);
-- π Add table comment
COMMENT ON TABLE users IS 'Application users with authentication';
π Core Annotations
ποΈ @DatabaseTable
Configure table-level settings:
@DatabaseTable(
name: 'custom_table_name', // π Custom table name (optional)
enableRLS: true, // π Row Level Security
comment: 'Table description', // π Documentation
partitionBy: RangePartition( // π Table partitioning
columns: ['created_at']
),
)
class MyTable { }
π·οΈ @DatabaseColumn
Define column properties:
@DatabaseColumn(
name: 'custom_column_name', // π Custom column name
type: ColumnType.varchar(255), // π― PostgreSQL type
isNullable: false, // β NOT NULL constraint
isPrimaryKey: true, // π Primary key
isUnique: true, // β Unique constraint
defaultValue: DefaultValue.currentTimestamp, // π Default value
comment: 'Column description', // π Documentation
checkConstraints: ['value > 0'], // β
CHECK constraints
)
late String myField;
π @ForeignKey
Define relationships:
@ForeignKey(
table: 'users', // π― Referenced table
column: 'id', // π Referenced column
onDelete: ForeignKeyAction.cascade, // ποΈ Delete behavior
onUpdate: ForeignKeyAction.restrict, // π Update behavior
)
@DatabaseColumn(type: ColumnType.uuid)
late String userId;
β‘ @DatabaseIndex
Optimize performance:
// π Composite index on table
@DatabaseIndex(
name: 'user_status_created_idx',
columns: ['status', 'created_at'],
type: IndexType.btree,
isUnique: false,
where: "status != 'deleted'", // π― Partial index
)
class User { }
// π Single column index
@DatabaseIndex(type: IndexType.hash)
@DatabaseColumn(type: ColumnType.text)
late String status;
π‘οΈ @RLSPolicy
Secure your data:
@RLSPolicy(
name: 'user_read_own', // π Policy name
type: RLSPolicyType.select, // π― Operation type
condition: 'auth.uid() = user_id', // π Access condition
roles: ['authenticated'], // π₯ Database roles
comment: 'Users can read their own data', // π Documentation
)
class UserData { }
π§ Configuration
π Configuration Options
Option | Type | Default | Description |
---|---|---|---|
migration_mode |
string | 'createOnly' |
Migration strategy |
enable_column_adding |
bool | true |
Add missing columns |
generate_do_blocks |
bool | true |
Use DO blocks for safety |
enable_rls_by_default |
bool | false |
RLS on all tables |
add_timestamps |
bool | false |
Auto-add timestamps |
use_explicit_nullability |
bool | false |
Explicit NULL/NOT NULL |
generate_comments |
bool | true |
Include documentation |
validate_schema |
bool | true |
Schema validation |
format_sql |
bool | true |
Format SQL output |
π― Environment-Specific Configurations
π§ Development Setup:
options:
migration_mode: 'createOrAlter' # Safe evolution
enable_rls_by_default: false # Easier testing
generate_comments: true # Full docs
validate_schema: true # Catch errors
format_sql: true # Readable output
π Production Setup:
options:
migration_mode: 'createOrAlter' # Safe migrations
enable_column_adding: true # Allow evolution
generate_do_blocks: true # Extra safety
validate_schema: true # Strict validation
format_sql: true # Clean output
π€ CI/CD Pipeline:
options:
migration_mode: 'createOnly' # Standard creation
validate_schema: true # Fail on errors
generate_comments: false # Minimal output
format_sql: true # Consistent format
ποΈ Column Types & Constraints
π PostgreSQL Column Types
π Text Types
ColumnType.text // TEXT
ColumnType.varchar(255) // VARCHAR(255)
ColumnType.char(10) // CHAR(10)
π’ Numeric Types
ColumnType.integer // INTEGER
ColumnType.bigint // BIGINT
ColumnType.decimal(10, 2) // DECIMAL(10,2)
ColumnType.real // REAL
ColumnType.doublePrecision // DOUBLE PRECISION
ColumnType.serial // SERIAL
ColumnType.bigserial // BIGSERIAL
π Date/Time Types
ColumnType.timestamp // TIMESTAMP
ColumnType.timestampWithTimeZone // TIMESTAMPTZ
ColumnType.date // DATE
ColumnType.time // TIME
ColumnType.interval // INTERVAL
π― Special Types
ColumnType.uuid // UUID
ColumnType.boolean // BOOLEAN
ColumnType.json // JSON
ColumnType.jsonb // JSONB
ColumnType.bytea // BYTEA
ColumnType.inet // INET
ColumnType.macaddr // MACADDR
ColumnType.point // POINT
ColumnType.array(ColumnType.text) // TEXT[]
π Default Values
// π Literal values
DefaultValue.none // NULL
DefaultValue.zero // 0
DefaultValue.one // 1
DefaultValue.emptyString // ''
DefaultValue.emptyArray // ARRAY[]
DefaultValue.emptyObject // '{}'
// β‘ Functions
DefaultValue.currentTimestamp // CURRENT_TIMESTAMP
DefaultValue.currentDate // CURRENT_DATE
DefaultValue.generateUuid // gen_random_uuid()
DefaultValue.autoIncrement // nextval(sequence)
// π Factory methods
DefaultValue.string('value') // 'value'
DefaultValue.number(42) // 42
DefaultValue.boolean(true) // true
DefaultValue.expression('NOW()') // Custom expression
β Constraints
@DatabaseColumn(
// π Primary key
isPrimaryKey: true,
// β Unique constraint
isUnique: true,
// β NOT NULL constraint
isNullable: false,
// β
CHECK constraints
checkConstraints: [
'length(email) > 0',
'email ~* \'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}\$\'',
],
)
late String email;
π Security & RLS Policies
π‘οΈ RLS Policy Types
RLSPolicyType.all // π All operations (CRUD)
RLSPolicyType.select // ποΈ Read operations only
RLSPolicyType.insert // β Insert operations only
RLSPolicyType.update // βοΈ Update operations only
RLSPolicyType.delete // ποΈ Delete operations only
π― Common RLS Patterns
π€ User Owns Data
@RLSPolicy(
name: 'users_own_data',
type: RLSPolicyType.all,
condition: 'auth.uid() = user_id',
)
π’ Multi-tenant Isolation
@RLSPolicy(
name: 'tenant_isolation',
type: RLSPolicyType.all,
condition: 'tenant_id = auth.jwt() ->> "tenant_id"',
)
π₯ Role-based Access
@RLSPolicy(
name: 'admin_full_access',
type: RLSPolicyType.all,
condition: 'auth.jwt() ->> "role" = "admin"',
roles: ['authenticated'],
)
@RLSPolicy(
name: 'user_read_only',
type: RLSPolicyType.select,
condition: 'auth.jwt() ->> "role" = "user"',
roles: ['authenticated'],
)
π Time-based Access
@RLSPolicy(
name: 'active_records_only',
type: RLSPolicyType.select,
condition: 'expires_at > NOW() AND is_active = true',
)
β‘ Performance & Indexing
π Index Types
IndexType.btree // π³ B-tree (default, general purpose)
IndexType.hash // #οΈβ£ Hash (equality only)
IndexType.gin // π GIN (JSON, arrays, full-text)
IndexType.gist // π― GiST (geometric, full-text)
IndexType.spgist // π SP-GiST (space-partitioned)
IndexType.brin // π BRIN (large ordered tables)
π Index Strategies
π Single Column Index
@DatabaseIndex(type: IndexType.btree)
@DatabaseColumn(type: ColumnType.text)
late String status;
π Composite Index
@DatabaseIndex(
name: 'user_activity_idx',
columns: ['user_id', 'created_at', 'activity_type'],
type: IndexType.btree,
)
π― Partial Index
@DatabaseIndex(
name: 'active_users_idx',
columns: ['email'],
where: "status = 'active' AND deleted_at IS NULL",
)
π Expression Index
@DatabaseIndex(
name: 'user_search_idx',
expression: "to_tsvector('english', name || ' ' || email)",
type: IndexType.gin,
)
π± JSON Index
@DatabaseIndex(
name: 'metadata_search_idx',
expression: "(metadata -> 'tags')",
type: IndexType.gin,
)
π Migration Support
π― Migration Modes
Mode | Description | Use Case |
---|---|---|
createOnly |
Standard CREATE TABLE | π New projects |
createIfNotExists |
CREATE TABLE IF NOT EXISTS | π Safe creation |
createOrAlter |
CREATE + ALTER for new columns | π Schema evolution |
alterOnly |
Only ALTER TABLE statements | π οΈ Existing schemas |
dropAndRecreate |
DROP and CREATE | π§ͺ Development only |
π Migration Examples
π Adding New Column
// Add this field to existing User class
@DatabaseColumn(
type: ColumnType.integer,
defaultValue: DefaultValue.zero,
)
int? age;
Generated Migration:
-- π Safe column addition
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'age'
) THEN
ALTER TABLE users ADD COLUMN age INTEGER DEFAULT 0;
END IF;
END $$;
π Adding Foreign Key
// Add relationship to existing table
@ForeignKey(
table: 'companies',
column: 'id',
onDelete: ForeignKeyAction.setNull,
)
@DatabaseColumn(type: ColumnType.uuid)
String? companyId;
Generated Migration:
-- π Safe foreign key addition
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'company_id'
) THEN
ALTER TABLE users ADD COLUMN company_id UUID;
ALTER TABLE users ADD CONSTRAINT users_company_id_fkey
FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL;
END IF;
END $$;
π‘οΈ Safe Migration Practices
# π― Recommended production configuration
options:
migration_mode: 'createOrAlter' # Safe evolution
enable_column_adding: true # Allow new columns
generate_do_blocks: true # Extra safety checks
validate_schema: true # Comprehensive validation
π― Advanced Examples
π’ Multi-tenant SaaS Application
@DatabaseTable(
name: 'documents',
enableRLS: true,
comment: 'Multi-tenant document storage',
)
@RLSPolicy(
name: 'tenant_isolation',
type: RLSPolicyType.all,
condition: 'tenant_id = auth.jwt() ->> "tenant_id"',
)
@DatabaseIndex(
name: 'documents_tenant_created_idx',
columns: ['tenant_id', 'created_at'],
)
@DatabaseIndex(
name: 'documents_search_idx',
expression: "to_tsvector('english', title || ' ' || content)",
type: IndexType.gin,
)
class Document {
@DatabaseColumn(
type: ColumnType.uuid,
isPrimaryKey: true,
defaultValue: DefaultValue.generateUuid,
)
String? id;
@ForeignKey(
table: 'tenants',
column: 'id',
onDelete: ForeignKeyAction.cascade,
)
@DatabaseColumn(
type: ColumnType.uuid,
isNullable: false,
)
late String tenantId;
@DatabaseColumn(
type: ColumnType.text,
isNullable: false,
checkConstraints: ['length(title) > 0'],
)
late String title;
@DatabaseColumn(type: ColumnType.text)
String? content;
@DatabaseColumn(
type: ColumnType.jsonb,
defaultValue: DefaultValue.emptyObject,
)
Map<String, dynamic>? metadata;
@DatabaseColumn(
type: ColumnType.timestampWithTimeZone,
defaultValue: DefaultValue.currentTimestamp,
)
late DateTime createdAt;
}
π E-commerce System
@DatabaseTable(
name: 'orders',
enableRLS: true,
comment: 'Customer orders with audit trail',
)
@RLSPolicy(
name: 'customers_own_orders',
type: RLSPolicyType.select,
condition: 'customer_id = auth.uid()',
)
@RLSPolicy(
name: 'staff_manage_orders',
type: RLSPolicyType.all,
condition: 'auth.jwt() ->> "role" IN ("admin", "staff")',
)
@DatabaseIndex(
name: 'orders_customer_status_idx',
columns: ['customer_id', 'status', 'created_at'],
)
@DatabaseIndex(
name: 'orders_total_idx',
columns: ['total_amount'],
where: "status != 'cancelled'",
)
class Order {
@DatabaseColumn(
type: ColumnType.uuid,
isPrimaryKey: true,
defaultValue: DefaultValue.generateUuid,
)
String? id;
@ForeignKey(
table: 'customers',
column: 'id',
onDelete: ForeignKeyAction.restrict,
)
@DatabaseColumn(
type: ColumnType.uuid,
isNullable: false,
)
late String customerId;
@DatabaseColumn(
type: ColumnType.varchar(20),
defaultValue: DefaultValue.string('pending'),
checkConstraints: [
"status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')"
],
)
late String status;
@DatabaseColumn(
type: ColumnType.decimal(10, 2),
isNullable: false,
checkConstraints: ['total_amount >= 0'],
)
late double totalAmount;
@DatabaseColumn(
type: ColumnType.jsonb,
comment: 'Order line items with product details',
)
List<Map<String, dynamic>>? items;
@DatabaseColumn(
type: ColumnType.timestampWithTimeZone,
defaultValue: DefaultValue.currentTimestamp,
)
late DateTime createdAt;
@DatabaseColumn(
type: ColumnType.timestampWithTimeZone,
defaultValue: DefaultValue.currentTimestamp,
)
late DateTime updatedAt;
}
π Analytics & Logging
@DatabaseTable(
name: 'events',
comment: 'Application event tracking',
partitionBy: RangePartition(columns: ['created_at']),
)
@DatabaseIndex(
name: 'events_type_created_idx',
columns: ['event_type', 'created_at'],
)
@DatabaseIndex(
name: 'events_user_session_idx',
columns: ['user_id', 'session_id'],
where: "user_id IS NOT NULL",
)
@DatabaseIndex(
name: 'events_properties_idx',
expression: "(properties -> 'category')",
type: IndexType.gin,
)
class Event {
@DatabaseColumn(
type: ColumnType.uuid,
isPrimaryKey: true,
defaultValue: DefaultValue.generateUuid,
)
String? id;
@DatabaseColumn(
type: ColumnType.varchar(50),
isNullable: false,
)
late String eventType;
@DatabaseColumn(type: ColumnType.uuid)
String? userId;
@DatabaseColumn(type: ColumnType.uuid)
String? sessionId;
@DatabaseColumn(
type: ColumnType.jsonb,
defaultValue: DefaultValue.emptyObject,
)
Map<String, dynamic>? properties;
@DatabaseColumn(
type: ColumnType.inet,
comment: 'Client IP address',
)
String? ipAddress;
@DatabaseColumn(
type: ColumnType.text,
comment: 'User agent string',
)
String? userAgent;
@DatabaseColumn(
type: ColumnType.timestampWithTimeZone,
defaultValue: DefaultValue.currentTimestamp,
isNullable: false,
)
late DateTime createdAt;
}
π Best Practices
ποΈ Schema Design
β DO:
- Use descriptive, meaningful names
- Follow PostgreSQL naming conventions (snake_case)
- Keep names under 63 characters
- Add comprehensive comments and documentation
- Use appropriate column types for your data
β DON'T:
- Use reserved keywords as names
- Create overly complex nested structures
- Forget to add indexes on frequently queried columns
- Skip validation constraints
π Security Guidelines
β DO:
- Always enable RLS on tables with sensitive data
- Use specific, restrictive policy conditions
- Test policies thoroughly with different user roles
- Document security requirements and assumptions
- Use parameterized conditions to prevent injection
β DON'T:
- Rely solely on application-level security
- Create overly permissive policies
- Forget to test edge cases in policy conditions
- Hardcode user IDs in policies
β‘ Performance Optimization
β DO:
- Add indexes on frequently queried columns
- Use composite indexes for multi-column queries
- Consider partial indexes for filtered queries
- Use appropriate index types for your use case
- Monitor query performance regularly
β DON'T:
- Create too many indexes (impacts write performance)
- Index every column "just in case"
- Forget to maintain statistics on large tables
- Ignore query execution plans
π Migration Management
β DO:
- Use migration modes for schema evolution
- Test migrations on staging data first
- Plan for rollback scenarios
- Document breaking changes thoroughly
- Use
createOrAlter
mode for production
β DON'T:
- Drop tables or columns without backup
- Skip testing migrations
- Apply untested migrations to production
- Forget to version your schema changes
π οΈ Development
π Getting Started
# Clone the repository
git clone https://github.com/ahmtydn/supabase_annotations.git
cd supabase_annotations
# Install dependencies
dart pub get
# Run tests
dart test
# Run analysis
dart analyze
# Generate documentation
dart doc
π§ͺ Running Examples
# Navigate to examples
cd example
# Generate schemas for all examples
dart run build_runner build
# View generated SQL files
ls lib/*.schema.sql
π Project Structure
lib/
βββ builder.dart # Build configuration
βββ supabase_annotations.dart # Public API
βββ src/
βββ annotations/ # Annotation definitions
β βββ database_column.dart
β βββ database_index.dart
β βββ database_table.dart
β βββ foreign_key.dart
β βββ rls_policy.dart
βββ generators/ # Code generation logic
β βββ schema_generator.dart
βββ models/ # Data models
βββ column_types.dart
βββ default_values.dart
βββ foreign_key_actions.dart
βββ index_types.dart
βββ migration_config.dart
βββ partition_strategy.dart
βββ table_constraints.dart
βββ validators.dart
π€ Contributing
We welcome contributions! Here's how you can help:
π Bug Reports
- Use the issue tracker
- Include a minimal reproduction case
- Provide environment details (Dart version, OS, etc.)
π‘ Feature Requests
- Check existing discussions
- Explain the use case and benefits
- Consider implementation complexity
π§ Pull Requests
- Fork the repository
- Create a feature branch (
git checkout -b feature/amazing-feature
) - Add tests for new functionality
- Ensure all tests pass (
dart test
) - Run analysis (
dart analyze
) - Commit changes (
git commit -m 'Add amazing feature'
) - Push to branch (
git push origin feature/amazing-feature
) - Submit a pull request
π Development Guidelines
- Follow the existing code style
- Add comprehensive tests
- Update documentation
- Include examples for new features
- Ensure backward compatibility
π Support & Community
π Documentation
π¬ Community
π Need Help?
- Check the FAQ
- Search existing issues
- Ask in discussions
π License
This project is licensed under the MIT License - see the LICENSE file for details.
π Acknowledgments
- Supabase Team - For creating an amazing platform
- Dart Team - For excellent tooling and language features
- PostgreSQL Community - For the world's most advanced open source database
- Contributors - For making this project better
π Show Your Support
If this project helped you, please consider:
- β Star the repository
- π Share with your team
- π Report issues
- π‘ Suggest improvements
- π€ Contribute code
Built with β€οΈ for the Supabase and Dart communities
π Website β’ π Docs β’ π¬ Community
Libraries
- builder
- Builder configuration for the Supabase schema generator.
- supabase_annotations
- A comprehensive code generator for creating Supabase/PostgreSQL database schemas from Dart model classes.