SQL Templates

Access ready-to-use SQL table creation templates for all supported providers and sync types. Copy and paste these templates into your database's SQL editor to create the required tables for data synchronization.

What are SQL Templates?

SQL templates are pre-built CREATE TABLE statements that define the exact table structure needed for each sync type. These templates ensure your PostgreSQL tables have the correct columns, data types, and constraints to receive synced data from external APIs.

Why Manual Table Creation?

CLS can auto-create tables with one click, but manual creation is available as a fallback. Manual table creation in your PostgreSQL database offers these benefits:

• Security: You maintain full control over your database schema
• Transparency: You see exactly what tables and columns are being created
• Customization: You can modify the schema if needed before creation
• Best Practice: Explicit database changes are safer than automatic modifications

How to Use SQL Templates

Follow these steps to create a table using any template on this site:

1. Navigate to the template page for your desired provider and sync type
2. Click the "Copy SQL" button to copy the template to your clipboard
3. Open your database provider's dashboard (Supabase, Neon, or other)
4. Navigate to your project
5. Go to SQL Editor → New query
6. Paste the SQL template
7. Click Run to execute and create the table
8. Verify the table appears in your database's table view

Available Templates

Stripe Templates

Stripe is our flagship provider with comprehensive support for common data types:

• Customers - Sync Stripe customer data including names, emails, and metadata
• Invoices - Sync invoice records with amounts, and payment status
• Subscriptions - Sync subscription data with recurring billing and status
• Payment Intents - Sync payment intent records with amounts and payment methods

Template Structure

Each SQL template includes:

• Primary Key: Unique identifier (usually id TEXT PRIMARY KEY)
• Core Fields: Essential data columns specific to the data type (e.g., email, name, status, amount)
• Data Column: Complete API object stored as JSONB (contains all fields from the provider's API)
• Metadata Fields: Standard sync tracking fields like livemode, created, synced_at
• Proper Data Types: Optimized PostgreSQL data types for each field
• Performance Indexes: Pre-built indexes for common query patterns

Understanding the Data Column

All templates include a data JSONB column that stores the complete object from the provider's API. This gives you access to every field, even those not extracted to dedicated columns.

Why use both columns and JSONB?

• Dedicated columns (like email, status, amount) are optimized for fast queries and filtering
• Data JSONB preserves all API fields, including custom metadata and fields that may be added by the provider in the future

Querying JSONB data:

-- Access nested fields using -> and ->> operators
SELECT data->>'phone' AS phone_number FROM stripe_customers;

-- Query by nested field
SELECT * FROM stripe_invoices WHERE data->>'invoice_pdf' IS NOT NULL;

-- Extract array elements
SELECT data->'payment_method_types'->0 AS payment_method FROM stripe_payment_intents;

Modifying Templates

You can modify templates before executing them, but keep these guidelines in mind:

Safe Modifications:

• ✅ Adding extra custom columns
• ✅ Adding indexes for performance
• ✅ Adding foreign key constraints to other tables
• ✅ Changing column order (PostgreSQL is flexible)

Unsafe Modifications:

• ❌ Removing required columns
• ❌ Renaming columns
• ❌ Changing column data types
• ❌ Removing the primary key

Common Table Customizations

Here are common modifications you can safely make to any template:

Add a GIN Index for JSONB Queries

If you frequently query nested fields in the data column:

-- Replace {table_name} with your actual table name
CREATE INDEX idx_{table_name}_data
ON {table_name} USING GIN(data);

Add Foreign Key to Customers Table

Link records to your customers table:

-- Replace {table_name} with your actual table name
ALTER TABLE {table_name}
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer)
REFERENCES stripe_customers(id);

Add Custom Columns

Add application-specific fields:

-- Replace {table_name} with your actual table name
ALTER TABLE {table_name}
ADD COLUMN app_user_id UUID REFERENCES users(id),
ADD COLUMN internal_notes TEXT,
ADD COLUMN processed_at TIMESTAMPTZ;

Add Performance Indexes

Add indexes for your specific query patterns:

-- Replace {table_name} and {column} with actual names
CREATE INDEX idx_{table_name}_{column} ON {table_name}({column});

How Syncing Works

When you trigger a sync for any data type, CLS follows this process:

1. Initial Sync: CLS fetches all records from your connected API account
2. Batch Processing: Records are processed in batches for efficiency and reliability
3. Upsert Logic: Existing records are updated, new records are inserted (based on primary key)
4. Status Tracking: For data types with status fields (invoices, subscriptions, payments), status changes are automatically reflected
5. Data Sync: The synced_at timestamp is updated for each record to track when it was last synced

Next Steps

1. Browse the templates below and select the data types you want to sync
2. Create the tables using the SQL templates (optionally create the Customers table first for relational data)
3. Set up a sync configuration in your CLS dashboard
4. Run your first sync to import data from your API provider
5. Verify the data appears in your database's table view

For step-by-step instructions on creating your first sync configuration, see the Quick Start Guide.

Troubleshooting Common Issues

Table Validation Failed

If the configuration wizard can't validate your table:

• ✅ Ensure you executed the exact SQL template without modifications
• ✅ Verify the table name matches the template (e.g., stripe_customers, not customers)
• ✅ Check your database connection string is correct in your project settings
• ✅ Confirm the table is in the public schema

Sync Completed But No Data Appears

If your sync succeeds but no rows appear:

• ✅ Verify you have data in your API provider account (e.g., customers in Stripe)
• ✅ Check that your API key is valid and not restricted
• ✅ Look for error messages in the sync history within the CLS dashboard
• ✅ Ensure your API key has read access to the data type you're syncing

For more help, visit the Troubleshooting Guide.

Need Help?

• Can't find a template? Check that your provider and data type are supported
• Validation failing? Ensure you used the exact template without modifications
• Table already exists? Drop the old table first or use a different name
• Still stuck? Visit the Troubleshooting Guide