mirror of
https://github.com/supabase/supabase.git
synced 2026-05-19 03:17:08 +08:00
08e9cdde5e
## I have read the [CONTRIBUTING.md](https://github.com/supabase/supabase/blob/master/CONTRIBUTING.md) file. YES ## What kind of change does this PR introduce? Replaces "stored procedures" with "functions" for everything related to the Data API. ## Additional context It's not accurate to call database functions "stored procedures". It may have been that way before Postgres 11, but now it causes confusion because PostgREST allows functions and not stored procedures. <!-- This is an auto-generated comment: release notes by coderabbit.ai --> ## Summary by CodeRabbit * **Documentation** * Standardized terminology across docs, SDK guides, CLI/config specs, examples, UI, and config comments to use "database functions" instead of "stored procedures". * Updated API docs, CLI/config descriptions, Studio UI labels, help text, empty-state and navigation copy, RPC documentation, and example text for consistency. * Adjusted explanatory text and error/help messages to reflect the revised terminology. <!-- end of auto-generated comment: release notes by coderabbit.ai -->
1604 lines
62 KiB
TypeScript
1604 lines
62 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.
|
|
-- See ${DOCS_URL}/guides/storage#policy-examples for more details.
|
|
create policy "Avatar images are publicly accessible." on storage.objects
|
|
for select using (bucket_id = 'avatars');
|
|
|
|
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;
|
|
`,
|
|
},
|
|
]
|