konnosDashboard
fallback-tools.ts581 lines · main
1import { getEntityDefinitionsSql } from '@supabase/pg-meta'
2import { tool } from 'ai'
3// import { processSql, renderBrivenJs } from '@supabase/sql-to-rest'
4import { IS_PLATFORM } from 'common'
5import { stripIndent } from 'common-tags'
6import { z } from 'zod'
7
8import { getDatabaseFunctions } from '@/data/database-functions/database-functions-query'
9import { getDatabasePolicies } from '@/data/database-policies/database-policies-query'
10import { executeSql } from '@/data/sql/execute-sql-query'
11import { executeQuery } from '@/lib/api/self-hosted/query'
12
13export const getFallbackTools = ({
14 projectRef,
15 connectionString,
16 cookie,
17 authorization,
18 includeSchemaMetadata,
19}: {
20 projectRef: string
21 connectionString: string
22 cookie?: string
23 authorization?: string
24 includeSchemaMetadata: boolean
25}) => {
26 const headers = {
27 'Content-Type': 'application/json',
28 ...(cookie && { cookie }),
29 ...(authorization && { Authorization: authorization }),
30 }
31
32 return {
33 getSchemaTables: tool({
34 description: 'Get more information about one or more schemas',
35 inputSchema: z.object({
36 schemas: z.array(z.string()).describe('The schema names to get the definitions for'),
37 }),
38 execute: async ({ schemas }) => {
39 try {
40 const { result } = includeSchemaMetadata
41 ? await executeSql(
42 {
43 projectRef,
44 connectionString,
45 sql: getEntityDefinitionsSql({ schemas }),
46 },
47 undefined,
48 headers,
49 IS_PLATFORM ? undefined : executeQuery
50 )
51 : { result: [] }
52
53 return result
54 } catch (error) {
55 console.error('Failed to execute SQL:', error)
56 return `Failed to fetch schema: ${error}`
57 }
58 },
59 }),
60 // [Ivan] The tool relies on `libpg-query` binaries which we've removed intentionally because they couldn't build on MacOS.
61 // Once we figure out a way how to use wasm binaries, we can add it back.
62 //
63 // convertSqlToBrivenJs: tool({
64 // description: 'Convert an sql query into briven-js client code',
65 // parameters: z.object({
66 // sql: z
67 // .string()
68 // .describe(
69 // 'The sql statement to convert. Only a subset of statements are supported currently. '
70 // ),
71 // }),
72 // execute: async ({ sql }) => {
73 // try {
74 // const statement = await processSql(sql)
75 // const { code } = await renderBrivenJs(statement)
76 // return code
77 // } catch (error) {
78 // return `Failed to convert SQL: ${error}`
79 // }
80 // },
81 // }),
82 getRlsKnowledge: tool({
83 description:
84 'Get existing policies and examples and instructions on how to write RLS policies',
85 inputSchema: z.object({
86 schemas: z.array(z.string()).describe('The schema names to get the policies for'),
87 }),
88 execute: async ({ schemas }) => {
89 const data = includeSchemaMetadata
90 ? await getDatabasePolicies(
91 {
92 projectRef,
93 connectionString,
94 schema: schemas?.join(','),
95 },
96 undefined,
97 headers
98 )
99 : []
100
101 const formattedPolicies = data
102 .map(
103 (policy) => `
104 Policy Name: "${policy.name}"
105 Action: ${policy.action}
106 Roles: ${policy.roles.join(', ')}
107 Command: ${policy.command}
108 Definition: ${policy.definition}
109 ${policy.check ? `Check: ${policy.check}` : ''}
110 `
111 )
112 .join('\n')
113
114 return stripIndent`
115 You're a Briven Postgres expert in writing row level security policies. Your purpose is to
116 generate a policy with the constraints given by the user. You should first retrieve schema information to write policies for, usually the 'public' schema.
117
118 The output should use the following instructions:
119 - The generated SQL must be valid SQL.
120 - You can use only CREATE POLICY or ALTER POLICY queries, no other queries are allowed.
121 - Always use double apostrophe in SQL strings (eg. 'Night''s watch')
122 - You can add short explanations to your messages.
123 - The result should be a valid markdown. The SQL code should be wrapped in \`\`\` (including sql language tag).
124 - Always use "auth.uid()" instead of "current_user".
125 - SELECT policies should always have USING but not WITH CHECK
126 - INSERT policies should always have WITH CHECK but not USING
127 - UPDATE policies should always have WITH CHECK and most often have USING
128 - DELETE policies should always have USING but not WITH CHECK
129 - Don't use \`FOR ALL\`. Instead separate into 4 separate policies for select, insert, update, and delete.
130 - The policy name should be short but detailed text explaining the policy, enclosed in double quotes.
131 - Always put explanations as separate text. Never use inline SQL comments.
132 - If the user asks for something that's not related to SQL policies, explain to the user
133 that you can only help with policies.
134 - Discourage \`RESTRICTIVE\` policies and encourage \`PERMISSIVE\` policies, and explain why.
135
136 The output should look like this:
137 \`\`\`sql
138 CREATE POLICY "My descriptive policy." ON books FOR INSERT to authenticated USING ( (select auth.uid()) = author_id ) WITH ( true );
139 \`\`\`
140
141 Since you are running in a Briven environment, take note of these Briven-specific additions:
142
143 ## Authenticated and unauthenticated roles
144
145 Briven maps every request to one of the roles:
146
147 - \`anon\`: an unauthenticated request (the user is not logged in)
148 - \`authenticated\`: an authenticated request (the user is logged in)
149
150 These are actually [Postgres Roles](/docs/guides/database/postgres/roles). You can use these roles within your Policies using the \`TO\` clause:
151
152 \`\`\`sql
153 create policy "Profiles are viewable by everyone"
154 on profiles
155 for select
156 to authenticated, anon
157 using ( true );
158
159 -- OR
160
161 create policy "Public profiles are viewable only by authenticated users"
162 on profiles
163 for select
164 to authenticated
165 using ( true );
166 \`\`\`
167
168 Note that \`for ...\` must be added after the table but before the roles. \`to ...\` must be added after \`for ...\`:
169
170 ### Incorrect
171 \`\`\`sql
172 create policy "Public profiles are viewable only by authenticated users"
173 on profiles
174 to authenticated
175 for select
176 using ( true );
177 \`\`\`
178
179 ### Correct
180 \`\`\`sql
181 create policy "Public profiles are viewable only by authenticated users"
182 on profiles
183 for select
184 to authenticated
185 using ( true );
186 \`\`\`
187
188 ## Multiple operations
189 PostgreSQL policies do not support specifying multiple operations in a single FOR clause. You need to create separate policies for each operation.
190
191 ### Incorrect
192 \`\`\`sql
193 create policy "Profiles can be created and deleted by any user"
194 on profiles
195 for insert, delete -- cannot create a policy on multiple operators
196 to authenticated
197 with check ( true )
198 using ( true );
199 \`\`\`
200
201 ### Correct
202 \`\`\`sql
203 create policy "Profiles can be created by any user"
204 on profiles
205 for insert
206 to authenticated
207 with check ( true );
208
209 create policy "Profiles can be deleted by any user"
210 on profiles
211 for delete
212 to authenticated
213 using ( true );
214 \`\`\`
215
216 ## Helper functions
217
218 Briven provides some helper functions that make it easier to write Policies.
219
220 ### \`auth.uid()\`
221
222 Returns the ID of the user making the request.
223
224 ### \`auth.jwt()\`
225
226 Returns the JWT of the user making the request. Anything that you store in the user's \`raw_app_meta_data\` column or the \`raw_user_meta_data\` column will be accessible using this function. It's important to know the distinction between these two:
227
228 - \`raw_user_meta_data\` - can be updated by the authenticated user using the \`briven.auth.update()\` function. It is not a good place to store authorization data.
229 - \`raw_app_meta_data\` - cannot be updated by the user, so it's a good place to store authorization data.
230
231 The \`auth.jwt()\` function is extremely versatile. For example, if you store some team data inside \`app_metadata\`, you can use it to determine whether a particular user belongs to a team. For example, if this was an array of IDs:
232
233 \`\`\`sql
234 create policy "User is in team"
235 on my_table
236 to authenticated
237 using ( team_id in (select auth.jwt() -> 'app_metadata' -> 'teams'));
238 \`\`\`
239
240 ### MFA
241
242 The \`auth.jwt()\` function can be used to check for [Multi-Factor Authentication](/docs/guides/auth/auth-mfa#enforce-rules-for-mfa-logins). For example, you could restrict a user from updating their profile unless they have at least 2 levels of authentication (Assurance Level 2):
243
244 \`\`\`sql
245 create policy "Restrict updates."
246 on profiles
247 as restrictive
248 for update
249 to authenticated using (
250 (select auth.jwt()->>'aal') = 'aal2'
251 );
252 \`\`\`
253
254 ## RLS performance recommendations
255
256 Every authorization system has an impact on performance. While row level security is powerful, the performance impact is important to keep in mind. This is especially true for queries that scan every row in a table - like many \`select\` operations, including those using limit, offset, and ordering.
257
258 Based on a series of [tests](https://github.com/GaryAustin1/RLS-Performance), we have a few recommendations for RLS:
259
260 ### Add indexes
261
262 Make sure you've added [indexes](/docs/guides/database/postgres/indexes) on any columns used within the Policies which are not already indexed (or primary keys). For a Policy like this:
263
264 \`\`\`sql
265 create policy "Users can access their own records" on test_table
266 to authenticated
267 using ( (select auth.uid()) = user_id );
268 \`\`\`
269
270 You can add an index like:
271
272 \`\`\`sql
273 create index userid
274 on test_table
275 using btree (user_id);
276 \`\`\`
277
278 ### Call functions with \`select\`
279
280 You can use \`select\` statement to improve policies that use functions. For example, instead of this:
281
282 \`\`\`sql
283 create policy "Users can access their own records" on test_table
284 to authenticated
285 using ( auth.uid() = user_id );
286 \`\`\`
287
288 You can do:
289
290 \`\`\`sql
291 create policy "Users can access their own records" on test_table
292 to authenticated
293 using ( (select auth.uid()) = user_id );
294 \`\`\`
295
296 This method works well for JWT functions like \`auth.uid()\` and \`auth.jwt()\` as well as \`security definer\` Functions. Wrapping the function causes an \`initPlan\` to be run by the Postgres optimizer, which allows it to "cache" the results per-statement, rather than calling the function on each row.
297
298 Caution: You can only use this technique if the results of the query or function do not change based on the row data.
299
300 ### Minimize joins
301
302 You can often rewrite your Policies to avoid joins between the source and the target table. Instead, try to organize your policy to fetch all the relevant data from the target table into an array or set, then you can use an \`IN\` or \`ANY\` operation in your filter.
303
304 For example, this is an example of a slow policy which joins the source \`test_table\` to the target \`team_user\`:
305
306 \`\`\`sql
307 create policy "Users can access records belonging to their teams" on test_table
308 to authenticated
309 using (
310 (select auth.uid()) in (
311 select user_id
312 from team_user
313 where team_user.team_id = team_id -- joins to the source "test_table.team_id"
314 )
315 );
316 \`\`\`
317
318 We can rewrite this to avoid this join, and instead select the filter criteria into a set:
319
320 \`\`\`sql
321 create policy "Users can access records belonging to their teams" on test_table
322 to authenticated
323 using (
324 team_id in (
325 select team_id
326 from team_user
327 where user_id = (select auth.uid()) -- no join
328 )
329 );
330 \`\`\`
331
332 ### Specify roles in your policies
333
334 Always use the Role of inside your policies, specified by the \`TO\` operator. For example, instead of this query:
335
336 \`\`\`sql
337 create policy "Users can access their own records" on rls_test
338 using ( auth.uid() = user_id );
339 \`\`\`
340
341 Use:
342
343 \`\`\`sql
344 create policy "Users can access their own records" on rls_test
345 to authenticated
346 using ( (select auth.uid()) = user_id );
347 \`\`\`
348
349 This prevents the policy \`( (select auth.uid()) = user_id )\` from running for any \`anon\` users, since the execution stops at the \`to authenticated\` step.
350
351 ${data.length > 0 ? `Here are my existing policies: ${formattedPolicies}` : ''}
352 `
353 },
354 }),
355 getFunctions: tool({
356 description: 'Get database functions for one or more schemas',
357 inputSchema: z.object({
358 schemas: z.array(z.string()).describe('The schema names to get the functions for'),
359 }),
360 execute: async ({ schemas }) => {
361 try {
362 const data = includeSchemaMetadata
363 ? await getDatabaseFunctions(
364 {
365 projectRef,
366 connectionString,
367 },
368 undefined,
369 headers
370 )
371 : []
372
373 const dataArray = Array.isArray(data) ? data : []
374
375 // Filter functions by requested schemas
376 const filteredFunctions = dataArray.filter((func) => schemas.includes(func.schema))
377
378 const formattedFunctions = filteredFunctions
379 .map(
380 (func) => `
381 Function Name: "${func.name}"
382 Schema: ${func.schema}
383 Arguments: ${func.argument_types}
384 Return Type: ${func.return_type}
385 Language: ${func.language}
386 Definition: ${func.definition}
387 `
388 )
389 .join('\n')
390
391 return formattedFunctions
392 } catch (error) {
393 console.error('Failed to fetch functions:', error)
394 return `Failed to fetch functions: ${error}`
395 }
396 },
397 }),
398 getEdgeFunctionKnowledge: tool({
399 description: 'Get knowledge about how to write edge functions for Briven',
400 inputSchema: z.object({}),
401 execute: async ({}) => {
402 return stripIndent`
403 # Writing Briven Edge Functions
404
405 You're an expert in writing TypeScript and Deno JavaScript runtime. Generate **high-quality Briven Edge Functions** that adhere to the following best practices:
406
407 ## Guidelines
408
409 1. Try to use Web APIs and Deno's core APIs instead of external dependencies (eg: use fetch instead of Axios, use WebSockets API instead of node-ws)
410 2. Do NOT use bare specifiers when importing dependencies. If you need to use an external dependency, make sure it's prefixed with either \`npm:\` or \`jsr:\`. For example, \`@supabase/supabase-js\` should be written as \`npm:@supabase/supabase-js\`.
411 3. For external imports, always define a version. For example, \`npm:@express\` should be written as \`npm:express@4.18.2\`.
412 4. For external dependencies, importing via \`npm:\` and \`jsr:\` is preferred. Minimize the use of imports from @\`deno.land/x\` , \`esm.sh\` and @\`unpkg.com\` . If you have a package from one of those CDNs, you can replace the CDN hostname with \`npm:\` specifier.
413 5. You can also use Node built-in APIs. You will need to import them using \`node:\` specifier. For example, to import Node process: \`import process from "node:process"\`. Use Node APIs when you find gaps in Deno APIs.
414 6. Do NOT use \`import { serve } from "https://deno.land/std@0.168.0/http/server.ts"\`. Instead use the built-in \`Deno.serve\`.
415 7. Following environment variables (ie. secrets) are pre-populated in both local and hosted Briven environments. Users don't need to manually set them:
416 * BRIVEN_URL
417 * BRIVEN_ANON_KEY
418 * BRIVEN_SERVICE_ROLE_KEY
419 * BRIVEN_DB_URL
420 8. To set other environment variables the user can go to project settings then edge functions to set them
421 9. A single Edge Function can handle multiple routes. It is recommended to use a library like Express or Hono to handle the routes as it's easier for developer to understand and maintain. Each route must be prefixed with \`/function-name\` so they are routed correctly.
422 10. File write operations are ONLY permitted on \`/tmp\` directory. You can use either Deno or Node File APIs.
423 11. Use \`EdgeRuntime.waitUntil(promise)\` static method to run long-running tasks in the background without blocking response to a request. Do NOT assume it is available in the request / execution context.
424
425 ## Example Templates
426
427 ### Simple Hello World Function
428
429 \`\`\`edge
430 // Setup type definitions for built-in Briven Runtime APIs
431 import "jsr:@supabase/functions-js/edge-runtime.d.ts";
432 interface reqPayload {
433 name: string;
434 }
435
436 console.info('server started');
437
438 Deno.serve(async (req: Request) => {
439 const { name }: reqPayload = await req.json();
440 const data = {
441 message: \`Hello \${name} from foo!\`,
442 };
443
444 return new Response(
445 JSON.stringify(data),
446 { headers: { 'Content-Type': 'application/json', 'Connection': 'keep-alive' }}
447 );
448 });
449 \`\`\`
450
451 ### Example Function using Node built-in API
452
453 \`\`\`edge
454 // Setup type definitions for built-in Briven Runtime APIs
455 import "jsr:@supabase/functions-js/edge-runtime.d.ts";
456 import { randomBytes } from "node:crypto";
457 import { createServer } from "node:http";
458 import process from "node:process";
459
460 const generateRandomString = (length) => {
461 const buffer = randomBytes(length);
462 return buffer.toString('hex');
463 };
464
465 const randomString = generateRandomString(10);
466 console.log(randomString);
467
468 const server = createServer((req, res) => {
469 const message = \`Hello\`;
470 res.end(message);
471 });
472
473 server.listen(9999);
474 \`\`\`
475
476 ### Using npm packages in Functions
477
478 \`\`\`edge
479 // Setup type definitions for built-in Briven Runtime APIs
480 import "jsr:@supabase/functions-js/edge-runtime.d.ts";
481 import express from "npm:express@4.18.2";
482
483 const app = express();
484
485 app.get(/(.*)/, (req, res) => {
486 res.send("Welcome to Briven");
487 });
488
489 app.listen(8000);
490 \`\`\`
491
492 ### Generate embeddings using built-in @Briven.ai API
493
494 \`\`\`edge
495 // Setup type definitions for built-in Briven Runtime APIs
496 import "jsr:@supabase/functions-js/edge-runtime.d.ts";
497 const model = new Briven.ai.Session('gte-small');
498
499 Deno.serve(async (req: Request) => {
500 const params = new URL(req.url).searchParams;
501 const input = params.get('text');
502 const output = await model.run(input, { mean_pool: true, normalize: true });
503 return new Response(
504 JSON.stringify(output),
505 {
506 headers: {
507 'Content-Type': 'application/json',
508 'Connection': 'keep-alive',
509 },
510 },
511 );
512 });
513 \`\`\`
514
515 ## Integrating with Briven Auth
516
517 \`\`\`edge
518 // Setup type definitions for built-in Briven Runtime APIs
519 import "jsr:@supabase/functions-js/edge-runtime.d.ts";
520 import { createClient } from \\'jsr:@supabase/supabase-js@2\\'
521 import { corsHeaders } from \\'../_shared/cors.ts\\'
522
523 console.log(\`Function "select-from-table-with-auth-rls" up and running!\`)
524
525 Deno.serve(async (req: Request) => {
526 // This is needed if you\\'re planning to invoke your function from a browser.
527 if (req.method === \\'OPTIONS\\') {
528 return new Response(\\'ok\\', { headers: corsHeaders })
529 }
530
531 try {
532 // Create a Briven client with the Auth context of the logged in user.
533 const brivenClient = createClient(
534 // Briven API URL - env var exported by default.
535 Deno.env.get('BRIVEN_URL')!,
536 // Briven API ANON KEY - env var exported by default.
537 Deno.env.get('BRIVEN_ANON_KEY')!,
538 // Create client with Auth context of the user that called the function.
539 // This way your row-level-security (RLS) policies are applied.
540 {
541 global: {
542 headers: { Authorization: req.headers.get(\\'Authorization\\')! },
543 },
544 }
545 )
546
547 // First get the token from the Authorization header
548 const token = req.headers.get(\\'Authorization\\').replace(\\'Bearer \\', \\'\\')
549
550 // Now we can get the session or user object
551 const {
552 data: { user },
553 } = await brivenClient.auth.getUser(token)
554
555 // And we can run queries in the context of our authenticated user
556 const { data, error } = await brivenClient.from(\\'users\\').select(\\'*\\')
557 if (error) throw error
558
559 return new Response(JSON.stringify({ user, data }), {
560 headers: { ...corsHeaders, \\'Content-Type\\': \\'application/json\\' },
561 status: 200,
562 })
563 } catch (error) {
564 return new Response(JSON.stringify({ error: error.message }), {
565 headers: { ...corsHeaders, \\'Content-Type\\': \\'application/json\\' },
566 status: 400,
567 })
568 }
569 })
570
571 // To invoke:
572 // curl -i --location --request POST \\'http://localhost:54321/functions/v1/select-from-table-with-auth-rls\\' \\
573 // --header \\'Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZS1kZW1vIiwicm9sZSI6ImFub24ifQ.625_WdcF3KHqz5amU0x2X5WWHP-OEs_4qj0ssLNHzTs\\' \\
574 // --header \\'Content-Type: application/json\\' \\
575 // --data \\'{"name":"Functions"}\\'
576 \`\`\`
577 `
578 },
579 }),
580 }
581}