konnosDashboard
SQLEditor.queries.ts1603 lines · main
1import type { SQLTemplate } from './SQLEditor.types'
2import { DOCS_URL } from '@/lib/constants'
3
4export const SQL_TEMPLATES: SQLTemplate[] = [
5 {
6 id: 1,
7 type: 'template',
8 title: 'Create table',
9 description: 'Basic table template. Change "table_name" to the name you prefer.',
10 sql: `create table table_name (
11 id bigint generated by default as identity primary key,
12 inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
13 updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
14 data jsonb,
15 name text
16);`,
17 },
18 {
19 id: 2,
20 type: 'template',
21 title: 'Add view',
22 description:
23 'Template to add a view. Make sure to change the table and column names to ones that already exist.',
24 sql: `CREATE VIEW countries_view AS
25SELECT id, continent
26FROM countries;`,
27 },
28 {
29 id: 3,
30 type: 'template',
31 title: 'Add column',
32 description: 'Template to add a column. Make sure to change the name and type.',
33 sql: `alter table table_name
34add column new_column_name data_type;`,
35 },
36 {
37 id: 4,
38 type: 'template',
39 title: 'Add comments',
40 description: 'Templates to add a comment to either a table or a column.',
41 sql: `comment on table table_name is 'Table description';
42comment on column table_name.column_name is 'Column description';`,
43 },
44 {
45 id: 5,
46 type: 'template',
47 title: 'Show extensions',
48 description: 'Get a list of extensions in your database and status.',
49 sql: `select
50 name, comment, default_version, installed_version
51from
52 pg_available_extensions
53order by
54 name asc;`,
55 },
56 {
57 id: 6,
58 type: 'template',
59 title: 'Show version',
60 description: 'Get your Postgres version.',
61 sql: `select * from
62 (select version()) as version,
63 (select current_setting('server_version_num')) as version_number;`,
64 },
65 {
66 id: 7,
67 type: 'template',
68 title: 'Show active connections',
69 description: 'Get the number of active and max connections.',
70 sql: `select * from
71(select count(pid) as active_connections FROM pg_stat_activity where state = 'active') active_connections,
72(select setting as max_connections from pg_settings where name = 'max_connections') max_connections;`,
73 },
74 {
75 id: 8,
76 type: 'template',
77 title: 'Automatically update timestamps',
78 description: 'Update a column timestamp on every update.',
79 sql: `
80create extension if not exists moddatetime schema extensions;
81
82-- assuming the table name is "todos", and a timestamp column "updated_at"
83-- this trigger will set the "updated_at" column to the current timestamp for every update
84create trigger
85 handle_updated_at before update
86on todos
87for each row execute
88 procedure moddatetime(updated_at);
89 `.trim(),
90 },
91 {
92 id: 9,
93 type: 'template',
94 title: 'Increment field value',
95 description: 'Update a field with incrementing value using a function.',
96 sql: `
97create function increment(row_id int)
98returns void as
99$$
100 update table_name
101 set field_name = field_name + 1
102 where id = row_id;
103$$
104language sql volatile;
105
106-- you can call the function from your browser with briven-js
107-- const { data, error } = await briven.rpc('increment', { row_id: 2 })
108 `.trim(),
109 },
110 {
111 id: 10,
112 type: 'template',
113 title: 'pg_stat_statements report',
114 description: 'Select from pg_stat_statements and view recent queries',
115 sql: `-- pg_stat_statements report
116
117-- A limit of 100 has been added below
118
119select
120 auth.rolname,
121 statements.query,
122 statements.calls,
123 -- -- Postgres 13, 14
124 statements.total_exec_time + statements.total_plan_time as total_time,
125 statements.min_exec_time + statements.min_plan_time as min_time,
126 statements.max_exec_time + statements.max_plan_time as max_time,
127 statements.mean_exec_time + statements.mean_plan_time as mean_time,
128 -- -- Postgres <= 12
129 -- total_time,
130 -- min_time,
131 -- max_time,
132 -- mean_time,
133 statements.rows / statements.calls as avg_rows,
134 statements.wal_bytes,
135 statements.wal_records
136 from pg_stat_statements as statements
137 inner join pg_authid as auth on statements.userid = auth.oid
138 order by
139 total_time desc
140 limit
141 100;`,
142 },
143 {
144 id: 11,
145 type: 'quickstart',
146 title: 'Colors',
147 description: 'Create a table with a list of colors and their hex values.',
148 sql: `-- Information from Wikipedia "List of Colors"
149CREATE TYPE public.color_source AS ENUM (
150 '99COLORS_NET',
151 'ART_PAINTS_YG07S',
152 'BYRNE',
153 'CRAYOLA',
154 'CMYK_COLOR_MODEL',
155 'COLORCODE_IS',
156 'COLORHEXA',
157 'COLORXS',
158 'CORNELL_UNIVERSITY',
159 'COLUMBIA_UNIVERSITY',
160 'DUKE_UNIVERSITY',
161 'ENCYCOLORPEDIA_COM',
162 'ETON_COLLEGE',
163 'FANTETTI_AND_PETRACCHI',
164 'FINDTHEDATA_COM',
165 'FERRARIO_1919',
166 'FEDERAL_STANDARD_595',
167 'FLAG_OF_INDIA',
168 'FLAG_OF_SOUTH_AFRICA',
169 'GLAZEBROOK_AND_BALDRY',
170 'GOOGLE',
171 'HEXCOLOR_CO',
172 'ISCC_NBS',
173 'KELLY_MOORE',
174 'MATTEL',
175 'MAERZ_AND_PAUL',
176 'MILK_PAINT',
177 'MUNSELL_COLOR_WHEEL',
178 'NATURAL_COLOR_SYSTEM',
179 'PANTONE',
180 'PLOCHERE',
181 'POURPRE_COM',
182 'RAL',
183 'RESENE',
184 'RGB_COLOR_MODEL',
185 'THOM_POOLE',
186 'UNIVERSITY_OF_ALABAMA',
187 'UNIVERSITY_OF_CALIFORNIA_DAVIS',
188 'UNIVERSITY_OF_CAMBRIDGE',
189 'UNIVERSITY_OF_NORTH_CAROLINA',
190 'UNIVERSITY_OF_TEXAS_AT_AUSTIN',
191 'X11_WEB',
192 'XONA_COM'
193);
194
195create table public.colors (
196 id bigint generated by default as identity primary key,
197 name text,
198 hex text not null,
199 red int2,
200 green int2,
201 blue int2,
202 hue int2,
203 sat_hsl int2,
204 light_hsl int2,
205 sat_hsv int2,
206 val_hsv int2,
207 source color_source
208);
209
210comment on table colors is 'Full list of colors (based on various sources)';
211comment on column colors.name is 'Name of the color';
212comment on column colors.hex is 'Hex tripliets of the color for HTML web colors';
213comment on column colors.red is 'Red in RGB (%)';
214comment on column colors.green is 'Green in RGB (%)';
215comment on column colors.blue is 'Blue in RGB (%)';
216comment on column colors.hue is 'Hue in HSL (°)';
217comment on column colors.sat_hsl is 'Saturation in HSL (%)';
218comment on column colors.light_hsl is 'Light in HSL (%)';
219comment on column colors.sat_hsv is 'Saturation in HSV (%)';
220comment on column colors.val_hsv is 'Value in HSV (%)';
221comment on column colors.source is 'Source of information on the color';
222
223insert into public.colors (name, hex, red, green, blue, hue, sat_hsl, light_hsl, sat_hsv, val_hsv, source) values
224 ('Absolute Zero', '#0048BA', 0, 28, 73, 217, 100, 37, 100, 73, 'CRAYOLA'),
225 ('Acid green', '#B0BF1A', 69, 75, 10, 65, 76, 43, 76, 75, 'ART_PAINTS_YG07S'),
226 ('Aero', '#7CB9E8', 49, 73, 91, 206, 70, 70, 47, 91, 'MAERZ_AND_PAUL'),
227 ('African violet', '#B284BE', 70, 52, 75, 288, 31, 63, 31.5, '75', 'PANTONE'),
228 ('Air superiority blue', '#72A0C1', 45, 63, 76, 205, 39, 60, 41, 76, 'FEDERAL_STANDARD_595'),
229 ('Alice blue', '#F0F8FF', 94, 97, 100, 208, 100, 97, 6, 100, 'X11_WEB'),
230 ('Alizarin', '#DB2D43', 86, 18, 26, 352, 71, 52, 79, 86, 'MAERZ_AND_PAUL'),
231 ('Alloy orange', '#C46210', 77, 38, 6, 27, 85, 42, 92, 77, 'CRAYOLA'),
232 ('Almond', '#EED9C4', 93, 85, 77, 30, 55, 85, 18, 93, 'CRAYOLA'),
233 ('Amaranth deep purple', '#9F2B68', 62, 17, 41, 328, 57, 40, 73, 62, 'MAERZ_AND_PAUL'),
234 ('Amaranth pink', '#F19CBB', 95, 61, 73, 338, 75, 78, 35, 95, 'MAERZ_AND_PAUL'),
235 ('Amaranth purple', '#AB274F', 67, 15, 31, 342, 63, 41, 77, 67, 'MAERZ_AND_PAUL'),
236 ('Amazon', '#3B7A57', 23, 48, 34, 147, 35, 36, 52, 48, 'XONA_COM'),
237 ('Amber', '#FFBF00', 100, 75, 0, 45, 100, 50, 100, 100, 'RGB_COLOR_MODEL'),
238 ('Amethyst', '#9966CC', 60, 40, 80, 270, 50, 60, 50, 80, 'X11_WEB'),
239 ('Android green', '#3DDC84', 24, 86, 53, 148, 69, 55, 72, 86, 'GOOGLE'),
240 ('Antique brass', '#C88A65', 78, 54, 40, 22, 47, 59, 49, 78, 'CRAYOLA'),
241 ('Antique bronze', '#665D1E', 40, 36, 12, 53, 55, 26, 71, 40, 'ISCC_NBS'),
242 ('Antique fuchsia', '#915C83', 57, 36, 51, 316, 22, 46, 37, 57, 'PLOCHERE'),
243 ('Antique ruby', '#841B2D', 52, 11, 18, 350, 66, 31, 80, 52, 'ISCC_NBS'),
244 ('Antique white', '#FAEBD7', 98, 92, 84, 34, 78, 91, 14, 98, 'X11_WEB'),
245 ('Apricot', '#FBCEB1', 98, 81, 69, 24, 90, 84, 29, 98, 'MAERZ_AND_PAUL'),
246 ('Aqua', '#00FFFF', 0, 100, 100, 180, 100, 50, 100, 100, 'X11_WEB'),
247 ('Aquamarine', '#7FFFD4', 50, 100, 83, 160, 100, 75, 50, 100, 'X11_WEB'),
248 ('Arctic lime', '#D0FF14', 82, 100, 8, 72, 100, 54, 92, 100, 'CRAYOLA'),
249 ('Artichoke green', '#4B6F44', 29, 44, 27, 110, 24, 35, 39, 44, 'PANTONE'),
250 ('Arylide yellow', '#E9D66B', 91, 84, 42, 51, 74, 67, 54, 91, 'COLORHEXA'),
251 ('Ash gray', '#B2BEB5', 70, 75, 71, 135, 9, 72, 6, 75, 'ISCC_NBS'),
252 ('Atomic tangerine', '#FF9966', 100, 60, 40, 20, 100, 70, 60, 100, 'CRAYOLA'),
253 ('Aureolin', '#FDEE00', 99, 93, 0, 56, 100, 50, 100, 99, 'X11_WEB'),
254 ('Azure', '#007FFF', 0, 50, 100, 210, 100, 50, 100, 100, 'RGB_COLOR_MODEL'),
255 ('Azure (X11/web color)', '#F0FFFF', 94, 100, 100, 180, 100, 97, 6, 100, 'X11_WEB'),
256 ('Baby blue', '#89CFF0', 54, 81, 94, 199, 77, 74, 43, 94, 'MAERZ_AND_PAUL'),
257 ('Baby blue eyes', '#A1CAF1', 63, 79, 95, 209, 74, 79, 33, 95, 'PLOCHERE'),
258 ('Baby pink', '#F4C2C2', 96, 76, 76, 0, 69, 86, 20, 96, 'ISCC_NBS'),
259 ('Baby powder', '#FEFEFA', 100, 100, 98, 60, 67, 99, 2, 100, 'CRAYOLA'),
260 ('Baker-Miller pink', '#FF91AF', 100, 57, 69, 344, 100, 78, 43, 100, 'BYRNE'),
261 ('Banana Mania', '#FAE7B5', 98, 91, 71, 43, 87, 85, 28, 98, 'CRAYOLA'),
262 ('Barbie Pink', '#DA1884', 85, 9, 52, 327, 80, 48, 89, 85, 'MATTEL'),
263 ('Barn red', '#7C0A02', 49, 4, 1, 4, 97, 25, 98, 49, 'MILK_PAINT'),
264 ('Battleship grey', '#848482', 52, 52, 51, 60, 1, 51, 2, 52, 'ISCC_NBS'),
265 ('Beau blue', '#BCD4E6', 74, 83, 90, 206, 46, 82, 18, 90, 'PLOCHERE'),
266 ('Beaver', '#9F8170', 62, 51, 44, 22, 20, 53, 30, 62, 'CRAYOLA'),
267 ('Beige', '#F5F5DC', 96, 96, 86, 60, 56, 91, 10, 96, 'X11_WEB'),
268 ('B''dazzled blue', '#2E5894', 18, 35, 58, 215, 53, 38, 69, 58, 'CRAYOLA'),
269 ('Big dip o''ruby', '#9C2542', 61, 15, 26, 345, 62, 38, 76, 61, 'CRAYOLA'),
270 ('Bisque', '#FFE4C4', 100, 89, 77, 33, 100, 88, 23, 100, 'X11_WEB'),
271 ('Bistre', '#3D2B1F', 24, 17, 12, 24, 33, 18, 49, 24, '99COLORS_NET'),
272 ('Bistre brown', '#967117', 59, 44, 9, 43, 73, 34, 85, 59, 'ISCC_NBS'),
273 ('Bitter lemon', '#CAE00D', 79, 88, 5, 66, 89, 47, 94, 88, 'XONA_COM'),
274 ('Black', '#000000', 0, 0, 0, 0, 0, 0, 0, 0, 'RGB_COLOR_MODEL'),
275 ('Black bean', '#3D0C02', 24, 5, 1, 10, 94, 12, 97, 24, 'XONA_COM'),
276 ('Black coral', '#54626F', 33, 38, 44, 209, 14, 38, 24, 44, 'CRAYOLA'),
277 ('Black olive', '#3B3C36', 23, 24, 21, 70, 5, 22, 10, 24, 'RAL'),
278 ('Black Shadows', '#BFAFB2', 75, 69, 70, 349, 11, 72, 8, 75, 'CRAYOLA'),
279 ('Blanched almond', '#FFEBCD', 100, 92, 80, 36, 100, 90, 20, 100, 'X11_WEB'),
280 ('Blast-off bronze', '#A57164', 65, 44, 39, 12, 27, 52, 39, 65, 'CRAYOLA'),
281 ('Bleu de France', '#318CE7', 19, 55, 91, 210, 79, 55, 79, 91, 'POURPRE_COM'),
282 ('Blizzard blue', '#ACE5EE', 67, 90, 93, 188, 66, 80, 28, 93, 'CRAYOLA'),
283 ('Blood red', '#660000', 40, 0, 0, 0, 100, 20, 100, 40, 'THOM_POOLE'),
284 ('Blue', '#0000FF', 0, 0, 100, 240, 100, 50, 100, 100, 'X11_WEB'),
285 ('Blue (Crayola)', '#1F75FE', 12, 46, 100, 217, 99, 56, 88, 100, 'CRAYOLA'),
286 ('Blue (Munsell)', '#0093AF', 0, 58, 69, 190, 100, 34, 100, 69, 'MUNSELL_COLOR_WHEEL'),
287 ('Blue (NCS)', '#0087BD', 0, 53, 74, 197, 100, 37, 100, 74, 'NATURAL_COLOR_SYSTEM'),
288 ('Blue (Pantone)', '#0018A8', 0, 9, 66, 231, 100, 33, 100, 66, 'PANTONE'),
289 ('Blue (pigment)', '#333399', 20, 20, 60, 240, 50, 40, 67, 60, 'CMYK_COLOR_MODEL'),
290 ('Blue bell', '#A2A2D0', 64, 64, 82, 240, 33, 73, 22, 82, 'CRAYOLA'),
291 ('Blue-gray (Crayola)', '#6699CC', 40, 60, 80, 210, 50, 60, 50, 80, 'CRAYOLA'),
292 ('Blue jeans', '#5DADEC', 36, 68, 93, 206, 79, 65, 61, 93, 'CRAYOLA'),
293 ('Blue sapphire', '#126180', 7, 38, 50, 197, 75, 29, 86, 50, 'PANTONE'),
294 ('Blue-violet', '#8A2BE2', 54, 17, 89, 271, 76, 53, 81, 89, 'X11_WEB'),
295 ('Blue yonder', '#5072A7', 31, 45, 65, 217, 35, 48, 52, 65, 'PANTONE'),
296 ('Bluetiful', '#3C69E7', 24, 41, 91, 224, 78, 57, 74, 91, 'CRAYOLA'),
297 ('Blush', '#DE5D83', 87, 36, 51, 342, 66, 62, 58, 87, 'CRAYOLA'),
298 ('Bole', '#79443B', 47, 27, 23, 9, 34, 35, 51, 47, 'ISCC_NBS'),
299 ('Bone', '#E3DAC9', 89, 85, 79, 39, 32, 84, 11, 89, 'KELLY_MOORE'),
300 ('Brick red', '#CB4154', 80, 25, 33, 352, 57, 53, 68, 80, 'CRAYOLA'),
301 ('Bright lilac', '#D891EF', 85, 57, 94, 285, 75, 75, 39, 94, 'CRAYOLA'),
302 ('Bright yellow (Crayola)', '#FFAA1D', 100, 67, 11, 37, 100, 56, 89, 100, 'CRAYOLA'),
303 ('British racing green', '#004225', 0, 26, 15, 154, 100, 13, 100, 26, 'COLORHEXA'),
304 ('Bronze', '#CD7F32', 80, 50, 20, 30, 61, 50, 76, 80, 'MAERZ_AND_PAUL'),
305 ('Brown', '#964B00', 59, 29, 0, 30, 100, 29, 100, 59, 'COLORXS'),
306 ('Brown sugar', '#AF6E4D', 69, 43, 30, 20, 39, 49, 56, 69, 'CRAYOLA'),
307 ('Bud green', '#7BB661', 48, 71, 38, 102, 37, 55, 47, 71, 'PANTONE'),
308 ('Buff', '#FFC680', 100, 78, 50, 33, 100, 75, 50, 100, 'MAERZ_AND_PAUL'),
309 ('Burgundy', '#800020', 50, 0, 13, 345, 100, 25, 100, 50, 'MAERZ_AND_PAUL'),
310 ('Burlywood', '#DEB887', 87, 72, 53, 34, 57, 70, 39, 87, 'X11_WEB'),
311 ('Burnished brown', '#A17A74', 63, 48, 45, 8, 19, 54, 28, 63, 'CRAYOLA'),
312 ('Burnt orange', '#CC5500', 80, 33, 0, 25, 100, 40, 100, 80, 'UNIVERSITY_OF_TEXAS_AT_AUSTIN'),
313 ('Burnt sienna', '#E97451', 91, 45, 32, 14, 78, 62, 65, 91, 'FERRARIO_1919'),
314 ('Burnt umber', '#8A3324', 54, 20, 14, 9, 59, 34, 74, 54, 'XONA_COM'),
315 ('Byzantine', '#BD33A4', 74, 20, 64, 311, 58, 47, 73, 74, 'MAERZ_AND_PAUL'),
316 ('Byzantium', '#702963', 44, 16, 39, 311, 46, 30, 63, 44, 'ISCC_NBS'),
317 ('Cadet blue', '#5F9EA0', 37, 62, 63, 182, 26, 50, 41, 63, 'X11_WEB'),
318 ('Cadet grey', '#91A3B0', 57, 64, 69, 205, 16, 63, 18, 69, 'ISCC_NBS'),
319 ('Cadmium green', '#006B3C', 0, 42, 24, 154, 100, 21, 100, 42, 'ISCC_NBS'),
320 ('Cadmium orange', '#ED872D', 93, 53, 18, 28, 84, 55, 81, 93, 'ISCC_NBS'),
321 ('Café au lait', '#A67B5B', 65, 48, 36, 26, 30, 50, 45, 65, 'ISCC_NBS'),
322 ('Café noir', '#4B3621', 29, 21, 13, 30, 39, 21, 56, 29, 'ISCC_NBS'),
323 ('Cambridge blue', '#A3C1AD', 64, 76, 68, 140, 20, 70, 16, 76, 'UNIVERSITY_OF_CAMBRIDGE'),
324 ('Camel', '#C19A6B', 76, 60, 42, 33, 41, 59, 45, 76, 'ISCC_NBS'),
325 ('Cameo pink', '#EFBBCC', 94, 73, 80, 340, 62, 84, 22, 94, 'ISCC_NBS'),
326 ('Canary', '#FFFF99', 100, 100, 60, 60, 100, 80, 40, 100, 'CRAYOLA'),
327 ('Canary yellow', '#FFEF00', 100, 94, 0, 56, 100, 50, 100, 100, 'CMYK_COLOR_MODEL'),
328 ('Candy pink', '#E4717A', 89, 44, 48, 355, 68, 67, 50, 89, 'ISCC_NBS'),
329 ('Cardinal', '#C41E3A', 77, 12, 23, 350, 74, 44, 85, 77, 'MAERZ_AND_PAUL'),
330 ('Caribbean green', '#00CC99', 0, 80, 60, 165, 100, 40, 100, 80, 'CRAYOLA'),
331 ('Carmine', '#960018', 59, 0, 9, 350, 100, 29, 100, 59, 'POURPRE_COM'),
332 ('Carmine (M&P)', '#D70040', 84, 0, 25, 342, 100, 42, 100, 84, 'MAERZ_AND_PAUL'),
333 ('Carnation pink', '#FFA6C9', 100, 65, 79, 336, 100, 83, 35, 100, 'CRAYOLA'),
334 ('Carnelian', '#B31B1B', 70, 11, 11, 0, 74, 40, 85, 70, 'CORNELL_UNIVERSITY'),
335 ('Carolina blue', '#56A0D3', 34, 63, 83, 204, 59, 58, 59, 83, 'UNIVERSITY_OF_NORTH_CAROLINA'),
336 ('Carrot orange', '#ED9121', 93, 57, 13, 33, 85, 53, 86, 93, 'MAERZ_AND_PAUL'),
337 ('Catawba', '#703642', 44, 21, 26, 348, 35, 33, 52, 44, 'MAERZ_AND_PAUL'),
338 ('Cedar Chest', '#C95A49', 79, 35, 29, 8, 54, 54, 64, 79, 'CRAYOLA'),
339 ('Celadon', '#ACE1AF', 67, 88, 69, 123, 47, 78, 24, 88, 'ENCYCOLORPEDIA_COM'),
340 ('Celeste', '#B2FFFF', 70, 100, 100, 180, 100, 85, 30, 100, 'FANTETTI_AND_PETRACCHI'),
341 ('Cerise', '#DE3163', 87, 19, 39, 343, 72, 53, 78, 87, 'MAERZ_AND_PAUL'),
342 ('Cerulean', '#007BA7', 0, 48, 65, 196, 100, 33, 100, 65, 'MAERZ_AND_PAUL'),
343 ('Cerulean blue', '#2A52BE', 16, 32, 75, 224, 64, 46, 78, 75, 'MAERZ_AND_PAUL'),
344 ('Cerulean frost', '#6D9BC3', 43, 61, 76, 208, 42, 60, 44, 76, 'CRAYOLA'),
345 ('Cerulean (Crayola)', '#1DACD6', 11, 67, 84, 194, 76, 48, 86, 84, 'CRAYOLA'),
346 ('Cerulean (RGB)', '#0040FF', 0, 25, 100, 225, 100, 50, 100, 100, null),
347 ('Champagne', '#F7E7CE', 97, 91, 81, 37, 72, 89, 17, 97, 'MAERZ_AND_PAUL'),
348 ('Champagne pink', '#F1DDCF', 95, 87, 81, 25, 55, 88, 14, 95, 'PANTONE'),
349 ('Charcoal', '#36454F', 21, 27, 31, 204, 19, 26, 32, 31, 'ISCC_NBS'),
350 ('Charm pink', '#E68FAC', 90, 56, 67, 340, 64, 73, 38, 90, 'PLOCHERE'),
351 ('Chartreuse (web)', '#80FF00', 50, 100, 0, 90, 100, 50, 100, 100, 'RGB_COLOR_MODEL'),
352 ('Cherry blossom pink', '#FFB7C5', 100, 72, 77, 348, 100, 86, 28, 100, 'MAERZ_AND_PAUL'),
353 ('Chestnut', '#954535', 58, 27, 21, 10, 48, 40, 64, 58, 'MAERZ_AND_PAUL'),
354 ('Chili red', '#E23D28', 89, 24, 16, 5, 76, 52, 183, 125, 'FLAG_OF_SOUTH_AFRICA'),
355 ('China pink', '#DE6FA1', 87, 44, 63, 333, 63, 65, 50, 87, 'PLOCHERE'),
356 ('Chinese red', '#AA381E', 67, 22, 12, 11, 70, 39, 82, 67, 'ISCC_NBS'),
357 ('Chinese violet', '#856088', 52, 38, 53, 296, 17, 46, 29, 53, 'PANTONE'),
358 ('Chinese yellow', '#FFB200', 100, 70, 0, 42, 100, 50, 100, 100, 'ISCC_NBS'),
359 ('Chocolate (traditional)', '#7B3F00', 48, 25, 0, 31, 100, 24, 100, 48, 'MAERZ_AND_PAUL'),
360 ('Chocolate (web)', '#D2691E', 82, 41, 12, 25, 75, 47, 86, 82, 'X11_WEB'),
361 ('Cinereous', '#98817B', 60, 51, 48, 12, 12, 54, 19, 60, 'MAERZ_AND_PAUL'),
362 ('Cinnabar', '#E34234', 89, 26, 20, 5, 76, 55, 77, 89, 'MAERZ_AND_PAUL'),
363 ('Cinnamon Satin', '#CD607E', 80, 38, 49, 343, 52, 59, 53, 80, 'CRAYOLA'),
364 ('Citrine', '#E4D00A', 89, 82, 4, 54, 92, 47, 96, 89, 'MAERZ_AND_PAUL'),
365 ('Citron', '#9FA91F', 62, 66, 12, 64, 69, 39, 82, 66, 'XONA_COM'),
366 ('Claret', '#7F1734', 50, 9, 20, 343, 69, 29, 82, 50, 'XONA_COM'),
367 ('Coffee', '#6F4E37', 44, 31, 22, 25, 34, 33, 50, 44, 'ISCC_NBS'),
368 ('Columbia Blue', '#B9D9EB', 73, 85, 92, 202, 56, 82, 21, 92, 'COLUMBIA_UNIVERSITY'),
369 ('Congo pink', '#F88379', 97, 51, 47, 5, 90, 72, 51, 97, 'ISCC_NBS'),
370 ('Cool grey', '#8C92AC', 55, 57, 67, 229, 16, 61, 19, 67, 'ISCC_NBS'),
371 ('Copper', '#B87333', 72, 45, 20, 29, 57, 46, 72, 72, 'MAERZ_AND_PAUL'),
372 ('Copper (Crayola)', '#DA8A67', 85, 54, 40, 18, 61, 63, 53, 85, 'CRAYOLA'),
373 ('Copper penny', '#AD6F69', 68, 44, 41, 5, 29, 55, 39, 68, 'CRAYOLA'),
374 ('Copper red', '#CB6D51', 80, 43, 32, 14, 54, 56, 60, 80, 'ISCC_NBS'),
375 ('Copper rose', '#996666', 60, 40, 40, 0, 20, 50, 33, 60, '99COLORS_NET'),
376 ('Coquelicot', '#FF3800', 100, 22, 0, 13, 100, 50, 100, 100, 'COLORHEXA'),
377 ('Coral', '#FF7F50', 100, 50, 31, 16, 100, 66, 69, 100, 'X11_WEB'),
378 ('Coral pink', '#F88379', 97, 51, 47, 5, 90, 72, 51, 97, 'ISCC_NBS'),
379 ('Cordovan', '#893F45', 54, 25, 27, 355, 37, 39, 54, 54, 'PANTONE'),
380 ('Corn', '#FBEC5D', 98, 93, 36, 54, 95, 68, 63, 98, 'MAERZ_AND_PAUL'),
381 ('Cornflower blue', '#6495ED', 39, 58, 93, 219, 79, 66, 58, 93, 'X11_WEB'),
382 ('Cornsilk', '#FFF8DC', 100, 97, 86, 48, 100, 93, 14, 100, 'X11_WEB'),
383 ('Cosmic cobalt', '#2E2D88', 18, 18, 53, 241, 50, 36, 67, 53, 'CRAYOLA'),
384 ('Cosmic latte', '#FFF8E7', 100, 97, 91, 43, 100, 95, 9, 100, 'GLAZEBROOK_AND_BALDRY'),
385 ('Coyote brown', '#81613C', 51, 38, 24, 32, 37, 37, 52, 51, 'COLORCODE_IS'),
386 ('Cotton candy', '#FFBCD9', 100, 74, 85, 334, 100, 87, 26, 100, 'CRAYOLA'),
387 ('Cream', '#FFFDD0', 100, 99, 82, 57, 100, 91, 18, 100, 'MAERZ_AND_PAUL'),
388 ('Crimson', '#DC143C', 86, 8, 24, 348, 83, 47, 91, 86, 'X11_WEB'),
389 ('Crimson (UA)', '#9E1B32', 62, 11, 20, 349, 71, 36, 83, 62, 'UNIVERSITY_OF_ALABAMA'),
390 ('Cultured Pearl', '#F5F5F5', 96, 96, 96, 0, 0, 96, 0, 96, 'CRAYOLA'),
391 ('Cyan', '#00FFFF', 0, 100, 100, 180, 100, 50, 100, 100, 'X11_WEB'),
392 ('Cyan (process)', '#00B7EB', 0, 72, 92, 193, 100, 46, 100, 92, 'CMYK_COLOR_MODEL'),
393 ('Cyber grape', '#58427C', 35, 26, 49, 263, 31, 37, 47, 49, 'CRAYOLA'),
394 ('Cyber yellow', '#FFD300', 100, 83, 0, 50, 100, 50, 100, 100, 'PANTONE'),
395 ('Cyclamen', '#F56FA1', 96, 44, 63, 338, 87, 70, 54, 96, 'CRAYOLA'),
396 ('Dandelion', '#FED85D', 100, 85, 36, 46, 99, 68, 63, 100, 'CRAYOLA'),
397 ('Dark brown', '#654321', 40, 26, 13, 30, 51, 26, 67, 40, 'X11_WEB'),
398 ('Dark byzantium', '#5D3954', 36, 22, 33, 315, 24, 29, 39, 36, 'ISCC_NBS'),
399 ('Dark cyan', '#008B8B', 0, 55, 55, 180, 100, 27, 100, 55, 'X11_WEB'),
400 ('Dark electric blue', '#536878', 33, 41, 47, 206, 18, 40, 31, 47, 'ISCC_NBS'),
401 ('Dark goldenrod', '#B8860B', 72, 53, 4, 43, 89, 38, 94, 72, 'X11_WEB'),
402 ('Dark green (X11)', '#006400', 0, 39, 0, 120, 100, 20, 100, 39, 'X11_WEB'),
403 ('Dark jungle green', '#1A2421', 10, 14, 13, 162, 16, 12, 28, 14, 'ISCC_NBS'),
404 ('Dark khaki', '#BDB76B', 74, 72, 42, 56, 38, 58, 43, 74, 'X11_WEB'),
405 ('Dark lava', '#483C32', 28, 24, 20, 27, 18, 24, 31, 28, 'ISCC_NBS'),
406 ('Dark liver (horses)', '#543D37', 33, 24, 22, 12, 21, 27, 35, 33, 'UNIVERSITY_OF_CALIFORNIA_DAVIS'),
407 ('Dark magenta', '#8B008B', 55, 0, 55, 300, 100, 27, 100, 55, 'X11_WEB'),
408 ('Dark olive green', '#556B2F', 33, 42, 18, 82, 39, 30, 56, 42, 'X11_WEB'),
409 ('Dark orange', '#FF8C00', 100, 55, 0, 33, 100, 50, 100, 100, 'X11_WEB'),
410 ('Dark orchid', '#9932CC', 60, 20, 80, 280, 61, 50, 75, 80, 'X11_WEB'),
411 ('Dark purple', '#301934', 19, 10, 20, 291, 35, 15, 51, 20, 'ISCC_NBS'),
412 ('Dark red', '#8B0000', 55, 0, 0, 0, 100, 27, 100, 55, 'X11_WEB'),
413 ('Dark salmon', '#E9967A', 91, 59, 48, 15, 72, 70, 48, 91, 'X11_WEB'),
414 ('Dark sea green', '#8FBC8F', 56, 74, 56, 120, 25, 65, 24, 74, 'X11_WEB'),
415 ('Dark sienna', '#3C1414', 24, 8, 8, 0, 50, 16, 67, 24, 'ISCC_NBS'),
416 ('Dark sky blue', '#8CBED6', 55, 75, 84, 199, 47, 69, 35, 84, 'PANTONE'),
417 ('Dark slate blue', '#483D8B', 28, 24, 55, 248, 39, 39, 56, 55, 'X11_WEB'),
418 ('Dark slate gray', '#2F4F4F', 18, 31, 31, 180, 25, 25, 41, 31, 'X11_WEB'),
419 ('Dark spring green', '#177245', 9, 45, 27, 150, 66, 27, 80, 45, 'X11_WEB'),
420 ('Dark turquoise', '#00CED1', 0, 81, 82, 181, 100, 41, 100, 82, 'X11_WEB'),
421 ('Dark violet', '#9400D3', 58, 0, 83, 282, 100, 41, 100, 83, 'X11_WEB'),
422 ('Davy''s grey', '#555555', 33, 33, 33, 0, 0, 33, 0, 33, 'ISCC_NBS'),
423 ('Deep cerise', '#DA3287', 85, 20, 53, 330, 69, 53, 77, 85, 'CRAYOLA'),
424 ('Deep champagne', '#FAD6A5', 98, 84, 65, 35, 90, 81, 34, 98, 'ISCC_NBS'),
425 ('Deep chestnut', '#B94E48', 73, 31, 28, 3, 45, 50, 61, 73, 'CRAYOLA'),
426 ('Deep jungle green', '#004B49', 0, 29, 29, 178, 100, 15, 100, 29, 'ISCC_NBS'),
427 ('Deep pink', '#FF1493', 100, 8, 58, 328, 100, 54, 92, 100, 'X11_WEB'),
428 ('Deep saffron', '#FF9933', 100, 60, 20, 30, 100, 60, 80, 100, 'FLAG_OF_INDIA'),
429 ('Deep sky blue', '#00BFFF', 0, 75, 100, 195, 100, 50, 100, 100, 'X11_WEB'),
430 ('Deep Space Sparkle', '#4A646C', 29, 39, 42, 194, 19, 36, 31, 42, 'CRAYOLA'),
431 ('Deep taupe', '#7E5E60', 49, 37, 38, 356, 15, 43, 25, 49, 'PANTONE'),
432 ('Denim', '#1560BD', 8, 38, 74, 213, 80, 41, 89, 74, 'CRAYOLA'),
433 ('Denim blue', '#2243B6', 13, 26, 71, 227, 69, 42, 81, 71, 'CRAYOLA'),
434 ('Desert', '#C19A6B', 76, 60, 42, 33, 41, 59, 45, 76, 'ISCC_NBS'),
435 ('Desert sand', '#EDC9AF', 93, 79, 69, 25, 63, 81, 26, 93, 'CRAYOLA'),
436 ('Dim gray', '#696969', 41, 41, 41, 0, 0, 41, 0, 41, 'X11_WEB'),
437 ('Dodger blue', '#1E90FF', 12, 56, 100, 210, 100, 56, 88, 100, 'X11_WEB'),
438 ('Drab dark brown', '#4A412A', 29, 25, 16, 43, 28, 23, 43, 29, 'PANTONE'),
439 ('Duke blue', '#00009C', 0, 0, 61, 240, 100, 31, 100, 61, 'DUKE_UNIVERSITY'),
440 ('Dutch white', '#EFDFBB', 94, 87, 73, 42, 62, 84, 22, 94, 'RESENE'),
441 ('Ebony', '#555D50', 33, 36, 31, 97, 8, 34, 14, 36, 'MAERZ_AND_PAUL'),
442 ('Ecru', '#C2B280', 76, 70, 50, 45, 35, 63, 34, 76, 'ISCC_NBS'),
443 ('Eerie black', '#1B1B1B', 11, 11, 11, 0, 0, 11, 0, 11, 'CRAYOLA'),
444 ('Eggplant', '#614051', 38, 25, 32, 329, 21, 32, 34, 38, 'CRAYOLA'),
445 ('Eggshell', '#F0EAD6', 94, 92, 84, 46, 46, 89, 11, 94, 'ISCC_NBS'),
446 ('Electric lime', '#CCFF00', 80, 100, 0, 72, 100, 50, 100, 100, 'CRAYOLA'),
447 ('Electric purple', '#BF00FF', 75, 0, 100, 285, 100, 50, 100, 100, 'X11_WEB'),
448 ('Electric violet', '#8F00FF', 56, 0, 100, 274, 100, 50, 100, 100, 'ISCC_NBS'),
449 ('Emerald', '#50C878', 31, 78, 47, 140, 52, 55, 60, 78, 'MAERZ_AND_PAUL'),
450 ('Eminence', '#6C3082', 42, 19, 51, 284, 46, 35, 63, 51, 'XONA_COM'),
451 ('English lavender', '#B48395', 71, 51, 58, 338, 25, 61, 27, 71, 'PANTONE'),
452 ('English red', '#AB4B52', 67, 29, 32, 356, 39, 48, 56, 67, 'ISCC_NBS'),
453 ('English vermillion', '#CC474B', 80, 28, 29, 358, 57, 54, 65, 80, 'CRAYOLA'),
454 ('English violet', '#563C5C', 34, 24, 36, 289, 21, 30, 35, 36, 'ISCC_NBS'),
455 ('Erin', '#00FF40', 0, 100, 25, 135, 100, 50, 100, 100, 'MAERZ_AND_PAUL'),
456 ('Eton blue', '#96C8A2', 59, 78, 64, 134, 31, 69, 25, 78, 'ETON_COLLEGE'),
457 ('Fallow', '#C19A6B', 76, 60, 42, 33, 41, 59, 45, 76, 'ISCC_NBS'),
458 ('Falu red', '#801818', 50, 9, 9, 0, 68, 30, 81, 50, 'COLORHEXA'),
459 ('Fandango', '#B53389', 71, 20, 54, 320, 56, 46, 72, 71, 'MAERZ_AND_PAUL'),
460 ('Fandango pink', '#DE5285', 87, 32, 52, 338, 68, 60, 63, 87, 'PANTONE'),
461 ('Fawn', '#E5AA70', 90, 67, 44, 30, 69, 67, 51, 90, 'X11_WEB'),
462 ('Fern green', '#4F7942', 31, 47, 26, 106, 29, 37, 45, 47, 'MAERZ_AND_PAUL'),
463 ('Field drab', '#6C541E', 42, 33, 12, 42, 57, 27, 72, 42, 'ISCC_NBS'),
464 ('Fiery rose', '#FF5470', 100, 33, 44, 350, 100, 67, 67, 100, 'CRAYOLA'),
465 ('Finn', '#683068', 41, 19, 41, 300, 37, 30, 54, 41, 'HEXCOLOR_CO'),
466 ('Firebrick', '#B22222', 70, 13, 13, 0, 68, 42, 81, 70, 'X11_WEB'),
467 ('Fire engine red', '#CE2029', 81, 13, 16, 357, 73, 47, 84, 81, 'FINDTHEDATA_COM'),
468 ('Flame', '#E25822', 89, 35, 13, 17, 77, 51, 85, 89, 'ISCC_NBS'),
469 ('Flax', '#EEDC82', 93, 86, 51, 50, 76, 72, 45, 93, 'MAERZ_AND_PAUL'),
470 ('Flirt', '#A2006D', 64, 0, 43, 320, 100, 32, 100, 64, 'XONA_COM'),
471 ('Floral white', '#FFFAF0', 100, 98, 94, 40, 100, 97, 6, 100, 'X11_WEB'),
472 ('Forest green (web)', '#228B22', 13, 55, 13, 120, 61, 34, 76, 55, 'X11_WEB'),
473 ('French beige', '#A67B5B', 65, 48, 36, 26, 30, 50, 45, 65, 'ISCC_NBS'),
474 ('French bistre', '#856D4D', 52, 43, 30, 34, 27, 41, 42, 52, 'POURPRE_COM'),
475 ('French blue', '#0072BB', 0, 45, 73, 203, 100, 37, 100, 73, 'MAERZ_AND_PAUL'),
476 ('French fuchsia', '#FD3F92', 99, 25, 57, 334, 98, 62, 75, 99, 'POURPRE_COM'),
477 ('French lilac', '#86608E', 53, 38, 56, 290, 19, 47, 32, 56, 'ISCC_NBS'),
478 ('French lime', '#9EFD38', 62, 99, 22, 89, 98, 61, 78, 99, 'POURPRE_COM'),
479 ('French mauve', '#D473D4', 83, 45, 83, 300, 53, 64, 46, 83, 'POURPRE_COM'),
480 ('French pink', '#FD6C9E', 99, 42, 62, 339, 97, 71, 57, 99, 'POURPRE_COM'),
481 ('French raspberry', '#C72C48', 78, 17, 28, 349, 64, 48, 78, 78, 'POURPRE_COM'),
482 ('French sky blue', '#77B5FE', 47, 71, 100, 212, 99, 73, 53, 100, 'POURPRE_COM'),
483 ('French violet', '#8806CE', 53, 2, 81, 279, 94, 42, 97, 81, 'POURPRE_COM'),
484 ('Frostbite', '#E936A7', 91, 21, 65, 322, 80, 56, 77, 91, 'CRAYOLA'),
485 ('Fuchsia', '#FF00FF', 100, 0, 100, 300, 100, 50, 100, 100, 'X11_WEB'),
486 ('Fuchsia (Crayola)', '#C154C1', 76, 33, 76, 300, 47, 54, 56, 76, 'CRAYOLA'),
487 ('Fulvous', '#E48400', 89, 52, 0, 35, 100, 45, 100, 89, '99COLORS_NET'),
488 ('Fuzzy Wuzzy', '#87421F', 53, 26, 12, 20, 63, 33, 77, 53, 'CRAYOLA');
489`.trim(),
490 },
491 {
492 id: 12,
493 type: 'quickstart',
494 title: 'Slack Clone',
495 description: 'Build a basic slack clone with Row Level Security.',
496 sql: `
497--
498-- For use with https://github.com/briven/briven/tree/master/examples/slack-clone/nextjs-slack-clone
499
500-- Custom types
501create type public.app_permission as enum ('channels.delete', 'messages.delete');
502create type public.app_role as enum ('admin', 'moderator');
503create type public.user_status as enum ('ONLINE', 'OFFLINE');
504
505-- USERS
506create table public.users (
507 id uuid not null primary key, -- UUID from auth.users
508 username text,
509 status user_status default 'OFFLINE'::public.user_status
510);
511comment on table public.users is 'Profile data for each user.';
512comment on column public.users.id is 'References the internal Briven Auth user.';
513
514-- CHANNELS
515create table public.channels (
516 id bigint generated by default as identity primary key,
517 inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
518 slug text not null unique,
519 created_by uuid references public.users not null
520);
521comment on table public.channels is 'Topics and groups.';
522
523-- MESSAGES
524create table public.messages (
525 id bigint generated by default as identity primary key,
526 inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
527 message text,
528 user_id uuid references public.users not null,
529 channel_id bigint references public.channels on delete cascade not null
530);
531comment on table public.messages is 'Individual messages sent by each user.';
532
533-- USER ROLES
534create table public.user_roles (
535 id bigint generated by default as identity primary key,
536 user_id uuid references public.users on delete cascade not null,
537 role app_role not null,
538 unique (user_id, role)
539);
540comment on table public.user_roles is 'Application roles for each user.';
541
542-- ROLE PERMISSIONS
543create table public.role_permissions (
544 id bigint generated by default as identity primary key,
545 role app_role not null,
546 permission app_permission not null,
547 unique (role, permission)
548);
549comment on table public.role_permissions is 'Application permissions for each role.';
550
551-- authorize with role-based access control (RBAC)
552create function public.authorize(
553 requested_permission app_permission,
554 user_id uuid
555)
556returns boolean as
557$$
558 declare
559 bind_permissions int;
560 begin
561 select
562 count(*)
563 from public.role_permissions
564 inner join public.user_roles on role_permissions.role = user_roles.role
565 where
566 role_permissions.permission = authorize.requested_permission and
567 user_roles.user_id = authorize.user_id
568 into bind_permissions;
569
570 return bind_permissions > 0;
571 end;
572$$
573language plpgsql security definer;
574
575-- Secure the tables
576alter table public.users
577 enable row level security;
578alter table public.channels
579 enable row level security;
580alter table public.messages
581 enable row level security;
582alter table public.user_roles
583 enable row level security;
584alter table public.role_permissions
585 enable row level security;
586
587create policy "Allow logged-in read access" on public.users
588 for select using (auth.role() = 'authenticated');
589create policy "Allow individual insert access" on public.users
590 for insert with check ((select auth.uid()) = id);
591create policy "Allow individual update access" on public.users
592 for update using ( (select auth.uid()) = id );
593create policy "Allow logged-in read access" on public.channels
594 for select using (auth.role() = 'authenticated');
595create policy "Allow individual insert access" on public.channels
596 for insert with check ((select auth.uid()) = created_by);
597create policy "Allow individual delete access" on public.channels
598 for delete using ((select auth.uid()) = created_by);
599create policy "Allow authorized delete access" on public.channels
600 for delete using (authorize('channels.delete', auth.uid()));
601create policy "Allow logged-in read access" on public.messages
602 for select using (auth.role() = 'authenticated');
603create policy "Allow individual insert access" on public.messages
604 for insert with check ((select auth.uid()) = user_id);
605create policy "Allow individual update access" on public.messages
606 for update using ((select auth.uid()) = user_id);
607create policy "Allow individual delete access" on public.messages
608 for delete using ((select auth.uid()) = user_id);
609create policy "Allow authorized delete access" on public.messages
610 for delete using (authorize('messages.delete', auth.uid()));
611create policy "Allow individual read access" on public.user_roles
612 for select using ((select auth.uid()) = user_id);
613
614-- Send "previous data" on change
615alter table public.users
616 replica identity full;
617alter table public.channels
618 replica identity full;
619alter table public.messages
620 replica identity full;
621
622-- inserts a row into public.users and assigns roles
623create function public.handle_new_user()
624returns trigger
625set search_path = ''
626as $$
627 declare is_admin boolean;
628 begin
629 insert into public.users (id, username)
630 values (new.id, new.email);
631
632 select count(*) = 1 from auth.users into is_admin;
633
634 if position('+supaadmin@' in new.email) > 0 then
635 insert into public.user_roles (user_id, role) values (new.id, 'admin');
636 elsif position('+supamod@' in new.email) > 0 then
637 insert into public.user_roles (user_id, role) values (new.id, 'moderator');
638 end if;
639
640 return new;
641 end;
642$$ language plpgsql security definer;
643
644-- trigger the function every time a user is created
645create trigger on_auth_user_created
646 after insert on auth.users
647 for each row execute procedure public.handle_new_user();
648
649/**
650 * REALTIME SUBSCRIPTIONS
651 * Only allow realtime listening on public tables.
652 */
653
654begin;
655 -- remove the realtime publication
656 drop publication if exists briven_realtime;
657
658 -- re-create the publication but don't enable it for any tables
659 create publication briven_realtime;
660commit;
661
662-- add tables to the publication
663alter publication briven_realtime add table public.channels;
664alter publication briven_realtime add table public.messages;
665alter publication briven_realtime add table public.users;
666
667-- DUMMY DATA
668insert into public.users (id, username)
669values
670 ('8d0fd2b3-9ca7-4d9e-a95f-9e13dded323e', 'supabot');
671
672insert into public.channels (slug, created_by)
673values
674 ('public', '8d0fd2b3-9ca7-4d9e-a95f-9e13dded323e'),
675 ('random', '8d0fd2b3-9ca7-4d9e-a95f-9e13dded323e');
676
677insert into public.messages (message, channel_id, user_id)
678values
679 ('Hello World 👋', 1, '8d0fd2b3-9ca7-4d9e-a95f-9e13dded323e'),
680 ('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');
681
682insert into public.role_permissions (role, permission)
683values
684 ('admin', 'channels.delete'),
685 ('admin', 'messages.delete'),
686 ('moderator', 'messages.delete');
687`.trim(),
688 },
689 {
690 id: 13,
691 type: 'quickstart',
692 title: 'Todo List',
693 description: 'Build a basic todo list with Row Level Security.',
694 sql: `
695--
696-- For use with:
697-- https://github.com/briven/briven/tree/master/examples/todo-list/sveltejs-todo-list or
698-- https://github.com/briven/examples-archive/tree/main/briven-js-v1/todo-list
699--
700
701create table todos (
702 id bigint generated by default as identity primary key,
703 user_id uuid references auth.users not null,
704 task text check (char_length(task) > 3),
705 is_complete boolean default false,
706 inserted_at timestamp with time zone default timezone('utc'::text, now()) not null
707);
708alter table todos enable row level security;
709create policy "Individuals can create todos." on todos for
710 insert with check (auth.uid() = user_id);
711create policy "Individuals can view their own todos. " on todos for
712 select using ((select auth.uid()) = user_id);
713create policy "Individuals can update their own todos." on todos for
714 update using ((select auth.uid()) = user_id);
715create policy "Individuals can delete their own todos." on todos for
716 delete using ((select auth.uid()) = user_id);
717`.trim(),
718 },
719 {
720 id: 14,
721 type: 'quickstart',
722 title: 'Stripe Subscriptions',
723 description: 'Starter template for the Next.js Stripe Subscriptions Starter.',
724 sql: `
725/**
726* USERS
727* Note: This table contains user data. Users should only be able to view and update their own data.
728*/
729create table users (
730 -- UUID from auth.users
731 id uuid references auth.users not null primary key,
732 full_name text,
733 avatar_url text,
734 -- The customer's billing address, stored in JSON format.
735 billing_address jsonb,
736 -- Stores your customer's payment instruments.
737 payment_method jsonb
738);
739alter table users
740 enable row level security;
741create policy "Can view own user data." on users
742 for select using ((select auth.uid()) = id);
743create policy "Can update own user data." on users
744 for update using ((select auth.uid()) = id);
745
746/**
747* This trigger automatically creates a user entry when a new user signs up via Briven Auth.
748*/
749create function public.handle_new_user()
750returns trigger
751set search_path = ''
752as $$
753 begin
754 insert into public.users (id, full_name, avatar_url)
755 values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');
756 return new;
757 end;
758$$
759language plpgsql security definer;
760
761create trigger on_auth_user_created
762 after insert on auth.users
763 for each row
764 execute procedure public.handle_new_user();
765
766/**
767* CUSTOMERS
768* Note: this is a private table that contains a mapping of user IDs to Stripe customer IDs.
769*/
770create table customers (
771 -- UUID from auth.users
772 id uuid references auth.users not null primary key,
773 -- The user's customer ID in Stripe. User must not be able to update this.
774 stripe_customer_id text
775);
776alter table customers enable row level security;
777-- No policies as this is a private table that the user must not have access to.
778
779/**
780* PRODUCTS
781* Note: products are created and managed in Stripe and synced to our DB via Stripe webhooks.
782*/
783create table products (
784 -- Product ID from Stripe, e.g. prod_1234.
785 id text primary key,
786 -- Whether the product is currently available for purchase.
787 active boolean,
788 -- 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.
789 name text,
790 -- 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.
791 description text,
792 -- A URL of the product image in Stripe, meant to be displayable to the customer.
793 image text,
794 -- Set of key-value pairs, used to store additional information about the object in a structured format.
795 metadata jsonb
796);
797alter table products
798 enable row level security;
799create policy "Allow public read-only access." on products
800 for select using (true);
801
802/**
803* PRICES
804* Note: prices are created and managed in Stripe and synced to our DB via Stripe webhooks.
805*/
806create type pricing_type as enum ('one_time', 'recurring');
807create type pricing_plan_interval as enum ('day', 'week', 'month', 'year');
808create table prices (
809 -- Price ID from Stripe, e.g. price_1234.
810 id text primary key,
811 -- The ID of the prduct that this price belongs to.
812 product_id text references products,
813 -- Whether the price can be used for new purchases.
814 active boolean,
815 -- A brief description of the price.
816 description text,
817 -- 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).
818 unit_amount bigint,
819 -- Three-letter ISO currency code, in lowercase.
820 currency text check (char_length(currency) = 3),
821 -- One of \`one_time\` or \`recurring\` depending on whether the price is for a one-time purchase or a recurring (subscription) purchase.
822 type pricing_type,
823 -- The frequency at which a subscription is billed. One of \`day\`, \`week\`, \`month\` or \`year\`.
824 interval pricing_plan_interval,
825 -- The number of intervals (specified in the \`interval\` attribute) between subscription billings. For example, \`interval=month\` and \`interval_count=3\` bills every 3 months.
826 interval_count integer,
827 -- 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).
828 trial_period_days integer,
829 -- Set of key-value pairs, used to store additional information about the object in a structured format.
830 metadata jsonb
831);
832alter table prices
833 enable row level security;
834create policy "Allow public read-only access." on prices
835 for select using (true);
836
837/**
838* SUBSCRIPTIONS
839* Note: subscriptions are created and managed in Stripe and synced to our DB via Stripe webhooks.
840*/
841create type subscription_status as enum ('trialing', 'active', 'canceled', 'incomplete', 'incomplete_expired', 'past_due', 'unpaid');
842create table subscriptions (
843 -- Subscription ID from Stripe, e.g. sub_1234.
844 id text primary key,
845 user_id uuid references auth.users not null,
846 -- The status of the subscription object, one of subscription_status type above.
847 status subscription_status,
848 -- Set of key-value pairs, used to store additional information about the object in a structured format.
849 metadata jsonb,
850 -- ID of the price that created this subscription.
851 price_id text references prices,
852 -- Quantity multiplied by the unit amount of the price creates the amount of the subscription. Can be used to charge multiple seats.
853 quantity integer,
854 -- If true the subscription has been canceled by the user and will be deleted at the end of the billing period.
855 cancel_at_period_end boolean,
856 -- Time at which the subscription was created.
857 created timestamp with time zone default timezone('utc'::text, now()) not null,
858 -- Start of the current period that the subscription has been invoiced for.
859 current_period_start timestamp with time zone default timezone('utc'::text, now()) not null,
860 -- End of the current period that the subscription has been invoiced for. At the end of this period, a new invoice will be created.
861 current_period_end timestamp with time zone default timezone('utc'::text, now()) not null,
862 -- If the subscription has ended, the timestamp of the date the subscription ended.
863 ended_at timestamp with time zone default timezone('utc'::text, now()),
864 -- A date in the future at which the subscription will automatically get canceled.
865 cancel_at timestamp with time zone default timezone('utc'::text, now()),
866 -- 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.
867 canceled_at timestamp with time zone default timezone('utc'::text, now()),
868 -- If the subscription has a trial, the beginning of that trial.
869 trial_start timestamp with time zone default timezone('utc'::text, now()),
870 -- If the subscription has a trial, the end of that trial.
871 trial_end timestamp with time zone default timezone('utc'::text, now())
872);
873alter table subscriptions
874 enable row level security;
875create policy "Can only view own subs data." on subscriptions
876 for select using ((select auth.uid()) = user_id);
877
878/**
879 * REALTIME SUBSCRIPTIONS
880 * Only allow realtime listening on public tables.
881 */
882drop publication if exists briven_realtime;
883create publication briven_realtime
884 for table products, prices;
885`.trim(),
886 },
887 {
888 id: 15,
889 type: 'quickstart',
890 title: 'User Management Starter',
891 description: 'Sets up a public Profiles table which you can access with your API.',
892 sql: `
893-- Create a table for public profiles
894create table profiles (
895 id uuid references auth.users on delete cascade not null primary key,
896 updated_at timestamp with time zone,
897 username text unique,
898 full_name text,
899 avatar_url text,
900 website text,
901
902 constraint username_length check (char_length(username) >= 3)
903);
904-- Set up Row Level Security (RLS)
905-- See ${DOCS_URL}/guides/auth/row-level-security for more details.
906alter table profiles
907 enable row level security;
908
909create policy "Public profiles are viewable by everyone." on profiles
910 for select using (true);
911
912create policy "Users can insert their own profile." on profiles
913 for insert with check ((select auth.uid()) = id);
914
915create policy "Users can update own profile." on profiles
916 for update using ((select auth.uid()) = id);
917
918-- This trigger automatically creates a profile entry when a new user signs up via Briven Auth.
919-- See ${DOCS_URL}/guides/auth/managing-user-data#using-triggers for more details.
920create function public.handle_new_user()
921returns trigger
922set search_path = ''
923as $$
924begin
925 insert into public.profiles (id, full_name, avatar_url)
926 values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');
927 return new;
928end;
929$$ language plpgsql security definer;
930create trigger on_auth_user_created
931 after insert on auth.users
932 for each row execute procedure public.handle_new_user();
933
934-- Set up Storage!
935insert into storage.buckets (id, name)
936 values ('avatars', 'avatars');
937
938-- Set up access controls for storage.
939-- See ${DOCS_URL}/guides/storage#policy-examples for more details.
940create policy "Avatar images are publicly accessible." on storage.objects
941 for select using (bucket_id = 'avatars');
942
943create policy "Anyone can upload an avatar." on storage.objects
944 for insert with check (bucket_id = 'avatars');
945`.trim(),
946 },
947 {
948 id: 16,
949 type: 'quickstart',
950 title: 'NextAuth Schema Setup',
951 description: 'Sets up a the Schema and Tables for the NextAuth Briven Adapter.',
952 sql: `
953--
954-- Name: next_auth; Type: SCHEMA;
955--
956CREATE SCHEMA next_auth;
957
958GRANT USAGE ON SCHEMA next_auth TO service_role;
959GRANT ALL ON SCHEMA next_auth TO postgres;
960
961--
962-- Create users table
963--
964CREATE TABLE IF NOT EXISTS next_auth.users
965(
966 id uuid NOT NULL DEFAULT gen_random_uuid(),
967 name text,
968 email text,
969 "emailVerified" timestamp with time zone,
970 image text,
971 CONSTRAINT users_pkey PRIMARY KEY (id),
972 CONSTRAINT email_unique UNIQUE (email)
973);
974
975GRANT ALL ON TABLE next_auth.users TO postgres;
976GRANT ALL ON TABLE next_auth.users TO service_role;
977
978--- uid() function to be used in RLS policies
979CREATE FUNCTION next_auth.uid() RETURNS uuid
980 LANGUAGE sql STABLE
981 AS $$
982 select
983 coalesce(
984 nullif(current_setting('request.jwt.claim.sub', true), ''),
985 (nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')
986 )::uuid
987$$;
988
989--
990-- Create sessions table
991--
992CREATE TABLE IF NOT EXISTS next_auth.sessions
993(
994 id uuid NOT NULL DEFAULT gen_random_uuid(),
995 expires timestamp with time zone NOT NULL,
996 "sessionToken" text NOT NULL,
997 "userId" uuid,
998 CONSTRAINT sessions_pkey PRIMARY KEY (id),
999 CONSTRAINT sessionToken_unique UNIQUE ("sessionToken"),
1000 CONSTRAINT "sessions_userId_fkey" FOREIGN KEY ("userId")
1001 REFERENCES next_auth.users (id) MATCH SIMPLE
1002 ON UPDATE NO ACTION
1003 ON DELETE CASCADE
1004);
1005
1006GRANT ALL ON TABLE next_auth.sessions TO postgres;
1007GRANT ALL ON TABLE next_auth.sessions TO service_role;
1008
1009--
1010-- Create accounts table
1011--
1012CREATE TABLE IF NOT EXISTS next_auth.accounts
1013(
1014 id uuid NOT NULL DEFAULT gen_random_uuid(),
1015 type text NOT NULL,
1016 provider text NOT NULL,
1017 "providerAccountId" text NOT NULL,
1018 refresh_token text,
1019 access_token text,
1020 expires_at bigint,
1021 token_type text,
1022 scope text,
1023 id_token text,
1024 session_state text,
1025 oauth_token_secret text,
1026 oauth_token text,
1027 "userId" uuid,
1028 CONSTRAINT accounts_pkey PRIMARY KEY (id),
1029 CONSTRAINT provider_unique UNIQUE (provider, "providerAccountId"),
1030 CONSTRAINT "accounts_userId_fkey" FOREIGN KEY ("userId")
1031 REFERENCES next_auth.users (id) MATCH SIMPLE
1032 ON UPDATE NO ACTION
1033 ON DELETE CASCADE
1034);
1035
1036GRANT ALL ON TABLE next_auth.accounts TO postgres;
1037GRANT ALL ON TABLE next_auth.accounts TO service_role;
1038
1039--
1040-- Create verification_tokens table
1041--
1042CREATE TABLE IF NOT EXISTS next_auth.verification_tokens
1043(
1044 identifier text,
1045 token text,
1046 expires timestamp with time zone NOT NULL,
1047 CONSTRAINT verification_tokens_pkey PRIMARY KEY (token),
1048 CONSTRAINT token_unique UNIQUE (token),
1049 CONSTRAINT token_identifier_unique UNIQUE (token, identifier)
1050);
1051
1052GRANT ALL ON TABLE next_auth.verification_tokens TO postgres;
1053GRANT ALL ON TABLE next_auth.verification_tokens TO service_role;
1054`.trim(),
1055 },
1056 {
1057 id: 17,
1058 type: 'template',
1059 title: 'Most frequently invoked',
1060 description: 'Most frequently called queries in your database.',
1061 sql: `-- Most frequently called queries
1062
1063-- A limit of 100 has been added below
1064
1065select
1066 auth.rolname,
1067 statements.query,
1068 statements.calls,
1069 -- -- Postgres 13, 14, 15
1070 statements.total_exec_time + statements.total_plan_time as total_time,
1071 statements.min_exec_time + statements.min_plan_time as min_time,
1072 statements.max_exec_time + statements.max_plan_time as max_time,
1073 statements.mean_exec_time + statements.mean_plan_time as mean_time,
1074 -- -- Postgres <= 12
1075 -- total_time,
1076 -- min_time,
1077 -- max_time,
1078 -- mean_time,
1079 statements.rows / statements.calls as avg_rows
1080
1081 from pg_stat_statements as statements
1082 inner join pg_authid as auth on statements.userid = auth.oid
1083 order by
1084 statements.calls desc
1085 limit
1086 100;`,
1087 },
1088 {
1089 id: 18,
1090 type: 'template',
1091 title: 'Most time consuming',
1092 description: 'Aggregate time spent on a query type.',
1093 sql: `-- Most time consuming queries
1094
1095-- A limit of 100 has been added below
1096
1097select
1098 auth.rolname,
1099 statements.query,
1100 statements.calls,
1101 statements.total_exec_time + statements.total_plan_time as total_time,
1102 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
1103 from pg_stat_statements as statements
1104 inner join pg_authid as auth on statements.userid = auth.oid
1105 order by
1106 total_time desc
1107 limit
1108 100;`,
1109 },
1110 {
1111 id: 19,
1112 type: 'template',
1113 title: 'Slowest execution time',
1114 description: 'Slowest queries based on max execution time.',
1115 sql: `-- Slowest queries by max execution time
1116
1117-- A limit of 100 has been added below
1118
1119select
1120 auth.rolname,
1121 statements.query,
1122 statements.calls,
1123 -- -- Postgres 13, 14, 15
1124 statements.total_exec_time + statements.total_plan_time as total_time,
1125 statements.min_exec_time + statements.min_plan_time as min_time,
1126 statements.max_exec_time + statements.max_plan_time as max_time,
1127 statements.mean_exec_time + statements.mean_plan_time as mean_time,
1128 -- -- Postgres <= 12
1129 -- total_time,
1130 -- min_time,
1131 -- max_time,
1132 -- mean_time,
1133 statements.rows / statements.calls as avg_rows
1134 from pg_stat_statements as statements
1135 inner join pg_authid as auth on statements.userid = auth.oid
1136 order by
1137 max_time desc
1138 limit
1139 100;`,
1140 },
1141 {
1142 id: 20,
1143 type: 'template',
1144 title: 'Hit rate',
1145 description: 'See your cache and index hit rate.',
1146 sql: `-- Cache and index hit rate
1147
1148select
1149 'index hit rate' as name,
1150 (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) as ratio
1151 from pg_statio_user_indexes
1152 union all
1153 select
1154 'table hit rate' as name,
1155 sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) as ratio
1156 from pg_statio_user_tables;`,
1157 },
1158 {
1159 id: 21,
1160 type: 'quickstart',
1161 title: 'OpenAI Vector Search',
1162 description: 'Template for the Next.js OpenAI Doc Search Starter.',
1163 sql: `
1164-- Enable pg_vector extension
1165create extension if not exists vector with schema public;
1166
1167-- Create tables
1168create table "public"."nods_page" (
1169 id bigserial primary key,
1170 parent_page_id bigint references public.nods_page,
1171 path text not null unique,
1172 checksum text,
1173 meta jsonb,
1174 type text,
1175 source text
1176);
1177alter table "public"."nods_page" enable row level security;
1178
1179create table "public"."nods_page_section" (
1180 id bigserial primary key,
1181 page_id bigint not null references public.nods_page on delete cascade,
1182 content text,
1183 token_count int,
1184 embedding vector(1536),
1185 slug text,
1186 heading text
1187);
1188alter table "public"."nods_page_section" enable row level security;
1189
1190-- Create embedding similarity search functions
1191create or replace function match_page_sections(embedding vector(1536), match_threshold float, match_count int, min_content_length int)
1192returns table (id bigint, page_id bigint, slug text, heading text, content text, similarity float)
1193language plpgsql
1194as $$
1195#variable_conflict use_variable
1196begin
1197 return query
1198 select
1199 nods_page_section.id,
1200 nods_page_section.page_id,
1201 nods_page_section.slug,
1202 nods_page_section.heading,
1203 nods_page_section.content,
1204 (nods_page_section.embedding <#> embedding) * -1 as similarity
1205 from nods_page_section
1206
1207 -- We only care about sections that have a useful amount of content
1208 where length(nods_page_section.content) >= min_content_length
1209
1210 -- The dot product is negative because of a Postgres limitation, so we negate it
1211 and (nods_page_section.embedding <#> embedding) * -1 > match_threshold
1212
1213 -- OpenAI embeddings are normalized to length 1, so
1214 -- cosine similarity and dot product will produce the same results.
1215 -- Using dot product which can be computed slightly faster.
1216 --
1217 -- For the different syntaxes, see https://github.com/pgvector/pgvector
1218 order by nods_page_section.embedding <#> embedding
1219
1220 limit match_count;
1221end;
1222$$;
1223
1224create or replace function get_page_parents(page_id bigint)
1225returns table (id bigint, parent_page_id bigint, path text, meta jsonb)
1226language sql
1227as $$
1228 with recursive chain as (
1229 select *
1230 from nods_page
1231 where id = page_id
1232
1233 union all
1234
1235 select child.*
1236 from nods_page as child
1237 join chain on chain.parent_page_id = child.id
1238 )
1239 select id, parent_page_id, path, meta
1240 from chain;
1241$$;
1242`.trim(),
1243 },
1244 {
1245 id: 22,
1246 type: 'template',
1247 title: 'Replication status report',
1248 description: 'See the status of your replication slots and replication lag.',
1249 sql: `-- Replication status report
1250
1251SELECT
1252 s.slot_name,
1253 s.active,
1254 COALESCE(r.state, 'N/A') as state,
1255 COALESCE(r.client_addr, null) as replication_client_address,
1256 GREATEST(0, ROUND((redo_lsn-restart_lsn)/1024/1024/1024, 2)) as replication_lag_gb
1257FROM pg_control_checkpoint(), pg_replication_slots s
1258LEFT JOIN pg_stat_replication r ON (r.pid = s.active_pid);
1259`,
1260 },
1261 {
1262 id: 23,
1263 type: 'quickstart',
1264 title: 'LangChain',
1265 description: 'LangChain is a popular framework for working with AI, Vectors, and embeddings.',
1266 sql: `
1267-- Enable the pgvector extension to work with embedding vectors
1268create extension vector;
1269
1270-- Create a table to store your documents
1271create table documents (
1272 id bigserial primary key,
1273 content text, -- corresponds to Document.pageContent
1274 metadata jsonb, -- corresponds to Document.metadata
1275 embedding vector(1536) -- 1536 works for OpenAI embeddings, change if needed
1276);
1277
1278-- Create a function to search for documents
1279create function match_documents (
1280 query_embedding vector(1536),
1281 match_count int default null,
1282 filter jsonb DEFAULT '{}'
1283) returns table (
1284 id bigint,
1285 content text,
1286 metadata jsonb,
1287 similarity float
1288)
1289language plpgsql
1290as $$
1291#variable_conflict use_column
1292begin
1293 return query
1294 select
1295 id,
1296 content,
1297 metadata,
1298 1 - (documents.embedding <=> query_embedding) as similarity
1299 from documents
1300 where metadata @> filter
1301 order by documents.embedding <=> query_embedding
1302 limit match_count;
1303end;
1304$$;
1305`.trim(),
1306 },
1307 {
1308 id: 24,
1309 type: 'template',
1310 title: 'Install dbdev',
1311 description:
1312 'dbdev is a client for installing Trusted Language Extensions (TLE) into your database.',
1313 sql: `
1314/*---------------------
1315---- install dbdev ----
1316-----------------------
1317Requires:
1318 - pg_tle: https://github.com/aws/pg_tle
1319 - pgsql-http: https://github.com/pramsey/pgsql-http
1320
1321Warning:
1322Restoring a logical backup of a database with a TLE installed can fail.
1323For this reason, dbdev should only be used with databases with physical backups enabled.
1324*/
1325create extension if not exists http with schema extensions;
1326create extension if not exists pg_tle;
1327select pgtle.uninstall_extension_if_exists('briven-dbdev');
1328drop extension if exists "briven-dbdev";
1329select
1330 pgtle.install_extension(
1331 'briven-dbdev',
1332 resp.contents ->> 'version',
1333 'PostgreSQL package manager',
1334 resp.contents ->> 'sql'
1335 )
1336from http(
1337 (
1338 'GET',
1339 'https://api.database.dev/rest/v1/'
1340 || 'package_versions?select=sql,version'
1341 || '&package_name=eq.briven-dbdev'
1342 || '&order=version.desc'
1343 || '&limit=1',
1344 array[
1345 ('apiKey', 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6InhtdXB0cHBsZnZpaWZyYndtbXR2Iiwicm9sZSI6ImFub24iLCJpYXQiOjE2ODAxMDczNzIsImV4cCI6MTk5NTY4MzM3Mn0.z2CN0mvO2No8wSi46Gw59DFGCTJrzM0AQKsu_5k134s')::http_header
1346 ],
1347 null,
1348 null
1349 )
1350) x,
1351lateral (
1352 select
1353 ((row_to_json(x) -> 'content') #>> '{}')::json -> 0
1354) resp(contents);
1355create extension "briven-dbdev";
1356select dbdev.install('briven-dbdev');
1357drop extension if exists "briven-dbdev";
1358create extension "briven-dbdev";
1359`.trim(),
1360 },
1361 {
1362 id: 25,
1363 type: 'template',
1364 title: 'Large objects',
1365 description: 'List large objects (tables/indexes) in your database.',
1366 sql: `SELECT
1367 SCHEMA_NAME,
1368 relname,
1369 table_size
1370 FROM
1371 (SELECT
1372 pg_catalog.pg_namespace.nspname AS SCHEMA_NAME,
1373 relname,
1374 pg_relation_size(pg_catalog.pg_class.oid) AS table_size
1375 FROM pg_catalog.pg_class
1376 JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
1377 ) t
1378 WHERE SCHEMA_NAME NOT LIKE 'pg_%'
1379 ORDER BY table_size DESC
1380 LIMIT 25`.trim(),
1381 },
1382 {
1383 id: 26,
1384 type: 'template',
1385 title: 'Limit MFA verification attempts to one in 2 seconds',
1386 description:
1387 'Create an Auth hook that limits the number of failed MFA verification attempts to one in 2 seconds.',
1388 sql: `
1389create function public.hook_mfa_verification_attempt(event jsonb)
1390 returns jsonb
1391 language plpgsql
1392as $$
1393 declare
1394 last_failed_at timestamp;
1395 begin
1396 if event->'valid' is true then
1397 -- code is valid, accept it
1398 return jsonb_build_object('decision', 'continue');
1399 end if;
1400
1401 select last_failed_at into last_failed_at
1402 from public.mfa_failed_verification_attempts
1403 where
1404 user_id = (event->'user_id')::uuid
1405 and
1406 factor_id = event->'factor_id';
1407
1408 if last_failed_at is not null and now() - last_failed_at < interval '2 seconds' then
1409 -- last attempt was done too quickly
1410 return jsonb_build_object(
1411 'error', jsonb_build_object(
1412 'http_code', 429,
1413 'message', 'Please wait a moment before trying again.'
1414 )
1415 );
1416 end if;
1417
1418 -- record this failed attempt
1419 insert into public.mfa_failed_verification_attempts
1420 (
1421 user_id,
1422 factor_id,
1423 last_refreshed_at
1424 )
1425 values
1426 (
1427 event->'user_id',
1428 event->'factor_id',
1429 now()
1430 )
1431 on conflict do update
1432 set last_refreshed_at = now();
1433
1434 -- finally let Briven Auth do the default behavior for a failed attempt
1435 return jsonb_build_object('decision', 'continue');
1436 end;
1437$$;
1438
1439-- Assign appropriate permissions and revoke access
1440grant execute
1441 on function public.hook_mfa_verification_attempt
1442 to briven_auth_admin;
1443
1444grant all
1445 on table public.mfa_failed_verification_attempts
1446 to briven_auth_admin;
1447
1448revoke execute
1449 on function public.hook_mfa_verification_attempt
1450 from authenticated, anon, public;
1451
1452revoke all
1453 on table public.mfa_failed_verification_attempts
1454 from authenticated, anon, public;
1455
1456grant usage on schema public to briven_auth_admin;`.trim(),
1457 },
1458 {
1459 id: 27,
1460 type: 'template',
1461 title: 'Add Auth Hook (Password Verification Attempt)',
1462 description:
1463 'Create an Auth Hook that limits number of failed password verification attempts to one in 10 seconds',
1464 sql: `
1465create function public.hook_password_verification_attempt(event jsonb)
1466returns jsonb
1467language plpgsql
1468as $$
1469 declare
1470 last_failed_at timestamp;
1471 begin
1472 if event->'valid' is true then
1473 -- password is valid, accept it
1474 return jsonb_build_object('decision', 'continue');
1475 end if;
1476
1477 select last_failed_at into last_failed_at
1478 from public.password_failed_verification_attempts
1479 where
1480 user_id = (event->'user_id')::uuid;
1481
1482 if last_failed_at is not null and now() - last_failed_at < interval '10 seconds' then
1483 -- last attempt was done too quickly
1484 return jsonb_build_object(
1485 'error', jsonb_build_object(
1486 'http_code', 429,
1487 'message', 'Please wait a moment before trying again.'
1488 )
1489 );
1490 end if;
1491
1492 -- record this failed attempt
1493 insert into public.password_failed_verification_attempts
1494 (
1495 user_id,
1496 last_failed_at
1497 )
1498 values
1499 (
1500 event->'user_id',
1501 now()
1502 )
1503 on conflict do update
1504 set last_failed_at = now();
1505
1506 -- finally let Briven Auth do the default behavior for a failed attempt
1507 return jsonb_build_object('decision', 'continue');
1508 end;
1509$$;
1510
1511-- Assign appropriate permissions
1512grant execute
1513 on function public.hook_password_verification_attempt
1514 to briven_auth_admin;
1515
1516grant all
1517 on table public.password_failed_verification_attempts
1518 to briven_auth_admin;
1519
1520revoke execute
1521 on function public.hook_password_verification_attempt
1522 from authenticated, anon, public;
1523
1524revoke all
1525 on table public.password_failed_verification_attempts
1526 from authenticated, anon, public;
1527
1528grant usage on schema public to briven_auth_admin;`.trim(),
1529 },
1530 {
1531 id: 28,
1532 type: 'template',
1533 title: 'Add Auth Hook (Custom Access Token)',
1534 description: 'Create an Auth Hook to add custom claims to your Auth Token',
1535 sql: `
1536-- Assumes that there is an is_admin flag on the profiles table.
1537create or replace function public.custom_access_token_hook(event jsonb)
1538returns jsonb
1539language plpgsql
1540as $$
1541 declare
1542 claims jsonb;
1543 is_admin boolean;
1544 begin
1545 -- Check if the user is marked as admin in the profiles table
1546 select is_admin into is_admin from profiles where user_id = (event->>'user_id')::uuid;
1547
1548 -- Proceed only if the user is an admin
1549 if is_admin then
1550 claims := event->'claims';
1551
1552 -- Check if 'user_metadata' exists in claims
1553 if jsonb_typeof(claims->'user_metadata') is null then
1554 -- If 'user_metadata' does not exist, create an empty object
1555 claims := jsonb_set(claims, '{user_metadata}', '{}');
1556 end if;
1557
1558 -- Set a claim of 'admin'
1559 claims := jsonb_set(claims, '{user_metadata, admin}', 'true');
1560
1561 -- Update the 'claims' object in the original event
1562 event := jsonb_set(event, '{claims}', claims);
1563 end if;
1564
1565 -- Return the modified or original event
1566 return event;
1567 end;
1568$$;
1569
1570grant execute
1571 on function public.custom_access_token_hook
1572 to briven_auth_admin;
1573
1574revoke execute
1575 on function public.custom_access_token_hook
1576 from authenticated, anon, public;
1577
1578grant usage on schema public to briven_auth_admin;`.trim(),
1579 },
1580
1581 {
1582 id: 29,
1583 type: 'template',
1584 title: 'Add Auth Hook (General)',
1585 description: 'Create an Auth Hook',
1586 sql: `
1587create or replace function public.custom_access_token_hook(event jsonb)
1588returns jsonb
1589language plpgsql
1590as $$
1591declare
1592 -- Insert variables here
1593begin
1594 -- Insert logic here
1595 return event;
1596end;
1597$$;
1598-- Permissions for the hook
1599grant execute on function public.custom_access_token_hook to briven_auth_admin;
1600revoke execute on function public.custom_access_token_hook from authenticated, anon, public;
1601 `,
1602 },
1603]