Supabase DDL Template
eg) Todo
--
-- For use with:
-- https://github.com/supabase/supabase/tree/master/examples/todo-list/sveltejs-todo-list or
-- https://github.com/supabase/examples-archive/tree/main/supabase-js-v1/todo-list
--
create table todos (
id bigint generated by default as identity primary key,
user_id uuid references auth.users not null,
task text check (char_length(task) > 3),
is_complete boolean default false,
inserted_at timestamp with time zone default timezone('utc'::text, now()) not null
);
-- RLS 적용
alter table todos enable row level security;
create policy "Individuals can create todos." on todos for
insert with check (auth.uid() = user_id);
create policy "Individuals can view their own todos. " on todos for
select using (auth.uid() = user_id);
create policy "Individuals can update their own todos." on todos for
update using (auth.uid() = user_id);
create policy "Individuals can delete their own todos." on todos for
delete using (auth.uid() = user_id);
eg) AI Chat
Tables : profiles chat suggestion document message vote
datatype : boolean, uuid, varchar, text, json, timestamp with time zone
-- Set schema to ai_chat_test
SET search_path TO ai_chat_test;
-- Create a table for public profiles
create table ai_chat_test.profiles (
id uuid references auth.users on delete cascade not null primary key,
updated_at timestamp with time zone,
username text unique,
full_name text,
avatar_url text,
website text,
constraint username_length check (char_length(username) >= 3)
);
-- Set up Row Level Security (RLS)
-- See https://supabase.com/docs/guides/auth/row-level-security for more details.
alter table ai_chat_test.profiles
enable row level security;
create policy "Public profiles are viewable by everyone." on ai_chat_test.profiles
for select using (true);
create policy "Users can insert their own profile." on ai_chat_test.profiles
for insert with check ((select auth.uid()) = id);
create policy "Users can update own profile." on ai_chat_test.profiles
for update using ((select auth.uid()) = id);
-- This trigger automatically creates a profile entry when a new user signs up via Supabase Auth.
-- See https://supabase.com/docs/guides/auth/managing-user-data#using-triggers for more details.
create function ai_chat_test.handle_new_user()
returns trigger
set search_path = 'ai_chat_test'
as $$
begin
insert into ai_chat_test.profiles (id, full_name, avatar_url)
values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');
return new;
end;
$$ language plpgsql security definer;
create trigger on_auth_user_created_to_ai_chat_test
after insert on auth.users
for each row execute procedure ai_chat_test.handle_new_user();
-- Set schema to ai_chat_test
SET search_path TO ai_chat_test;
CREATE TABLE IF NOT EXISTS ai_chat_test.chat (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"createdAt" timestamp NOT NULL,
"messages" json NOT NULL,
"userId" uuid NOT NULL
);
--> statement-breakpoint
DO $$ BEGIN
ALTER TABLE ai_chat_test.chat
ADD CONSTRAINT "chat_userId_profiles_id_fk"
FOREIGN KEY ("userId") REFERENCES ai_chat_test.profiles(id) ON DELETE NO ACTION ON UPDATE NO ACTION;
EXCEPTION
WHEN duplicate_object THEN NULL;
END $$;
-- Set schema to ai_chat_test
SET search_path TO ai_chat_test;
CREATE TABLE IF NOT EXISTS ai_chat_test."suggestion" (
"id" uuid DEFAULT gen_random_uuid() NOT NULL,
"documentId" uuid NOT NULL,
"documentCreatedAt" timestamp NOT NULL,
"originalText" text NOT NULL,
"suggestedText" text NOT NULL,
"description" text,
"isResolved" boolean DEFAULT false NOT NULL,
"userId" uuid NOT NULL,
"createdAt" timestamp NOT NULL,
CONSTRAINT "suggestion_id_pk" PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS ai_chat_test."document" (
"id" uuid DEFAULT gen_random_uuid() NOT NULL,
"createdAt" timestamp NOT NULL,
"title" text NOT NULL,
"content" text,
"userId" uuid NOT NULL,
CONSTRAINT "document_id_createdAt_pk" PRIMARY KEY("id","createdAt")
);
DO $$ BEGIN
ALTER TABLE ai_chat_test."suggestion" ADD CONSTRAINT "suggestion_userId_profiles_id_fk" FOREIGN KEY ("userId") REFERENCES "ai_chat_test"."profiles"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
WHEN duplicate_object THEN null;
END $$;
DO $$ BEGIN
ALTER TABLE ai_chat_test."suggestion" ADD CONSTRAINT "suggestion_documentId_documentCreatedAt_document_id_createdAt_fk" FOREIGN KEY ("documentId","documentCreatedAt") REFERENCES "ai_chat_test"."document"("id","createdAt") ON DELETE no action ON UPDATE no action;
EXCEPTION
WHEN duplicate_object THEN null;
END $$;
DO $$ BEGIN
ALTER TABLE ai_chat_test."document" ADD CONSTRAINT "document_userId_profiles_id_fk" FOREIGN KEY ("userId") REFERENCES "ai_chat_test"."profiles"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
WHEN duplicate_object THEN null;
END $$;
-- Set schema to ai_chat_test
SET search_path TO ai_chat_test;
CREATE TABLE IF NOT EXISTS ai_chat_test."message" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"chatId" uuid NOT NULL,
"role" varchar NOT NULL,
"content" json NOT NULL,
"createdAt" timestamp NOT NULL
);
CREATE TABLE IF NOT EXISTS ai_chat_test."vote" (
"chatId" uuid NOT NULL,
"messageId" uuid NOT NULL,
"isUpvoted" boolean NOT NULL,
CONSTRAINT "vote_chatId_messageId_pk" PRIMARY KEY("chatId","messageId")
);
ALTER TABLE ai_chat_test."chat" ADD COLUMN "title" text NOT NULL;
DO $$ BEGIN
ALTER TABLE ai_chat_test."message" ADD CONSTRAINT "message_chatId_chat_id_fk" FOREIGN KEY ("chatId") REFERENCES "ai_chat_test"."chat"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
WHEN duplicate_object THEN null;
END $$;
DO $$ BEGIN
ALTER TABLE ai_chat_test."vote" ADD CONSTRAINT "vote_chatId_chat_id_fk" FOREIGN KEY ("chatId") REFERENCES "ai_chat_test"."chat"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
WHEN duplicate_object THEN null;
END $$;
DO $$ BEGIN
ALTER TABLE ai_chat_test."vote" ADD CONSTRAINT "vote_messageId_message_id_fk" FOREIGN KEY ("messageId") REFERENCES "ai_chat_test"."message"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
WHEN duplicate_object THEN null;
END $$;
ALTER TABLE ai_chat_test."chat" DROP COLUMN IF EXISTS "messages";
eg) stripe
/**
* USERS
* Note: This table contains user data. Users should only be able to view and update their own data.
*/
create table users (
-- UUID from auth.users
id uuid references auth.users not null primary key,
full_name text,
avatar_url text,
-- The customer's billing address, stored in JSON format.
billing_address jsonb,
-- Stores your customer's payment instruments.
payment_method jsonb
);
alter table users
enable row level security;
create policy "Can view own user data." on users
for select using (auth.uid() = id);
create policy "Can update own user data." on users
for update using (auth.uid() = id);
/**
* This trigger automatically creates a user entry when a new user signs up via Supabase Auth.
*/
create function public.handle_new_user()
returns trigger as
$$
begin
insert into public.users (id, full_name, avatar_url)
values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');
return new;
end;
$$
language plpgsql security definer;
create trigger on_auth_user_created
after insert on auth.users
for each row
execute procedure public.handle_new_user();
/**
* CUSTOMERS
* Note: this is a private table that contains a mapping of user IDs to Strip customer IDs.
*/
create table customers (
-- UUID from auth.users
id uuid references auth.users not null primary key,
-- The user's customer ID in Stripe. User must not be able to update this.
stripe_customer_id text
);
alter table customers enable row level security;
-- No policies as this is a private table that the user must not have access to.
/**
* PRODUCTS
* Note: products are created and managed in Stripe and synced to our DB via Stripe webhooks.
*/
create table products (
-- Product ID from Stripe, e.g. prod_1234.
id text primary key,
-- Whether the product is currently available for purchase.
active boolean,
-- The product's name, meant to be displayable to the customer. Whenever this product is sold via a subscription, name will show up on associated invoice line item descriptions.
name text,
-- The product's description, meant to be displayable to the customer. Use this field to optionally store a long form explanation of the product being sold for your own rendering purposes.
description text,
-- A URL of the product image in Stripe, meant to be displayable to the customer.
image text,
-- Set of key-value pairs, used to store additional information about the object in a structured format.
metadata jsonb
);
alter table products
enable row level security;
create policy "Allow public read-only access." on products
for select using (true);
/**
* PRICES
* Note: prices are created and managed in Stripe and synced to our DB via Stripe webhooks.
*/
create type pricing_type as enum ('one_time', 'recurring');
create type pricing_plan_interval as enum ('day', 'week', 'month', 'year');
create table prices (
-- Price ID from Stripe, e.g. price_1234.
id text primary key,
-- The ID of the prduct that this price belongs to.
product_id text references products,
-- Whether the price can be used for new purchases.
active boolean,
-- A brief description of the price.
description text,
-- The unit amount as a positive integer in the smallest currency unit (e.g., 100 cents for US$1.00 or 100 for ¥100, a zero-decimal currency).
unit_amount bigint,
-- Three-letter ISO currency code, in lowercase.
currency text check (char_length(currency) = 3),
-- One of `one_time` or `recurring` depending on whether the price is for a one-time purchase or a recurring (subscription) purchase.
type pricing_type,
-- The frequency at which a subscription is billed. One of `day`, `week`, `month` or `year`.
interval pricing_plan_interval,
-- The number of intervals (specified in the `interval` attribute) between subscription billings. For example, `interval=month` and `interval_count=3` bills every 3 months.
interval_count integer,
-- Default number of trial days when subscribing a customer to this price using [`trial_from_plan=true`](https://stripe.com/docs/api#create_subscription-trial_from_plan).
trial_period_days integer,
-- Set of key-value pairs, used to store additional information about the object in a structured format.
metadata jsonb
);
alter table prices
enable row level security;
create policy "Allow public read-only access." on prices
for select using (true);
/**
* SUBSCRIPTIONS
* Note: subscriptions are created and managed in Stripe and synced to our DB via Stripe webhooks.
*/
create type subscription_status as enum ('trialing', 'active', 'canceled', 'incomplete', 'incomplete_expired', 'past_due', 'unpaid');
create table subscriptions (
-- Subscription ID from Stripe, e.g. sub_1234.
id text primary key,
user_id uuid references auth.users not null,
-- The status of the subscription object, one of subscription_status type above.
status subscription_status,
-- Set of key-value pairs, used to store additional information about the object in a structured format.
metadata jsonb,
-- ID of the price that created this subscription.
price_id text references prices,
-- Quantity multiplied by the unit amount of the price creates the amount of the subscription. Can be used to charge multiple seats.
quantity integer,
-- If true the subscription has been canceled by the user and will be deleted at the end of the billing period.
cancel_at_period_end boolean,
-- Time at which the subscription was created.
created timestamp with time zone default timezone('utc'::text, now()) not null,
-- Start of the current period that the subscription has been invoiced for.
current_period_start timestamp with time zone default timezone('utc'::text, now()) not null,
-- End of the current period that the subscription has been invoiced for. At the end of this period, a new invoice will be created.
current_period_end timestamp with time zone default timezone('utc'::text, now()) not null,
-- If the subscription has ended, the timestamp of the date the subscription ended.
ended_at timestamp with time zone default timezone('utc'::text, now()),
-- A date in the future at which the subscription will automatically get canceled.
cancel_at timestamp with time zone default timezone('utc'::text, now()),
-- If the subscription has been canceled, the date of that cancellation. If the subscription was canceled with `cancel_at_period_end`, `canceled_at` will still reflect the date of the initial cancellation request, not the end of the subscription period when the subscription is automatically moved to a canceled state.
canceled_at timestamp with time zone default timezone('utc'::text, now()),
-- If the subscription has a trial, the beginning of that trial.
trial_start timestamp with time zone default timezone('utc'::text, now()),
-- If the subscription has a trial, the end of that trial.
trial_end timestamp with time zone default timezone('utc'::text, now())
);
alter table subscriptions
enable row level security;
create policy "Can only view own subs data." on subscriptions
for select using (auth.uid() = user_id);
/**
* REALTIME SUBSCRIPTIONS
* Only allow realtime listening on public tables.
*/
drop publication if exists supabase_realtime;
create publication supabase_realtime
for table products, prices;