mirror of
https://github.com/supabase/supabase.git
synced 2026-10-09 11:25:06 +03:00
Chris Chinchilla
16ce2c1a8a
## I have read the [CONTRIBUTING.md](https://github.com/supabase/supabase/blob/master/CONTRIBUTING.md) file. YES Adds a new SQL example to the SQL editor to make following quickstarts easier. <!-- This is an auto-generated comment: release notes by coderabbit.ai --> ## Summary by CodeRabbit * **New Features** * Added a new SQL quickstart template called **“Instruments”**. * The template helps users create a sample `instruments` table, add example rows, and set up read access with row-level security. <!-- end of auto-generated comment: release notes by coderabbit.ai -->
1637 lines
63 KiB
TypeScript
1637 lines
63 KiB
TypeScript
import type { SQLTemplate } from './SQLEditor.types'
|
|
import { DOCS_URL } from '@/lib/constants'
|
|
|
|
export const SQL_TEMPLATES: SQLTemplate[] = [
|
|
{
|
|
id: 1,
|
|
type: 'template',
|
|
title: 'Create table',
|
|
description: 'Basic table template. Change "table_name" to the name you prefer.',
|
|
sql: `create table table_name (
|
|
id bigint generated by default as identity primary key,
|
|
inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
|
|
updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
|
|
data jsonb,
|
|
name text
|
|
);`,
|
|
},
|
|
{
|
|
id: 2,
|
|
type: 'template',
|
|
title: 'Add view',
|
|
description:
|
|
'Template to add a view. Make sure to change the table and column names to ones that already exist.',
|
|
sql: `CREATE VIEW countries_view AS
|
|
SELECT id, continent
|
|
FROM countries;`,
|
|
},
|
|
{
|
|
id: 3,
|
|
type: 'template',
|
|
title: 'Add column',
|
|
description: 'Template to add a column. Make sure to change the name and type.',
|
|
sql: `alter table table_name
|
|
add column new_column_name data_type;`,
|
|
},
|
|
{
|
|
id: 4,
|
|
type: 'template',
|
|
title: 'Add comments',
|
|
description: 'Templates to add a comment to either a table or a column.',
|
|
sql: `comment on table table_name is 'Table description';
|
|
comment on column table_name.column_name is 'Column description';`,
|
|
},
|
|
{
|
|
id: 5,
|
|
type: 'template',
|
|
title: 'Show extensions',
|
|
description: 'Get a list of extensions in your database and status.',
|
|
sql: `select
|
|
name, comment, default_version, installed_version
|
|
from
|
|
pg_available_extensions
|
|
order by
|
|
name asc;`,
|
|
},
|
|
{
|
|
id: 6,
|
|
type: 'template',
|
|
title: 'Show version',
|
|
description: 'Get your Postgres version.',
|
|
sql: `select * from
|
|
(select version()) as version,
|
|
(select current_setting('server_version_num')) as version_number;`,
|
|
},
|
|
{
|
|
id: 7,
|
|
type: 'template',
|
|
title: 'Show active connections',
|
|
description: 'Get the number of active and max connections.',
|
|
sql: `select * from
|
|
(select count(pid) as active_connections FROM pg_stat_activity where state = 'active') active_connections,
|
|
(select setting as max_connections from pg_settings where name = 'max_connections') max_connections;`,
|
|
},
|
|
{
|
|
id: 8,
|
|
type: 'template',
|
|
title: 'Automatically update timestamps',
|
|
description: 'Update a column timestamp on every update.',
|
|
sql: `
|
|
create extension if not exists moddatetime schema extensions;
|
|
|
|
-- assuming the table name is "todos", and a timestamp column "updated_at"
|
|
-- this trigger will set the "updated_at" column to the current timestamp for every update
|
|
create trigger
|
|
handle_updated_at before update
|
|
on todos
|
|
for each row execute
|
|
procedure moddatetime(updated_at);
|
|
`.trim(),
|
|
},
|
|
{
|
|
id: 9,
|
|
type: 'template',
|
|
title: 'Increment field value',
|
|
description: 'Update a field with incrementing value using a function.',
|
|
sql: `
|
|
create function increment(row_id int)
|
|
returns void as
|
|
$$
|
|
update table_name
|
|
set field_name = field_name + 1
|
|
where id = row_id;
|
|
$$
|
|
language sql volatile;
|
|
|
|
-- you can call the function from your browser with supabase-js
|
|
-- const { data, error } = await supabase.rpc('increment', { row_id: 2 })
|
|
`.trim(),
|
|
},
|
|
{
|
|
id: 10,
|
|
type: 'template',
|
|
title: 'pg_stat_statements report',
|
|
description: 'Select from pg_stat_statements and view recent queries',
|
|
sql: `-- pg_stat_statements report
|
|
|
|
-- A limit of 100 has been added below
|
|
|
|
select
|
|
auth.rolname,
|
|
statements.query,
|
|
statements.calls,
|
|
-- -- Postgres 13, 14
|
|
statements.total_exec_time + statements.total_plan_time as total_time,
|
|
statements.min_exec_time + statements.min_plan_time as min_time,
|
|
statements.max_exec_time + statements.max_plan_time as max_time,
|
|
statements.mean_exec_time + statements.mean_plan_time as mean_time,
|
|
-- -- Postgres <= 12
|
|
-- total_time,
|
|
-- min_time,
|
|
-- max_time,
|
|
-- mean_time,
|
|
statements.rows / statements.calls as avg_rows,
|
|
statements.wal_bytes,
|
|
statements.wal_records
|
|
from pg_stat_statements as statements
|
|
inner join pg_authid as auth on statements.userid = auth.oid
|
|
order by
|
|
total_time desc
|
|
limit
|
|
100;`,
|
|
},
|
|
{
|
|
id: 11,
|
|
type: 'quickstart',
|
|
title: 'Colors',
|
|
description: 'Create a table with a list of colors and their hex values.',
|
|
sql: `-- Information from Wikipedia "List of Colors"
|
|
CREATE TYPE public.color_source AS ENUM (
|
|
'99COLORS_NET',
|
|
'ART_PAINTS_YG07S',
|
|
'BYRNE',
|
|
'CRAYOLA',
|
|
'CMYK_COLOR_MODEL',
|
|
'COLORCODE_IS',
|
|
'COLORHEXA',
|
|
'COLORXS',
|
|
'CORNELL_UNIVERSITY',
|
|
'COLUMBIA_UNIVERSITY',
|
|
'DUKE_UNIVERSITY',
|
|
'ENCYCOLORPEDIA_COM',
|
|
'ETON_COLLEGE',
|
|
'FANTETTI_AND_PETRACCHI',
|
|
'FINDTHEDATA_COM',
|
|
'FERRARIO_1919',
|
|
'FEDERAL_STANDARD_595',
|
|
'FLAG_OF_INDIA',
|
|
'FLAG_OF_SOUTH_AFRICA',
|
|
'GLAZEBROOK_AND_BALDRY',
|
|
'GOOGLE',
|
|
'HEXCOLOR_CO',
|
|
'ISCC_NBS',
|
|
'KELLY_MOORE',
|
|
'MATTEL',
|
|
'MAERZ_AND_PAUL',
|
|
'MILK_PAINT',
|
|
'MUNSELL_COLOR_WHEEL',
|
|
'NATURAL_COLOR_SYSTEM',
|
|
'PANTONE',
|
|
'PLOCHERE',
|
|
'POURPRE_COM',
|
|
'RAL',
|
|
'RESENE',
|
|
'RGB_COLOR_MODEL',
|
|
'THOM_POOLE',
|
|
'UNIVERSITY_OF_ALABAMA',
|
|
'UNIVERSITY_OF_CALIFORNIA_DAVIS',
|
|
'UNIVERSITY_OF_CAMBRIDGE',
|
|
'UNIVERSITY_OF_NORTH_CAROLINA',
|
|
'UNIVERSITY_OF_TEXAS_AT_AUSTIN',
|
|
'X11_WEB',
|
|
'XONA_COM'
|
|
);
|
|
|
|
create table public.colors (
|
|
id bigint generated by default as identity primary key,
|
|
name text,
|
|
hex text not null,
|
|
red int2,
|
|
green int2,
|
|
blue int2,
|
|
hue int2,
|
|
sat_hsl int2,
|
|
light_hsl int2,
|
|
sat_hsv int2,
|
|
val_hsv int2,
|
|
source color_source
|
|
);
|
|
|
|
comment on table colors is 'Full list of colors (based on various sources)';
|
|
comment on column colors.name is 'Name of the color';
|
|
comment on column colors.hex is 'Hex tripliets of the color for HTML web colors';
|
|
comment on column colors.red is 'Red in RGB (%)';
|
|
comment on column colors.green is 'Green in RGB (%)';
|
|
comment on column colors.blue is 'Blue in RGB (%)';
|
|
comment on column colors.hue is 'Hue in HSL (°)';
|
|
comment on column colors.sat_hsl is 'Saturation in HSL (%)';
|
|
comment on column colors.light_hsl is 'Light in HSL (%)';
|
|
comment on column colors.sat_hsv is 'Saturation in HSV (%)';
|
|
comment on column colors.val_hsv is 'Value in HSV (%)';
|
|
comment on column colors.source is 'Source of information on the color';
|
|
|
|
insert into public.colors (name, hex, red, green, blue, hue, sat_hsl, light_hsl, sat_hsv, val_hsv, source) values
|
|
('Absolute Zero', '#0048BA', 0, 28, 73, 217, 100, 37, 100, 73, 'CRAYOLA'),
|
|
('Acid green', '#B0BF1A', 69, 75, 10, 65, 76, 43, 76, 75, 'ART_PAINTS_YG07S'),
|
|
('Aero', '#7CB9E8', 49, 73, 91, 206, 70, 70, 47, 91, 'MAERZ_AND_PAUL'),
|
|
('African violet', '#B284BE', 70, 52, 75, 288, 31, 63, 31.5, '75', 'PANTONE'),
|
|
('Air superiority blue', '#72A0C1', 45, 63, 76, 205, 39, 60, 41, 76, 'FEDERAL_STANDARD_595'),
|
|
('Alice blue', '#F0F8FF', 94, 97, 100, 208, 100, 97, 6, 100, 'X11_WEB'),
|
|
('Alizarin', '#DB2D43', 86, 18, 26, 352, 71, 52, 79, 86, 'MAERZ_AND_PAUL'),
|
|
('Alloy orange', '#C46210', 77, 38, 6, 27, 85, 42, 92, 77, 'CRAYOLA'),
|
|
('Almond', '#EED9C4', 93, 85, 77, 30, 55, 85, 18, 93, 'CRAYOLA'),
|
|
('Amaranth deep purple', '#9F2B68', 62, 17, 41, 328, 57, 40, 73, 62, 'MAERZ_AND_PAUL'),
|
|
('Amaranth pink', '#F19CBB', 95, 61, 73, 338, 75, 78, 35, 95, 'MAERZ_AND_PAUL'),
|
|
('Amaranth purple', '#AB274F', 67, 15, 31, 342, 63, 41, 77, 67, 'MAERZ_AND_PAUL'),
|
|
('Amazon', '#3B7A57', 23, 48, 34, 147, 35, 36, 52, 48, 'XONA_COM'),
|
|
('Amber', '#FFBF00', 100, 75, 0, 45, 100, 50, 100, 100, 'RGB_COLOR_MODEL'),
|
|
('Amethyst', '#9966CC', 60, 40, 80, 270, 50, 60, 50, 80, 'X11_WEB'),
|
|
('Android green', '#3DDC84', 24, 86, 53, 148, 69, 55, 72, 86, 'GOOGLE'),
|
|
('Antique brass', '#C88A65', 78, 54, 40, 22, 47, 59, 49, 78, 'CRAYOLA'),
|
|
('Antique bronze', '#665D1E', 40, 36, 12, 53, 55, 26, 71, 40, 'ISCC_NBS'),
|
|
('Antique fuchsia', '#915C83', 57, 36, 51, 316, 22, 46, 37, 57, 'PLOCHERE'),
|
|
('Antique ruby', '#841B2D', 52, 11, 18, 350, 66, 31, 80, 52, 'ISCC_NBS'),
|
|
('Antique white', '#FAEBD7', 98, 92, 84, 34, 78, 91, 14, 98, 'X11_WEB'),
|
|
('Apricot', '#FBCEB1', 98, 81, 69, 24, 90, 84, 29, 98, 'MAERZ_AND_PAUL'),
|
|
('Aqua', '#00FFFF', 0, 100, 100, 180, 100, 50, 100, 100, 'X11_WEB'),
|
|
('Aquamarine', '#7FFFD4', 50, 100, 83, 160, 100, 75, 50, 100, 'X11_WEB'),
|
|
('Arctic lime', '#D0FF14', 82, 100, 8, 72, 100, 54, 92, 100, 'CRAYOLA'),
|
|
('Artichoke green', '#4B6F44', 29, 44, 27, 110, 24, 35, 39, 44, 'PANTONE'),
|
|
('Arylide yellow', '#E9D66B', 91, 84, 42, 51, 74, 67, 54, 91, 'COLORHEXA'),
|
|
('Ash gray', '#B2BEB5', 70, 75, 71, 135, 9, 72, 6, 75, 'ISCC_NBS'),
|
|
('Atomic tangerine', '#FF9966', 100, 60, 40, 20, 100, 70, 60, 100, 'CRAYOLA'),
|
|
('Aureolin', '#FDEE00', 99, 93, 0, 56, 100, 50, 100, 99, 'X11_WEB'),
|
|
('Azure', '#007FFF', 0, 50, 100, 210, 100, 50, 100, 100, 'RGB_COLOR_MODEL'),
|
|
('Azure (X11/web color)', '#F0FFFF', 94, 100, 100, 180, 100, 97, 6, 100, 'X11_WEB'),
|
|
('Baby blue', '#89CFF0', 54, 81, 94, 199, 77, 74, 43, 94, 'MAERZ_AND_PAUL'),
|
|
('Baby blue eyes', '#A1CAF1', 63, 79, 95, 209, 74, 79, 33, 95, 'PLOCHERE'),
|
|
('Baby pink', '#F4C2C2', 96, 76, 76, 0, 69, 86, 20, 96, 'ISCC_NBS'),
|
|
('Baby powder', '#FEFEFA', 100, 100, 98, 60, 67, 99, 2, 100, 'CRAYOLA'),
|
|
('Baker-Miller pink', '#FF91AF', 100, 57, 69, 344, 100, 78, 43, 100, 'BYRNE'),
|
|
('Banana Mania', '#FAE7B5', 98, 91, 71, 43, 87, 85, 28, 98, 'CRAYOLA'),
|
|
('Barbie Pink', '#DA1884', 85, 9, 52, 327, 80, 48, 89, 85, 'MATTEL'),
|
|
('Barn red', '#7C0A02', 49, 4, 1, 4, 97, 25, 98, 49, 'MILK_PAINT'),
|
|
('Battleship grey', '#848482', 52, 52, 51, 60, 1, 51, 2, 52, 'ISCC_NBS'),
|
|
('Beau blue', '#BCD4E6', 74, 83, 90, 206, 46, 82, 18, 90, 'PLOCHERE'),
|
|
('Beaver', '#9F8170', 62, 51, 44, 22, 20, 53, 30, 62, 'CRAYOLA'),
|
|
('Beige', '#F5F5DC', 96, 96, 86, 60, 56, 91, 10, 96, 'X11_WEB'),
|
|
('B''dazzled blue', '#2E5894', 18, 35, 58, 215, 53, 38, 69, 58, 'CRAYOLA'),
|
|
('Big dip o''ruby', '#9C2542', 61, 15, 26, 345, 62, 38, 76, 61, 'CRAYOLA'),
|
|
('Bisque', '#FFE4C4', 100, 89, 77, 33, 100, 88, 23, 100, 'X11_WEB'),
|
|
('Bistre', '#3D2B1F', 24, 17, 12, 24, 33, 18, 49, 24, '99COLORS_NET'),
|
|
('Bistre brown', '#967117', 59, 44, 9, 43, 73, 34, 85, 59, 'ISCC_NBS'),
|
|
('Bitter lemon', '#CAE00D', 79, 88, 5, 66, 89, 47, 94, 88, 'XONA_COM'),
|
|
('Black', '#000000', 0, 0, 0, 0, 0, 0, 0, 0, 'RGB_COLOR_MODEL'),
|
|
('Black bean', '#3D0C02', 24, 5, 1, 10, 94, 12, 97, 24, 'XONA_COM'),
|
|
('Black coral', '#54626F', 33, 38, 44, 209, 14, 38, 24, 44, 'CRAYOLA'),
|
|
('Black olive', '#3B3C36', 23, 24, 21, 70, 5, 22, 10, 24, 'RAL'),
|
|
('Black Shadows', '#BFAFB2', 75, 69, 70, 349, 11, 72, 8, 75, 'CRAYOLA'),
|
|
('Blanched almond', '#FFEBCD', 100, 92, 80, 36, 100, 90, 20, 100, 'X11_WEB'),
|
|
('Blast-off bronze', '#A57164', 65, 44, 39, 12, 27, 52, 39, 65, 'CRAYOLA'),
|
|
('Bleu de France', '#318CE7', 19, 55, 91, 210, 79, 55, 79, 91, 'POURPRE_COM'),
|
|
('Blizzard blue', '#ACE5EE', 67, 90, 93, 188, 66, 80, 28, 93, 'CRAYOLA'),
|
|
('Blood red', '#660000', 40, 0, 0, 0, 100, 20, 100, 40, 'THOM_POOLE'),
|
|
('Blue', '#0000FF', 0, 0, 100, 240, 100, 50, 100, 100, 'X11_WEB'),
|
|
('Blue (Crayola)', '#1F75FE', 12, 46, 100, 217, 99, 56, 88, 100, 'CRAYOLA'),
|
|
('Blue (Munsell)', '#0093AF', 0, 58, 69, 190, 100, 34, 100, 69, 'MUNSELL_COLOR_WHEEL'),
|
|
('Blue (NCS)', '#0087BD', 0, 53, 74, 197, 100, 37, 100, 74, 'NATURAL_COLOR_SYSTEM'),
|
|
('Blue (Pantone)', '#0018A8', 0, 9, 66, 231, 100, 33, 100, 66, 'PANTONE'),
|
|
('Blue (pigment)', '#333399', 20, 20, 60, 240, 50, 40, 67, 60, 'CMYK_COLOR_MODEL'),
|
|
('Blue bell', '#A2A2D0', 64, 64, 82, 240, 33, 73, 22, 82, 'CRAYOLA'),
|
|
('Blue-gray (Crayola)', '#6699CC', 40, 60, 80, 210, 50, 60, 50, 80, 'CRAYOLA'),
|
|
('Blue jeans', '#5DADEC', 36, 68, 93, 206, 79, 65, 61, 93, 'CRAYOLA'),
|
|
('Blue sapphire', '#126180', 7, 38, 50, 197, 75, 29, 86, 50, 'PANTONE'),
|
|
('Blue-violet', '#8A2BE2', 54, 17, 89, 271, 76, 53, 81, 89, 'X11_WEB'),
|
|
('Blue yonder', '#5072A7', 31, 45, 65, 217, 35, 48, 52, 65, 'PANTONE'),
|
|
('Bluetiful', '#3C69E7', 24, 41, 91, 224, 78, 57, 74, 91, 'CRAYOLA'),
|
|
('Blush', '#DE5D83', 87, 36, 51, 342, 66, 62, 58, 87, 'CRAYOLA'),
|
|
('Bole', '#79443B', 47, 27, 23, 9, 34, 35, 51, 47, 'ISCC_NBS'),
|
|
('Bone', '#E3DAC9', 89, 85, 79, 39, 32, 84, 11, 89, 'KELLY_MOORE'),
|
|
('Brick red', '#CB4154', 80, 25, 33, 352, 57, 53, 68, 80, 'CRAYOLA'),
|
|
('Bright lilac', '#D891EF', 85, 57, 94, 285, 75, 75, 39, 94, 'CRAYOLA'),
|
|
('Bright yellow (Crayola)', '#FFAA1D', 100, 67, 11, 37, 100, 56, 89, 100, 'CRAYOLA'),
|
|
('British racing green', '#004225', 0, 26, 15, 154, 100, 13, 100, 26, 'COLORHEXA'),
|
|
('Bronze', '#CD7F32', 80, 50, 20, 30, 61, 50, 76, 80, 'MAERZ_AND_PAUL'),
|
|
('Brown', '#964B00', 59, 29, 0, 30, 100, 29, 100, 59, 'COLORXS'),
|
|
('Brown sugar', '#AF6E4D', 69, 43, 30, 20, 39, 49, 56, 69, 'CRAYOLA'),
|
|
('Bud green', '#7BB661', 48, 71, 38, 102, 37, 55, 47, 71, 'PANTONE'),
|
|
('Buff', '#FFC680', 100, 78, 50, 33, 100, 75, 50, 100, 'MAERZ_AND_PAUL'),
|
|
('Burgundy', '#800020', 50, 0, 13, 345, 100, 25, 100, 50, 'MAERZ_AND_PAUL'),
|
|
('Burlywood', '#DEB887', 87, 72, 53, 34, 57, 70, 39, 87, 'X11_WEB'),
|
|
('Burnished brown', '#A17A74', 63, 48, 45, 8, 19, 54, 28, 63, 'CRAYOLA'),
|
|
('Burnt orange', '#CC5500', 80, 33, 0, 25, 100, 40, 100, 80, 'UNIVERSITY_OF_TEXAS_AT_AUSTIN'),
|
|
('Burnt sienna', '#E97451', 91, 45, 32, 14, 78, 62, 65, 91, 'FERRARIO_1919'),
|
|
('Burnt umber', '#8A3324', 54, 20, 14, 9, 59, 34, 74, 54, 'XONA_COM'),
|
|
('Byzantine', '#BD33A4', 74, 20, 64, 311, 58, 47, 73, 74, 'MAERZ_AND_PAUL'),
|
|
('Byzantium', '#702963', 44, 16, 39, 311, 46, 30, 63, 44, 'ISCC_NBS'),
|
|
('Cadet blue', '#5F9EA0', 37, 62, 63, 182, 26, 50, 41, 63, 'X11_WEB'),
|
|
('Cadet grey', '#91A3B0', 57, 64, 69, 205, 16, 63, 18, 69, 'ISCC_NBS'),
|
|
('Cadmium green', '#006B3C', 0, 42, 24, 154, 100, 21, 100, 42, 'ISCC_NBS'),
|
|
('Cadmium orange', '#ED872D', 93, 53, 18, 28, 84, 55, 81, 93, 'ISCC_NBS'),
|
|
('Café au lait', '#A67B5B', 65, 48, 36, 26, 30, 50, 45, 65, 'ISCC_NBS'),
|
|
('Café noir', '#4B3621', 29, 21, 13, 30, 39, 21, 56, 29, 'ISCC_NBS'),
|
|
('Cambridge blue', '#A3C1AD', 64, 76, 68, 140, 20, 70, 16, 76, 'UNIVERSITY_OF_CAMBRIDGE'),
|
|
('Camel', '#C19A6B', 76, 60, 42, 33, 41, 59, 45, 76, 'ISCC_NBS'),
|
|
('Cameo pink', '#EFBBCC', 94, 73, 80, 340, 62, 84, 22, 94, 'ISCC_NBS'),
|
|
('Canary', '#FFFF99', 100, 100, 60, 60, 100, 80, 40, 100, 'CRAYOLA'),
|
|
('Canary yellow', '#FFEF00', 100, 94, 0, 56, 100, 50, 100, 100, 'CMYK_COLOR_MODEL'),
|
|
('Candy pink', '#E4717A', 89, 44, 48, 355, 68, 67, 50, 89, 'ISCC_NBS'),
|
|
('Cardinal', '#C41E3A', 77, 12, 23, 350, 74, 44, 85, 77, 'MAERZ_AND_PAUL'),
|
|
('Caribbean green', '#00CC99', 0, 80, 60, 165, 100, 40, 100, 80, 'CRAYOLA'),
|
|
('Carmine', '#960018', 59, 0, 9, 350, 100, 29, 100, 59, 'POURPRE_COM'),
|
|
('Carmine (M&P)', '#D70040', 84, 0, 25, 342, 100, 42, 100, 84, 'MAERZ_AND_PAUL'),
|
|
('Carnation pink', '#FFA6C9', 100, 65, 79, 336, 100, 83, 35, 100, 'CRAYOLA'),
|
|
('Carnelian', '#B31B1B', 70, 11, 11, 0, 74, 40, 85, 70, 'CORNELL_UNIVERSITY'),
|
|
('Carolina blue', '#56A0D3', 34, 63, 83, 204, 59, 58, 59, 83, 'UNIVERSITY_OF_NORTH_CAROLINA'),
|
|
('Carrot orange', '#ED9121', 93, 57, 13, 33, 85, 53, 86, 93, 'MAERZ_AND_PAUL'),
|
|
('Catawba', '#703642', 44, 21, 26, 348, 35, 33, 52, 44, 'MAERZ_AND_PAUL'),
|
|
('Cedar Chest', '#C95A49', 79, 35, 29, 8, 54, 54, 64, 79, 'CRAYOLA'),
|
|
('Celadon', '#ACE1AF', 67, 88, 69, 123, 47, 78, 24, 88, 'ENCYCOLORPEDIA_COM'),
|
|
('Celeste', '#B2FFFF', 70, 100, 100, 180, 100, 85, 30, 100, 'FANTETTI_AND_PETRACCHI'),
|
|
('Cerise', '#DE3163', 87, 19, 39, 343, 72, 53, 78, 87, 'MAERZ_AND_PAUL'),
|
|
('Cerulean', '#007BA7', 0, 48, 65, 196, 100, 33, 100, 65, 'MAERZ_AND_PAUL'),
|
|
('Cerulean blue', '#2A52BE', 16, 32, 75, 224, 64, 46, 78, 75, 'MAERZ_AND_PAUL'),
|
|
('Cerulean frost', '#6D9BC3', 43, 61, 76, 208, 42, 60, 44, 76, 'CRAYOLA'),
|
|
('Cerulean (Crayola)', '#1DACD6', 11, 67, 84, 194, 76, 48, 86, 84, 'CRAYOLA'),
|
|
('Cerulean (RGB)', '#0040FF', 0, 25, 100, 225, 100, 50, 100, 100, null),
|
|
('Champagne', '#F7E7CE', 97, 91, 81, 37, 72, 89, 17, 97, 'MAERZ_AND_PAUL'),
|
|
('Champagne pink', '#F1DDCF', 95, 87, 81, 25, 55, 88, 14, 95, 'PANTONE'),
|
|
('Charcoal', '#36454F', 21, 27, 31, 204, 19, 26, 32, 31, 'ISCC_NBS'),
|
|
('Charm pink', '#E68FAC', 90, 56, 67, 340, 64, 73, 38, 90, 'PLOCHERE'),
|
|
('Chartreuse (web)', '#80FF00', 50, 100, 0, 90, 100, 50, 100, 100, 'RGB_COLOR_MODEL'),
|
|
('Cherry blossom pink', '#FFB7C5', 100, 72, 77, 348, 100, 86, 28, 100, 'MAERZ_AND_PAUL'),
|
|
('Chestnut', '#954535', 58, 27, 21, 10, 48, 40, 64, 58, 'MAERZ_AND_PAUL'),
|
|
('Chili red', '#E23D28', 89, 24, 16, 5, 76, 52, 183, 125, 'FLAG_OF_SOUTH_AFRICA'),
|
|
('China pink', '#DE6FA1', 87, 44, 63, 333, 63, 65, 50, 87, 'PLOCHERE'),
|
|
('Chinese red', '#AA381E', 67, 22, 12, 11, 70, 39, 82, 67, 'ISCC_NBS'),
|
|
('Chinese violet', '#856088', 52, 38, 53, 296, 17, 46, 29, 53, 'PANTONE'),
|
|
('Chinese yellow', '#FFB200', 100, 70, 0, 42, 100, 50, 100, 100, 'ISCC_NBS'),
|
|
('Chocolate (traditional)', '#7B3F00', 48, 25, 0, 31, 100, 24, 100, 48, 'MAERZ_AND_PAUL'),
|
|
('Chocolate (web)', '#D2691E', 82, 41, 12, 25, 75, 47, 86, 82, 'X11_WEB'),
|
|
('Cinereous', '#98817B', 60, 51, 48, 12, 12, 54, 19, 60, 'MAERZ_AND_PAUL'),
|
|
('Cinnabar', '#E34234', 89, 26, 20, 5, 76, 55, 77, 89, 'MAERZ_AND_PAUL'),
|
|
('Cinnamon Satin', '#CD607E', 80, 38, 49, 343, 52, 59, 53, 80, 'CRAYOLA'),
|
|
('Citrine', '#E4D00A', 89, 82, 4, 54, 92, 47, 96, 89, 'MAERZ_AND_PAUL'),
|
|
('Citron', '#9FA91F', 62, 66, 12, 64, 69, 39, 82, 66, 'XONA_COM'),
|
|
('Claret', '#7F1734', 50, 9, 20, 343, 69, 29, 82, 50, 'XONA_COM'),
|
|
('Coffee', '#6F4E37', 44, 31, 22, 25, 34, 33, 50, 44, 'ISCC_NBS'),
|
|
('Columbia Blue', '#B9D9EB', 73, 85, 92, 202, 56, 82, 21, 92, 'COLUMBIA_UNIVERSITY'),
|
|
('Congo pink', '#F88379', 97, 51, 47, 5, 90, 72, 51, 97, 'ISCC_NBS'),
|
|
('Cool grey', '#8C92AC', 55, 57, 67, 229, 16, 61, 19, 67, 'ISCC_NBS'),
|
|
('Copper', '#B87333', 72, 45, 20, 29, 57, 46, 72, 72, 'MAERZ_AND_PAUL'),
|
|
('Copper (Crayola)', '#DA8A67', 85, 54, 40, 18, 61, 63, 53, 85, 'CRAYOLA'),
|
|
('Copper penny', '#AD6F69', 68, 44, 41, 5, 29, 55, 39, 68, 'CRAYOLA'),
|
|
('Copper red', '#CB6D51', 80, 43, 32, 14, 54, 56, 60, 80, 'ISCC_NBS'),
|
|
('Copper rose', '#996666', 60, 40, 40, 0, 20, 50, 33, 60, '99COLORS_NET'),
|
|
('Coquelicot', '#FF3800', 100, 22, 0, 13, 100, 50, 100, 100, 'COLORHEXA'),
|
|
('Coral', '#FF7F50', 100, 50, 31, 16, 100, 66, 69, 100, 'X11_WEB'),
|
|
('Coral pink', '#F88379', 97, 51, 47, 5, 90, 72, 51, 97, 'ISCC_NBS'),
|
|
('Cordovan', '#893F45', 54, 25, 27, 355, 37, 39, 54, 54, 'PANTONE'),
|
|
('Corn', '#FBEC5D', 98, 93, 36, 54, 95, 68, 63, 98, 'MAERZ_AND_PAUL'),
|
|
('Cornflower blue', '#6495ED', 39, 58, 93, 219, 79, 66, 58, 93, 'X11_WEB'),
|
|
('Cornsilk', '#FFF8DC', 100, 97, 86, 48, 100, 93, 14, 100, 'X11_WEB'),
|
|
('Cosmic cobalt', '#2E2D88', 18, 18, 53, 241, 50, 36, 67, 53, 'CRAYOLA'),
|
|
('Cosmic latte', '#FFF8E7', 100, 97, 91, 43, 100, 95, 9, 100, 'GLAZEBROOK_AND_BALDRY'),
|
|
('Coyote brown', '#81613C', 51, 38, 24, 32, 37, 37, 52, 51, 'COLORCODE_IS'),
|
|
('Cotton candy', '#FFBCD9', 100, 74, 85, 334, 100, 87, 26, 100, 'CRAYOLA'),
|
|
('Cream', '#FFFDD0', 100, 99, 82, 57, 100, 91, 18, 100, 'MAERZ_AND_PAUL'),
|
|
('Crimson', '#DC143C', 86, 8, 24, 348, 83, 47, 91, 86, 'X11_WEB'),
|
|
('Crimson (UA)', '#9E1B32', 62, 11, 20, 349, 71, 36, 83, 62, 'UNIVERSITY_OF_ALABAMA'),
|
|
('Cultured Pearl', '#F5F5F5', 96, 96, 96, 0, 0, 96, 0, 96, 'CRAYOLA'),
|
|
('Cyan', '#00FFFF', 0, 100, 100, 180, 100, 50, 100, 100, 'X11_WEB'),
|
|
('Cyan (process)', '#00B7EB', 0, 72, 92, 193, 100, 46, 100, 92, 'CMYK_COLOR_MODEL'),
|
|
('Cyber grape', '#58427C', 35, 26, 49, 263, 31, 37, 47, 49, 'CRAYOLA'),
|
|
('Cyber yellow', '#FFD300', 100, 83, 0, 50, 100, 50, 100, 100, 'PANTONE'),
|
|
('Cyclamen', '#F56FA1', 96, 44, 63, 338, 87, 70, 54, 96, 'CRAYOLA'),
|
|
('Dandelion', '#FED85D', 100, 85, 36, 46, 99, 68, 63, 100, 'CRAYOLA'),
|
|
('Dark brown', '#654321', 40, 26, 13, 30, 51, 26, 67, 40, 'X11_WEB'),
|
|
('Dark byzantium', '#5D3954', 36, 22, 33, 315, 24, 29, 39, 36, 'ISCC_NBS'),
|
|
('Dark cyan', '#008B8B', 0, 55, 55, 180, 100, 27, 100, 55, 'X11_WEB'),
|
|
('Dark electric blue', '#536878', 33, 41, 47, 206, 18, 40, 31, 47, 'ISCC_NBS'),
|
|
('Dark goldenrod', '#B8860B', 72, 53, 4, 43, 89, 38, 94, 72, 'X11_WEB'),
|
|
('Dark green (X11)', '#006400', 0, 39, 0, 120, 100, 20, 100, 39, 'X11_WEB'),
|
|
('Dark jungle green', '#1A2421', 10, 14, 13, 162, 16, 12, 28, 14, 'ISCC_NBS'),
|
|
('Dark khaki', '#BDB76B', 74, 72, 42, 56, 38, 58, 43, 74, 'X11_WEB'),
|
|
('Dark lava', '#483C32', 28, 24, 20, 27, 18, 24, 31, 28, 'ISCC_NBS'),
|
|
('Dark liver (horses)', '#543D37', 33, 24, 22, 12, 21, 27, 35, 33, 'UNIVERSITY_OF_CALIFORNIA_DAVIS'),
|
|
('Dark magenta', '#8B008B', 55, 0, 55, 300, 100, 27, 100, 55, 'X11_WEB'),
|
|
('Dark olive green', '#556B2F', 33, 42, 18, 82, 39, 30, 56, 42, 'X11_WEB'),
|
|
('Dark orange', '#FF8C00', 100, 55, 0, 33, 100, 50, 100, 100, 'X11_WEB'),
|
|
('Dark orchid', '#9932CC', 60, 20, 80, 280, 61, 50, 75, 80, 'X11_WEB'),
|
|
('Dark purple', '#301934', 19, 10, 20, 291, 35, 15, 51, 20, 'ISCC_NBS'),
|
|
('Dark red', '#8B0000', 55, 0, 0, 0, 100, 27, 100, 55, 'X11_WEB'),
|
|
('Dark salmon', '#E9967A', 91, 59, 48, 15, 72, 70, 48, 91, 'X11_WEB'),
|
|
('Dark sea green', '#8FBC8F', 56, 74, 56, 120, 25, 65, 24, 74, 'X11_WEB'),
|
|
('Dark sienna', '#3C1414', 24, 8, 8, 0, 50, 16, 67, 24, 'ISCC_NBS'),
|
|
('Dark sky blue', '#8CBED6', 55, 75, 84, 199, 47, 69, 35, 84, 'PANTONE'),
|
|
('Dark slate blue', '#483D8B', 28, 24, 55, 248, 39, 39, 56, 55, 'X11_WEB'),
|
|
('Dark slate gray', '#2F4F4F', 18, 31, 31, 180, 25, 25, 41, 31, 'X11_WEB'),
|
|
('Dark spring green', '#177245', 9, 45, 27, 150, 66, 27, 80, 45, 'X11_WEB'),
|
|
('Dark turquoise', '#00CED1', 0, 81, 82, 181, 100, 41, 100, 82, 'X11_WEB'),
|
|
('Dark violet', '#9400D3', 58, 0, 83, 282, 100, 41, 100, 83, 'X11_WEB'),
|
|
('Davy''s grey', '#555555', 33, 33, 33, 0, 0, 33, 0, 33, 'ISCC_NBS'),
|
|
('Deep cerise', '#DA3287', 85, 20, 53, 330, 69, 53, 77, 85, 'CRAYOLA'),
|
|
('Deep champagne', '#FAD6A5', 98, 84, 65, 35, 90, 81, 34, 98, 'ISCC_NBS'),
|
|
('Deep chestnut', '#B94E48', 73, 31, 28, 3, 45, 50, 61, 73, 'CRAYOLA'),
|
|
('Deep jungle green', '#004B49', 0, 29, 29, 178, 100, 15, 100, 29, 'ISCC_NBS'),
|
|
('Deep pink', '#FF1493', 100, 8, 58, 328, 100, 54, 92, 100, 'X11_WEB'),
|
|
('Deep saffron', '#FF9933', 100, 60, 20, 30, 100, 60, 80, 100, 'FLAG_OF_INDIA'),
|
|
('Deep sky blue', '#00BFFF', 0, 75, 100, 195, 100, 50, 100, 100, 'X11_WEB'),
|
|
('Deep Space Sparkle', '#4A646C', 29, 39, 42, 194, 19, 36, 31, 42, 'CRAYOLA'),
|
|
('Deep taupe', '#7E5E60', 49, 37, 38, 356, 15, 43, 25, 49, 'PANTONE'),
|
|
('Denim', '#1560BD', 8, 38, 74, 213, 80, 41, 89, 74, 'CRAYOLA'),
|
|
('Denim blue', '#2243B6', 13, 26, 71, 227, 69, 42, 81, 71, 'CRAYOLA'),
|
|
('Desert', '#C19A6B', 76, 60, 42, 33, 41, 59, 45, 76, 'ISCC_NBS'),
|
|
('Desert sand', '#EDC9AF', 93, 79, 69, 25, 63, 81, 26, 93, 'CRAYOLA'),
|
|
('Dim gray', '#696969', 41, 41, 41, 0, 0, 41, 0, 41, 'X11_WEB'),
|
|
('Dodger blue', '#1E90FF', 12, 56, 100, 210, 100, 56, 88, 100, 'X11_WEB'),
|
|
('Drab dark brown', '#4A412A', 29, 25, 16, 43, 28, 23, 43, 29, 'PANTONE'),
|
|
('Duke blue', '#00009C', 0, 0, 61, 240, 100, 31, 100, 61, 'DUKE_UNIVERSITY'),
|
|
('Dutch white', '#EFDFBB', 94, 87, 73, 42, 62, 84, 22, 94, 'RESENE'),
|
|
('Ebony', '#555D50', 33, 36, 31, 97, 8, 34, 14, 36, 'MAERZ_AND_PAUL'),
|
|
('Ecru', '#C2B280', 76, 70, 50, 45, 35, 63, 34, 76, 'ISCC_NBS'),
|
|
('Eerie black', '#1B1B1B', 11, 11, 11, 0, 0, 11, 0, 11, 'CRAYOLA'),
|
|
('Eggplant', '#614051', 38, 25, 32, 329, 21, 32, 34, 38, 'CRAYOLA'),
|
|
('Eggshell', '#F0EAD6', 94, 92, 84, 46, 46, 89, 11, 94, 'ISCC_NBS'),
|
|
('Electric lime', '#CCFF00', 80, 100, 0, 72, 100, 50, 100, 100, 'CRAYOLA'),
|
|
('Electric purple', '#BF00FF', 75, 0, 100, 285, 100, 50, 100, 100, 'X11_WEB'),
|
|
('Electric violet', '#8F00FF', 56, 0, 100, 274, 100, 50, 100, 100, 'ISCC_NBS'),
|
|
('Emerald', '#50C878', 31, 78, 47, 140, 52, 55, 60, 78, 'MAERZ_AND_PAUL'),
|
|
('Eminence', '#6C3082', 42, 19, 51, 284, 46, 35, 63, 51, 'XONA_COM'),
|
|
('English lavender', '#B48395', 71, 51, 58, 338, 25, 61, 27, 71, 'PANTONE'),
|
|
('English red', '#AB4B52', 67, 29, 32, 356, 39, 48, 56, 67, 'ISCC_NBS'),
|
|
('English vermillion', '#CC474B', 80, 28, 29, 358, 57, 54, 65, 80, 'CRAYOLA'),
|
|
('English violet', '#563C5C', 34, 24, 36, 289, 21, 30, 35, 36, 'ISCC_NBS'),
|
|
('Erin', '#00FF40', 0, 100, 25, 135, 100, 50, 100, 100, 'MAERZ_AND_PAUL'),
|
|
('Eton blue', '#96C8A2', 59, 78, 64, 134, 31, 69, 25, 78, 'ETON_COLLEGE'),
|
|
('Fallow', '#C19A6B', 76, 60, 42, 33, 41, 59, 45, 76, 'ISCC_NBS'),
|
|
('Falu red', '#801818', 50, 9, 9, 0, 68, 30, 81, 50, 'COLORHEXA'),
|
|
('Fandango', '#B53389', 71, 20, 54, 320, 56, 46, 72, 71, 'MAERZ_AND_PAUL'),
|
|
('Fandango pink', '#DE5285', 87, 32, 52, 338, 68, 60, 63, 87, 'PANTONE'),
|
|
('Fawn', '#E5AA70', 90, 67, 44, 30, 69, 67, 51, 90, 'X11_WEB'),
|
|
('Fern green', '#4F7942', 31, 47, 26, 106, 29, 37, 45, 47, 'MAERZ_AND_PAUL'),
|
|
('Field drab', '#6C541E', 42, 33, 12, 42, 57, 27, 72, 42, 'ISCC_NBS'),
|
|
('Fiery rose', '#FF5470', 100, 33, 44, 350, 100, 67, 67, 100, 'CRAYOLA'),
|
|
('Finn', '#683068', 41, 19, 41, 300, 37, 30, 54, 41, 'HEXCOLOR_CO'),
|
|
('Firebrick', '#B22222', 70, 13, 13, 0, 68, 42, 81, 70, 'X11_WEB'),
|
|
('Fire engine red', '#CE2029', 81, 13, 16, 357, 73, 47, 84, 81, 'FINDTHEDATA_COM'),
|
|
('Flame', '#E25822', 89, 35, 13, 17, 77, 51, 85, 89, 'ISCC_NBS'),
|
|
('Flax', '#EEDC82', 93, 86, 51, 50, 76, 72, 45, 93, 'MAERZ_AND_PAUL'),
|
|
('Flirt', '#A2006D', 64, 0, 43, 320, 100, 32, 100, 64, 'XONA_COM'),
|
|
('Floral white', '#FFFAF0', 100, 98, 94, 40, 100, 97, 6, 100, 'X11_WEB'),
|
|
('Forest green (web)', '#228B22', 13, 55, 13, 120, 61, 34, 76, 55, 'X11_WEB'),
|
|
('French beige', '#A67B5B', 65, 48, 36, 26, 30, 50, 45, 65, 'ISCC_NBS'),
|
|
('French bistre', '#856D4D', 52, 43, 30, 34, 27, 41, 42, 52, 'POURPRE_COM'),
|
|
('French blue', '#0072BB', 0, 45, 73, 203, 100, 37, 100, 73, 'MAERZ_AND_PAUL'),
|
|
('French fuchsia', '#FD3F92', 99, 25, 57, 334, 98, 62, 75, 99, 'POURPRE_COM'),
|
|
('French lilac', '#86608E', 53, 38, 56, 290, 19, 47, 32, 56, 'ISCC_NBS'),
|
|
('French lime', '#9EFD38', 62, 99, 22, 89, 98, 61, 78, 99, 'POURPRE_COM'),
|
|
('French mauve', '#D473D4', 83, 45, 83, 300, 53, 64, 46, 83, 'POURPRE_COM'),
|
|
('French pink', '#FD6C9E', 99, 42, 62, 339, 97, 71, 57, 99, 'POURPRE_COM'),
|
|
('French raspberry', '#C72C48', 78, 17, 28, 349, 64, 48, 78, 78, 'POURPRE_COM'),
|
|
('French sky blue', '#77B5FE', 47, 71, 100, 212, 99, 73, 53, 100, 'POURPRE_COM'),
|
|
('French violet', '#8806CE', 53, 2, 81, 279, 94, 42, 97, 81, 'POURPRE_COM'),
|
|
('Frostbite', '#E936A7', 91, 21, 65, 322, 80, 56, 77, 91, 'CRAYOLA'),
|
|
('Fuchsia', '#FF00FF', 100, 0, 100, 300, 100, 50, 100, 100, 'X11_WEB'),
|
|
('Fuchsia (Crayola)', '#C154C1', 76, 33, 76, 300, 47, 54, 56, 76, 'CRAYOLA'),
|
|
('Fulvous', '#E48400', 89, 52, 0, 35, 100, 45, 100, 89, '99COLORS_NET'),
|
|
('Fuzzy Wuzzy', '#87421F', 53, 26, 12, 20, 63, 33, 77, 53, 'CRAYOLA');
|
|
`.trim(),
|
|
},
|
|
{
|
|
id: 12,
|
|
type: 'quickstart',
|
|
title: 'Slack Clone',
|
|
description: 'Build a basic slack clone with Row Level Security.',
|
|
sql: `
|
|
--
|
|
-- For use with https://github.com/supabase/supabase/tree/master/examples/slack-clone/nextjs-slack-clone
|
|
|
|
-- Custom types
|
|
create type public.app_permission as enum ('channels.delete', 'messages.delete');
|
|
create type public.app_role as enum ('admin', 'moderator');
|
|
create type public.user_status as enum ('ONLINE', 'OFFLINE');
|
|
|
|
-- USERS
|
|
create table public.users (
|
|
id uuid not null primary key, -- UUID from auth.users
|
|
username text,
|
|
status user_status default 'OFFLINE'::public.user_status
|
|
);
|
|
comment on table public.users is 'Profile data for each user.';
|
|
comment on column public.users.id is 'References the internal Supabase Auth user.';
|
|
|
|
-- CHANNELS
|
|
create table public.channels (
|
|
id bigint generated by default as identity primary key,
|
|
inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
|
|
slug text not null unique,
|
|
created_by uuid references public.users not null
|
|
);
|
|
comment on table public.channels is 'Topics and groups.';
|
|
|
|
-- MESSAGES
|
|
create table public.messages (
|
|
id bigint generated by default as identity primary key,
|
|
inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
|
|
message text,
|
|
user_id uuid references public.users not null,
|
|
channel_id bigint references public.channels on delete cascade not null
|
|
);
|
|
comment on table public.messages is 'Individual messages sent by each user.';
|
|
|
|
-- USER ROLES
|
|
create table public.user_roles (
|
|
id bigint generated by default as identity primary key,
|
|
user_id uuid references public.users on delete cascade not null,
|
|
role app_role not null,
|
|
unique (user_id, role)
|
|
);
|
|
comment on table public.user_roles is 'Application roles for each user.';
|
|
|
|
-- ROLE PERMISSIONS
|
|
create table public.role_permissions (
|
|
id bigint generated by default as identity primary key,
|
|
role app_role not null,
|
|
permission app_permission not null,
|
|
unique (role, permission)
|
|
);
|
|
comment on table public.role_permissions is 'Application permissions for each role.';
|
|
|
|
-- authorize with role-based access control (RBAC)
|
|
create function public.authorize(
|
|
requested_permission app_permission,
|
|
user_id uuid
|
|
)
|
|
returns boolean as
|
|
$$
|
|
declare
|
|
bind_permissions int;
|
|
begin
|
|
select
|
|
count(*)
|
|
from public.role_permissions
|
|
inner join public.user_roles on role_permissions.role = user_roles.role
|
|
where
|
|
role_permissions.permission = authorize.requested_permission and
|
|
user_roles.user_id = authorize.user_id
|
|
into bind_permissions;
|
|
|
|
return bind_permissions > 0;
|
|
end;
|
|
$$
|
|
language plpgsql security definer;
|
|
|
|
-- Secure the tables
|
|
alter table public.users
|
|
enable row level security;
|
|
alter table public.channels
|
|
enable row level security;
|
|
alter table public.messages
|
|
enable row level security;
|
|
alter table public.user_roles
|
|
enable row level security;
|
|
alter table public.role_permissions
|
|
enable row level security;
|
|
|
|
create policy "Allow logged-in read access" on public.users
|
|
for select using (auth.role() = 'authenticated');
|
|
create policy "Allow individual insert access" on public.users
|
|
for insert with check ((select auth.uid()) = id);
|
|
create policy "Allow individual update access" on public.users
|
|
for update using ( (select auth.uid()) = id );
|
|
create policy "Allow logged-in read access" on public.channels
|
|
for select using (auth.role() = 'authenticated');
|
|
create policy "Allow individual insert access" on public.channels
|
|
for insert with check ((select auth.uid()) = created_by);
|
|
create policy "Allow individual delete access" on public.channels
|
|
for delete using ((select auth.uid()) = created_by);
|
|
create policy "Allow authorized delete access" on public.channels
|
|
for delete using (authorize('channels.delete', auth.uid()));
|
|
create policy "Allow logged-in read access" on public.messages
|
|
for select using (auth.role() = 'authenticated');
|
|
create policy "Allow individual insert access" on public.messages
|
|
for insert with check ((select auth.uid()) = user_id);
|
|
create policy "Allow individual update access" on public.messages
|
|
for update using ((select auth.uid()) = user_id);
|
|
create policy "Allow individual delete access" on public.messages
|
|
for delete using ((select auth.uid()) = user_id);
|
|
create policy "Allow authorized delete access" on public.messages
|
|
for delete using (authorize('messages.delete', auth.uid()));
|
|
create policy "Allow individual read access" on public.user_roles
|
|
for select using ((select auth.uid()) = user_id);
|
|
|
|
-- Send "previous data" on change
|
|
alter table public.users
|
|
replica identity full;
|
|
alter table public.channels
|
|
replica identity full;
|
|
alter table public.messages
|
|
replica identity full;
|
|
|
|
-- inserts a row into public.users and assigns roles
|
|
create function public.handle_new_user()
|
|
returns trigger
|
|
set search_path = ''
|
|
as $$
|
|
declare is_admin boolean;
|
|
begin
|
|
insert into public.users (id, username)
|
|
values (new.id, new.email);
|
|
|
|
select count(*) = 1 from auth.users into is_admin;
|
|
|
|
if position('+supaadmin@' in new.email) > 0 then
|
|
insert into public.user_roles (user_id, role) values (new.id, 'admin');
|
|
elsif position('+supamod@' in new.email) > 0 then
|
|
insert into public.user_roles (user_id, role) values (new.id, 'moderator');
|
|
end if;
|
|
|
|
return new;
|
|
end;
|
|
$$ language plpgsql security definer;
|
|
|
|
-- trigger the function every time a user is created
|
|
create trigger on_auth_user_created
|
|
after insert on auth.users
|
|
for each row execute procedure public.handle_new_user();
|
|
|
|
/**
|
|
* REALTIME SUBSCRIPTIONS
|
|
* Only allow realtime listening on public tables.
|
|
*/
|
|
|
|
begin;
|
|
-- remove the realtime publication
|
|
drop publication if exists supabase_realtime;
|
|
|
|
-- re-create the publication but don't enable it for any tables
|
|
create publication supabase_realtime;
|
|
commit;
|
|
|
|
-- add tables to the publication
|
|
alter publication supabase_realtime add table public.channels;
|
|
alter publication supabase_realtime add table public.messages;
|
|
alter publication supabase_realtime add table public.users;
|
|
|
|
-- DUMMY DATA
|
|
insert into public.users (id, username)
|
|
values
|
|
('8d0fd2b3-9ca7-4d9e-a95f-9e13dded323e', 'supabot');
|
|
|
|
insert into public.channels (slug, created_by)
|
|
values
|
|
('public', '8d0fd2b3-9ca7-4d9e-a95f-9e13dded323e'),
|
|
('random', '8d0fd2b3-9ca7-4d9e-a95f-9e13dded323e');
|
|
|
|
insert into public.messages (message, channel_id, user_id)
|
|
values
|
|
('Hello World 👋', 1, '8d0fd2b3-9ca7-4d9e-a95f-9e13dded323e'),
|
|
('Perfection is attained, not when there is nothing more to add, but when there is nothing left to take away.', 2, '8d0fd2b3-9ca7-4d9e-a95f-9e13dded323e');
|
|
|
|
insert into public.role_permissions (role, permission)
|
|
values
|
|
('admin', 'channels.delete'),
|
|
('admin', 'messages.delete'),
|
|
('moderator', 'messages.delete');
|
|
`.trim(),
|
|
},
|
|
{
|
|
id: 13,
|
|
type: 'quickstart',
|
|
title: 'Todo List',
|
|
description: 'Build a basic todo list with Row Level Security.',
|
|
sql: `
|
|
--
|
|
-- 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
|
|
);
|
|
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 ((select auth.uid()) = user_id);
|
|
create policy "Individuals can update their own todos." on todos for
|
|
update using ((select auth.uid()) = user_id);
|
|
create policy "Individuals can delete their own todos." on todos for
|
|
delete using ((select auth.uid()) = user_id);
|
|
`.trim(),
|
|
},
|
|
{
|
|
id: 14,
|
|
type: 'quickstart',
|
|
title: 'Stripe Subscriptions',
|
|
description: 'Starter template for the Next.js Stripe Subscriptions Starter.',
|
|
sql: `
|
|
/**
|
|
* 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 ((select auth.uid()) = id);
|
|
create policy "Can update own user data." on users
|
|
for update using ((select 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
|
|
set search_path = ''
|
|
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 Stripe 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 ((select 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;
|
|
`.trim(),
|
|
},
|
|
{
|
|
id: 15,
|
|
type: 'quickstart',
|
|
title: 'User Management Starter',
|
|
description: 'Sets up a public Profiles table which you can access with your API.',
|
|
sql: `
|
|
-- Create a table for public profiles
|
|
create table 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 ${DOCS_URL}/guides/auth/row-level-security for more details.
|
|
alter table profiles
|
|
enable row level security;
|
|
|
|
create policy "Public profiles are viewable by everyone." on profiles
|
|
for select using (true);
|
|
|
|
create policy "Users can insert their own profile." on profiles
|
|
for insert with check ((select auth.uid()) = id);
|
|
|
|
create policy "Users can update own profile." on 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 ${DOCS_URL}/guides/auth/managing-user-data#using-triggers for more details.
|
|
create function public.handle_new_user()
|
|
returns trigger
|
|
set search_path = ''
|
|
as $$
|
|
begin
|
|
insert into public.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
|
|
after insert on auth.users
|
|
for each row execute procedure public.handle_new_user();
|
|
|
|
-- Set up Storage!
|
|
insert into storage.buckets (id, name)
|
|
values ('avatars', 'avatars');
|
|
|
|
-- Set up access controls for storage. Allows downloading object with public key
|
|
-- See ${DOCS_URL}/guides/storage/security/access-control#policy-examples for more details.
|
|
create policy "Avatar images are publicly accessible." on storage.objects
|
|
for select using (bucket_id = 'avatars' and storage.allow_any_operation(array['object.get_authenticated_info', 'object.get_authenticated']));
|
|
|
|
create policy "Anyone can upload an avatar." on storage.objects
|
|
for insert with check (bucket_id = 'avatars');
|
|
`.trim(),
|
|
},
|
|
{
|
|
id: 16,
|
|
type: 'quickstart',
|
|
title: 'NextAuth Schema Setup',
|
|
description: 'Sets up a the Schema and Tables for the NextAuth Supabase Adapter.',
|
|
sql: `
|
|
--
|
|
-- Name: next_auth; Type: SCHEMA;
|
|
--
|
|
CREATE SCHEMA next_auth;
|
|
|
|
GRANT USAGE ON SCHEMA next_auth TO service_role;
|
|
GRANT ALL ON SCHEMA next_auth TO postgres;
|
|
|
|
--
|
|
-- Create users table
|
|
--
|
|
CREATE TABLE IF NOT EXISTS next_auth.users
|
|
(
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text,
|
|
email text,
|
|
"emailVerified" timestamp with time zone,
|
|
image text,
|
|
CONSTRAINT users_pkey PRIMARY KEY (id),
|
|
CONSTRAINT email_unique UNIQUE (email)
|
|
);
|
|
|
|
GRANT ALL ON TABLE next_auth.users TO postgres;
|
|
GRANT ALL ON TABLE next_auth.users TO service_role;
|
|
|
|
--- uid() function to be used in RLS policies
|
|
CREATE FUNCTION next_auth.uid() RETURNS uuid
|
|
LANGUAGE sql STABLE
|
|
AS $$
|
|
select
|
|
coalesce(
|
|
nullif(current_setting('request.jwt.claim.sub', true), ''),
|
|
(nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')
|
|
)::uuid
|
|
$$;
|
|
|
|
--
|
|
-- Create sessions table
|
|
--
|
|
CREATE TABLE IF NOT EXISTS next_auth.sessions
|
|
(
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
expires timestamp with time zone NOT NULL,
|
|
"sessionToken" text NOT NULL,
|
|
"userId" uuid,
|
|
CONSTRAINT sessions_pkey PRIMARY KEY (id),
|
|
CONSTRAINT sessionToken_unique UNIQUE ("sessionToken"),
|
|
CONSTRAINT "sessions_userId_fkey" FOREIGN KEY ("userId")
|
|
REFERENCES next_auth.users (id) MATCH SIMPLE
|
|
ON UPDATE NO ACTION
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
GRANT ALL ON TABLE next_auth.sessions TO postgres;
|
|
GRANT ALL ON TABLE next_auth.sessions TO service_role;
|
|
|
|
--
|
|
-- Create accounts table
|
|
--
|
|
CREATE TABLE IF NOT EXISTS next_auth.accounts
|
|
(
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
type text NOT NULL,
|
|
provider text NOT NULL,
|
|
"providerAccountId" text NOT NULL,
|
|
refresh_token text,
|
|
access_token text,
|
|
expires_at bigint,
|
|
token_type text,
|
|
scope text,
|
|
id_token text,
|
|
session_state text,
|
|
oauth_token_secret text,
|
|
oauth_token text,
|
|
"userId" uuid,
|
|
CONSTRAINT accounts_pkey PRIMARY KEY (id),
|
|
CONSTRAINT provider_unique UNIQUE (provider, "providerAccountId"),
|
|
CONSTRAINT "accounts_userId_fkey" FOREIGN KEY ("userId")
|
|
REFERENCES next_auth.users (id) MATCH SIMPLE
|
|
ON UPDATE NO ACTION
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
GRANT ALL ON TABLE next_auth.accounts TO postgres;
|
|
GRANT ALL ON TABLE next_auth.accounts TO service_role;
|
|
|
|
--
|
|
-- Create verification_tokens table
|
|
--
|
|
CREATE TABLE IF NOT EXISTS next_auth.verification_tokens
|
|
(
|
|
identifier text,
|
|
token text,
|
|
expires timestamp with time zone NOT NULL,
|
|
CONSTRAINT verification_tokens_pkey PRIMARY KEY (token),
|
|
CONSTRAINT token_unique UNIQUE (token),
|
|
CONSTRAINT token_identifier_unique UNIQUE (token, identifier)
|
|
);
|
|
|
|
GRANT ALL ON TABLE next_auth.verification_tokens TO postgres;
|
|
GRANT ALL ON TABLE next_auth.verification_tokens TO service_role;
|
|
`.trim(),
|
|
},
|
|
{
|
|
id: 17,
|
|
type: 'template',
|
|
title: 'Most frequently invoked',
|
|
description: 'Most frequently called queries in your database.',
|
|
sql: `-- Most frequently called queries
|
|
|
|
-- A limit of 100 has been added below
|
|
|
|
select
|
|
auth.rolname,
|
|
statements.query,
|
|
statements.calls,
|
|
-- -- Postgres 13, 14, 15
|
|
statements.total_exec_time + statements.total_plan_time as total_time,
|
|
statements.min_exec_time + statements.min_plan_time as min_time,
|
|
statements.max_exec_time + statements.max_plan_time as max_time,
|
|
statements.mean_exec_time + statements.mean_plan_time as mean_time,
|
|
-- -- Postgres <= 12
|
|
-- total_time,
|
|
-- min_time,
|
|
-- max_time,
|
|
-- mean_time,
|
|
statements.rows / statements.calls as avg_rows
|
|
|
|
from pg_stat_statements as statements
|
|
inner join pg_authid as auth on statements.userid = auth.oid
|
|
order by
|
|
statements.calls desc
|
|
limit
|
|
100;`,
|
|
},
|
|
{
|
|
id: 18,
|
|
type: 'template',
|
|
title: 'Most time consuming',
|
|
description: 'Aggregate time spent on a query type.',
|
|
sql: `-- Most time consuming queries
|
|
|
|
-- A limit of 100 has been added below
|
|
|
|
select
|
|
auth.rolname,
|
|
statements.query,
|
|
statements.calls,
|
|
statements.total_exec_time + statements.total_plan_time as total_time,
|
|
to_char(((statements.total_exec_time + statements.total_plan_time)/sum(statements.total_exec_time + statements.total_plan_time) over()) * 100, 'FM90D0') || '%' as prop_total_time
|
|
from pg_stat_statements as statements
|
|
inner join pg_authid as auth on statements.userid = auth.oid
|
|
order by
|
|
total_time desc
|
|
limit
|
|
100;`,
|
|
},
|
|
{
|
|
id: 19,
|
|
type: 'template',
|
|
title: 'Slowest execution time',
|
|
description: 'Slowest queries based on max execution time.',
|
|
sql: `-- Slowest queries by max execution time
|
|
|
|
-- A limit of 100 has been added below
|
|
|
|
select
|
|
auth.rolname,
|
|
statements.query,
|
|
statements.calls,
|
|
-- -- Postgres 13, 14, 15
|
|
statements.total_exec_time + statements.total_plan_time as total_time,
|
|
statements.min_exec_time + statements.min_plan_time as min_time,
|
|
statements.max_exec_time + statements.max_plan_time as max_time,
|
|
statements.mean_exec_time + statements.mean_plan_time as mean_time,
|
|
-- -- Postgres <= 12
|
|
-- total_time,
|
|
-- min_time,
|
|
-- max_time,
|
|
-- mean_time,
|
|
statements.rows / statements.calls as avg_rows
|
|
from pg_stat_statements as statements
|
|
inner join pg_authid as auth on statements.userid = auth.oid
|
|
order by
|
|
max_time desc
|
|
limit
|
|
100;`,
|
|
},
|
|
{
|
|
id: 20,
|
|
type: 'template',
|
|
title: 'Hit rate',
|
|
description: 'See your cache and index hit rate.',
|
|
sql: `-- Cache and index hit rate
|
|
|
|
select
|
|
'index hit rate' as name,
|
|
(sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) as ratio
|
|
from pg_statio_user_indexes
|
|
union all
|
|
select
|
|
'table hit rate' as name,
|
|
sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) as ratio
|
|
from pg_statio_user_tables;`,
|
|
},
|
|
{
|
|
id: 21,
|
|
type: 'quickstart',
|
|
title: 'OpenAI Vector Search',
|
|
description: 'Template for the Next.js OpenAI Doc Search Starter.',
|
|
sql: `
|
|
-- Enable pg_vector extension
|
|
create extension if not exists vector with schema public;
|
|
|
|
-- Create tables
|
|
create table "public"."nods_page" (
|
|
id bigserial primary key,
|
|
parent_page_id bigint references public.nods_page,
|
|
path text not null unique,
|
|
checksum text,
|
|
meta jsonb,
|
|
type text,
|
|
source text
|
|
);
|
|
alter table "public"."nods_page" enable row level security;
|
|
|
|
create table "public"."nods_page_section" (
|
|
id bigserial primary key,
|
|
page_id bigint not null references public.nods_page on delete cascade,
|
|
content text,
|
|
token_count int,
|
|
embedding vector(1536),
|
|
slug text,
|
|
heading text
|
|
);
|
|
alter table "public"."nods_page_section" enable row level security;
|
|
|
|
-- Create embedding similarity search functions
|
|
create or replace function match_page_sections(embedding vector(1536), match_threshold float, match_count int, min_content_length int)
|
|
returns table (id bigint, page_id bigint, slug text, heading text, content text, similarity float)
|
|
language plpgsql
|
|
as $$
|
|
#variable_conflict use_variable
|
|
begin
|
|
return query
|
|
select
|
|
nods_page_section.id,
|
|
nods_page_section.page_id,
|
|
nods_page_section.slug,
|
|
nods_page_section.heading,
|
|
nods_page_section.content,
|
|
(nods_page_section.embedding <#> embedding) * -1 as similarity
|
|
from nods_page_section
|
|
|
|
-- We only care about sections that have a useful amount of content
|
|
where length(nods_page_section.content) >= min_content_length
|
|
|
|
-- The dot product is negative because of a Postgres limitation, so we negate it
|
|
and (nods_page_section.embedding <#> embedding) * -1 > match_threshold
|
|
|
|
-- OpenAI embeddings are normalized to length 1, so
|
|
-- cosine similarity and dot product will produce the same results.
|
|
-- Using dot product which can be computed slightly faster.
|
|
--
|
|
-- For the different syntaxes, see https://github.com/pgvector/pgvector
|
|
order by nods_page_section.embedding <#> embedding
|
|
|
|
limit match_count;
|
|
end;
|
|
$$;
|
|
|
|
create or replace function get_page_parents(page_id bigint)
|
|
returns table (id bigint, parent_page_id bigint, path text, meta jsonb)
|
|
language sql
|
|
as $$
|
|
with recursive chain as (
|
|
select *
|
|
from nods_page
|
|
where id = page_id
|
|
|
|
union all
|
|
|
|
select child.*
|
|
from nods_page as child
|
|
join chain on chain.parent_page_id = child.id
|
|
)
|
|
select id, parent_page_id, path, meta
|
|
from chain;
|
|
$$;
|
|
`.trim(),
|
|
},
|
|
{
|
|
id: 22,
|
|
type: 'template',
|
|
title: 'Replication status report',
|
|
description: 'See the status of your replication slots and replication lag.',
|
|
sql: `-- Replication status report
|
|
|
|
SELECT
|
|
s.slot_name,
|
|
s.active,
|
|
COALESCE(r.state, 'N/A') as state,
|
|
COALESCE(r.client_addr, null) as replication_client_address,
|
|
GREATEST(0, ROUND((redo_lsn-restart_lsn)/1024/1024/1024, 2)) as replication_lag_gb
|
|
FROM pg_control_checkpoint(), pg_replication_slots s
|
|
LEFT JOIN pg_stat_replication r ON (r.pid = s.active_pid);
|
|
`,
|
|
},
|
|
{
|
|
id: 23,
|
|
type: 'quickstart',
|
|
title: 'LangChain',
|
|
description: 'LangChain is a popular framework for working with AI, Vectors, and embeddings.',
|
|
sql: `
|
|
-- Enable the pgvector extension to work with embedding vectors
|
|
create extension vector;
|
|
|
|
-- Create a table to store your documents
|
|
create table documents (
|
|
id bigserial primary key,
|
|
content text, -- corresponds to Document.pageContent
|
|
metadata jsonb, -- corresponds to Document.metadata
|
|
embedding vector(1536) -- 1536 works for OpenAI embeddings, change if needed
|
|
);
|
|
|
|
-- Create a function to search for documents
|
|
create function match_documents (
|
|
query_embedding vector(1536),
|
|
match_count int default null,
|
|
filter jsonb DEFAULT '{}'
|
|
) returns table (
|
|
id bigint,
|
|
content text,
|
|
metadata jsonb,
|
|
similarity float
|
|
)
|
|
language plpgsql
|
|
as $$
|
|
#variable_conflict use_column
|
|
begin
|
|
return query
|
|
select
|
|
id,
|
|
content,
|
|
metadata,
|
|
1 - (documents.embedding <=> query_embedding) as similarity
|
|
from documents
|
|
where metadata @> filter
|
|
order by documents.embedding <=> query_embedding
|
|
limit match_count;
|
|
end;
|
|
$$;
|
|
`.trim(),
|
|
},
|
|
{
|
|
id: 24,
|
|
type: 'template',
|
|
title: 'Install dbdev',
|
|
description:
|
|
'dbdev is a client for installing Trusted Language Extensions (TLE) into your database.',
|
|
sql: `
|
|
/*---------------------
|
|
---- install dbdev ----
|
|
-----------------------
|
|
Requires:
|
|
- pg_tle: https://github.com/aws/pg_tle
|
|
- pgsql-http: https://github.com/pramsey/pgsql-http
|
|
|
|
Warning:
|
|
Restoring a logical backup of a database with a TLE installed can fail.
|
|
For this reason, dbdev should only be used with databases with physical backups enabled.
|
|
*/
|
|
create extension if not exists http with schema extensions;
|
|
create extension if not exists pg_tle;
|
|
select pgtle.uninstall_extension_if_exists('supabase-dbdev');
|
|
drop extension if exists "supabase-dbdev";
|
|
select
|
|
pgtle.install_extension(
|
|
'supabase-dbdev',
|
|
resp.contents ->> 'version',
|
|
'PostgreSQL package manager',
|
|
resp.contents ->> 'sql'
|
|
)
|
|
from http(
|
|
(
|
|
'GET',
|
|
'https://api.database.dev/rest/v1/'
|
|
|| 'package_versions?select=sql,version'
|
|
|| '&package_name=eq.supabase-dbdev'
|
|
|| '&order=version.desc'
|
|
|| '&limit=1',
|
|
array[
|
|
('apiKey', 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6InhtdXB0cHBsZnZpaWZyYndtbXR2Iiwicm9sZSI6ImFub24iLCJpYXQiOjE2ODAxMDczNzIsImV4cCI6MTk5NTY4MzM3Mn0.z2CN0mvO2No8wSi46Gw59DFGCTJrzM0AQKsu_5k134s')::http_header
|
|
],
|
|
null,
|
|
null
|
|
)
|
|
) x,
|
|
lateral (
|
|
select
|
|
((row_to_json(x) -> 'content') #>> '{}')::json -> 0
|
|
) resp(contents);
|
|
create extension "supabase-dbdev";
|
|
select dbdev.install('supabase-dbdev');
|
|
drop extension if exists "supabase-dbdev";
|
|
create extension "supabase-dbdev";
|
|
`.trim(),
|
|
},
|
|
{
|
|
id: 25,
|
|
type: 'template',
|
|
title: 'Large objects',
|
|
description: 'List large objects (tables/indexes) in your database.',
|
|
sql: `SELECT
|
|
SCHEMA_NAME,
|
|
relname,
|
|
table_size
|
|
FROM
|
|
(SELECT
|
|
pg_catalog.pg_namespace.nspname AS SCHEMA_NAME,
|
|
relname,
|
|
pg_relation_size(pg_catalog.pg_class.oid) AS table_size
|
|
FROM pg_catalog.pg_class
|
|
JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
|
|
) t
|
|
WHERE SCHEMA_NAME NOT LIKE 'pg_%'
|
|
ORDER BY table_size DESC
|
|
LIMIT 25`.trim(),
|
|
},
|
|
{
|
|
id: 26,
|
|
type: 'template',
|
|
title: 'Limit MFA verification attempts to one in 2 seconds',
|
|
description:
|
|
'Create an Auth hook that limits the number of failed MFA verification attempts to one in 2 seconds.',
|
|
sql: `
|
|
create function public.hook_mfa_verification_attempt(event jsonb)
|
|
returns jsonb
|
|
language plpgsql
|
|
as $$
|
|
declare
|
|
last_failed_at timestamp;
|
|
begin
|
|
if event->'valid' is true then
|
|
-- code is valid, accept it
|
|
return jsonb_build_object('decision', 'continue');
|
|
end if;
|
|
|
|
select last_failed_at into last_failed_at
|
|
from public.mfa_failed_verification_attempts
|
|
where
|
|
user_id = (event->'user_id')::uuid
|
|
and
|
|
factor_id = event->'factor_id';
|
|
|
|
if last_failed_at is not null and now() - last_failed_at < interval '2 seconds' then
|
|
-- last attempt was done too quickly
|
|
return jsonb_build_object(
|
|
'error', jsonb_build_object(
|
|
'http_code', 429,
|
|
'message', 'Please wait a moment before trying again.'
|
|
)
|
|
);
|
|
end if;
|
|
|
|
-- record this failed attempt
|
|
insert into public.mfa_failed_verification_attempts
|
|
(
|
|
user_id,
|
|
factor_id,
|
|
last_refreshed_at
|
|
)
|
|
values
|
|
(
|
|
event->'user_id',
|
|
event->'factor_id',
|
|
now()
|
|
)
|
|
on conflict do update
|
|
set last_refreshed_at = now();
|
|
|
|
-- finally let Supabase Auth do the default behavior for a failed attempt
|
|
return jsonb_build_object('decision', 'continue');
|
|
end;
|
|
$$;
|
|
|
|
-- Assign appropriate permissions and revoke access
|
|
grant execute
|
|
on function public.hook_mfa_verification_attempt
|
|
to supabase_auth_admin;
|
|
|
|
grant all
|
|
on table public.mfa_failed_verification_attempts
|
|
to supabase_auth_admin;
|
|
|
|
revoke execute
|
|
on function public.hook_mfa_verification_attempt
|
|
from authenticated, anon, public;
|
|
|
|
revoke all
|
|
on table public.mfa_failed_verification_attempts
|
|
from authenticated, anon, public;
|
|
|
|
grant usage on schema public to supabase_auth_admin;`.trim(),
|
|
},
|
|
{
|
|
id: 27,
|
|
type: 'template',
|
|
title: 'Add Auth Hook (Password Verification Attempt)',
|
|
description:
|
|
'Create an Auth Hook that limits number of failed password verification attempts to one in 10 seconds',
|
|
sql: `
|
|
create function public.hook_password_verification_attempt(event jsonb)
|
|
returns jsonb
|
|
language plpgsql
|
|
as $$
|
|
declare
|
|
last_failed_at timestamp;
|
|
begin
|
|
if event->'valid' is true then
|
|
-- password is valid, accept it
|
|
return jsonb_build_object('decision', 'continue');
|
|
end if;
|
|
|
|
select last_failed_at into last_failed_at
|
|
from public.password_failed_verification_attempts
|
|
where
|
|
user_id = (event->'user_id')::uuid;
|
|
|
|
if last_failed_at is not null and now() - last_failed_at < interval '10 seconds' then
|
|
-- last attempt was done too quickly
|
|
return jsonb_build_object(
|
|
'error', jsonb_build_object(
|
|
'http_code', 429,
|
|
'message', 'Please wait a moment before trying again.'
|
|
)
|
|
);
|
|
end if;
|
|
|
|
-- record this failed attempt
|
|
insert into public.password_failed_verification_attempts
|
|
(
|
|
user_id,
|
|
last_failed_at
|
|
)
|
|
values
|
|
(
|
|
event->'user_id',
|
|
now()
|
|
)
|
|
on conflict do update
|
|
set last_failed_at = now();
|
|
|
|
-- finally let Supabase Auth do the default behavior for a failed attempt
|
|
return jsonb_build_object('decision', 'continue');
|
|
end;
|
|
$$;
|
|
|
|
-- Assign appropriate permissions
|
|
grant execute
|
|
on function public.hook_password_verification_attempt
|
|
to supabase_auth_admin;
|
|
|
|
grant all
|
|
on table public.password_failed_verification_attempts
|
|
to supabase_auth_admin;
|
|
|
|
revoke execute
|
|
on function public.hook_password_verification_attempt
|
|
from authenticated, anon, public;
|
|
|
|
revoke all
|
|
on table public.password_failed_verification_attempts
|
|
from authenticated, anon, public;
|
|
|
|
grant usage on schema public to supabase_auth_admin;`.trim(),
|
|
},
|
|
{
|
|
id: 28,
|
|
type: 'template',
|
|
title: 'Add Auth Hook (Custom Access Token)',
|
|
description: 'Create an Auth Hook to add custom claims to your Auth Token',
|
|
sql: `
|
|
-- Assumes that there is an is_admin flag on the profiles table.
|
|
create or replace function public.custom_access_token_hook(event jsonb)
|
|
returns jsonb
|
|
language plpgsql
|
|
as $$
|
|
declare
|
|
claims jsonb;
|
|
is_admin boolean;
|
|
begin
|
|
-- Check if the user is marked as admin in the profiles table
|
|
select is_admin into is_admin from profiles where user_id = (event->>'user_id')::uuid;
|
|
|
|
-- Proceed only if the user is an admin
|
|
if is_admin then
|
|
claims := event->'claims';
|
|
|
|
-- Check if 'user_metadata' exists in claims
|
|
if jsonb_typeof(claims->'user_metadata') is null then
|
|
-- If 'user_metadata' does not exist, create an empty object
|
|
claims := jsonb_set(claims, '{user_metadata}', '{}');
|
|
end if;
|
|
|
|
-- Set a claim of 'admin'
|
|
claims := jsonb_set(claims, '{user_metadata, admin}', 'true');
|
|
|
|
-- Update the 'claims' object in the original event
|
|
event := jsonb_set(event, '{claims}', claims);
|
|
end if;
|
|
|
|
-- Return the modified or original event
|
|
return event;
|
|
end;
|
|
$$;
|
|
|
|
grant execute
|
|
on function public.custom_access_token_hook
|
|
to supabase_auth_admin;
|
|
|
|
revoke execute
|
|
on function public.custom_access_token_hook
|
|
from authenticated, anon, public;
|
|
|
|
grant usage on schema public to supabase_auth_admin;`.trim(),
|
|
},
|
|
|
|
{
|
|
id: 29,
|
|
type: 'template',
|
|
title: 'Add Auth Hook (General)',
|
|
description: 'Create an Auth Hook',
|
|
sql: `
|
|
create or replace function public.custom_access_token_hook(event jsonb)
|
|
returns jsonb
|
|
language plpgsql
|
|
as $$
|
|
declare
|
|
-- Insert variables here
|
|
begin
|
|
-- Insert logic here
|
|
return event;
|
|
end;
|
|
$$;
|
|
-- Permissions for the hook
|
|
grant execute on function public.custom_access_token_hook to supabase_auth_admin;
|
|
revoke execute on function public.custom_access_token_hook from authenticated, anon, public;
|
|
`,
|
|
},
|
|
{
|
|
id: 30,
|
|
type: 'quickstart',
|
|
title: 'Instruments',
|
|
description:
|
|
'Create an instruments table with sample data and Row Level Security, as used in the framework quickstarts.',
|
|
sql: `
|
|
-- Create the table
|
|
create table instruments (
|
|
id bigint primary key generated always as identity,
|
|
name text not null
|
|
);
|
|
|
|
-- Insert sample data into the table
|
|
insert into instruments (name)
|
|
values
|
|
('violin'),
|
|
('viola'),
|
|
('cello');
|
|
|
|
-- Grant the privileges the role needs, which is read access
|
|
grant select on public.instruments to anon;
|
|
|
|
-- Enable row level security for the table
|
|
alter table instruments enable row level security;
|
|
|
|
-- Create a policy to allow the anon role to read from the instruments table
|
|
create policy "public can read instruments"
|
|
on public.instruments
|
|
for select to anon
|
|
using (true);
|
|
`.trim(),
|
|
},
|
|
]
|