Update to Volcano

{
  "description": "Use whenever a Volcano app stores or persists data, including CRUD, records, per-user data, migrations, RLS, todos, chat messages, polls, analytics, counters, click tracking, CMS content, feature flags, and leaderboards.",
  "included_files": [],
  "name": "volcano-database",
  "skill_md_contents": "---\nname: volcano-database\ndescription: Use whenever a Volcano app stores or persists data, including CRUD, records, per-user data, migrations, RLS, todos, chat messages, polls, analytics, counters, click tracking, CMS content, feature flags, and leaderboards.\n---\n# Volcano Database Skill\n\n## Role\nImplement Volcano query-builder access with RLS-first authorization. All persistent data storage MUST use Volcano Databases. This skill is self-contained: the operators, mutation patterns, RLS examples, and error semantics are embedded below.\n\n## Workflow\n1. Initialize the client and call `volcano.database(databaseName)` BEFORE any query.\n2. Use explicit column lists; avoid `select('*')` unless intentional.\n3. Apply deterministic ordering and pagination for list views.\n4. Handle mutation errors at every call site; refresh state after writes.\n\n## Setup\n\n### Browser / Client\n```ts\nimport { VolcanoAuth } from '@volcano.dev/sdk';\n\nconst volcano = new VolcanoAuth({\n  apiUrl: process.env.NEXT_PUBLIC_VOLCANO_API_URL!,\n  anonKey: process.env.NEXT_PUBLIC_VOLCANO_ANON_KEY!,\n});\nvolcano.database(process.env.NEXT_PUBLIC_VOLCANO_DATABASE_NAME!);\n```\n\n### Function / Server-Side\n```ts\nfunction createClient(auth?: { access_token?: string }): VolcanoAuth {\n  const volcano = new VolcanoAuth({\n    apiUrl: process.env.VOLCANO_API_URL!,\n    anonKey: process.env.VOLCANO_ANON_KEY!,\n    accessToken: auth?.access_token,\n  });\n  volcano.database(process.env.VOLCANO_DATABASE!);\n  return volcano;\n}\n```\nUse the same request-scoped client pattern inside Functions handlers when server-side persistence is required.\n\n## SELECT\n\n### Basic\n```ts\nconst { data, error, count } = await volcano.from('posts').select('*');\n```\n\n### Specific columns (preferred)\n```ts\nconst { data } = await volcano.from('posts').select('id, title, created_at');\n```\n\n### Filter operators\n```ts\nvolcano.from('t').select('*').eq('col', value);     // =\nvolcano.from('t').select('*').neq('col', value);    // !=\nvolcano.from('t').select('*').gt('col', n);         // >\nvolcano.from('t').select('*').gte('col', n);        // >=\nvolcano.from('t').select('*').lt('col', n);         // <\nvolcano.from('t').select('*').lte('col', n);        // <=\nvolcano.from('t').select('*').like('col', 'A%');    // case-sensitive\nvolcano.from('t').select('*').ilike('col', '%a%');  // case-insensitive\nvolcano.from('t').select('*').is('col', null);      // NULL check\nvolcano.from('t').select('*').in('col', [a, b, c]); // IN (...)\n```\nPattern syntax: `%` matches any sequence, `_` matches any single character.\n\n### Combine filters (AND)\n```ts\nconst { data } = await volcano\n  .from('products')\n  .select('id, name, price')\n  .eq('category', 'electronics')\n  .gte('price', 100)\n  .lte('price', 500)\n  .eq('in_stock', true);\n```\n\n### Ordering\n```ts\n.order('created_at', { ascending: false })\n.order('category', { ascending: true })\n.order('created_at', { ascending: false }); // chain for multi-column\n```\n\n### Pagination\n```ts\nconst pageSize = 10, page = 3;\nconst { data, count } = await volcano\n  .from('posts')\n  .select('id, title')\n  .order('created_at', { ascending: false })\n  .limit(pageSize)\n  .offset((page - 1) * pageSize);\n```\n\n### Realistic example\n```ts\nasync function getPublishedPosts(category: string, page = 1) {\n  const pageSize = 20;\n  const { data, error, count } = await volcano\n    .from('posts')\n    .select('id, title, excerpt, author_name, created_at')\n    .eq('status', 'published')\n    .eq('category', category)\n    .is('deleted_at', null)\n    .order('created_at', { ascending: false })\n    .limit(pageSize)\n    .offset((page - 1) * pageSize);\n  if (error) throw new Error(`Failed to fetch posts: ${error.message}`);\n  return { posts: data, totalPages: Math.ceil((count ?? 0) / pageSize), currentPage: page };\n}\n```\n\n## INSERT\n```ts\nconst { data, error } = await volcano.insert('posts', {\n  title: 'My New Post',\n  content: 'This is the content.',\n  status: 'draft',\n});\n// data is an array; data[0] is the inserted row including any defaults.\n```\nIf the table has a `user_id` column with default `auth.uid()`, the authenticated user's id is automatically associated.\n\n## UPDATE\n```ts\nconst { data, error } = await volcano\n  .update('posts', {\n    title: 'Updated Title',\n    status: 'published',\n    updated_at: new Date().toISOString(),\n  })\n  .eq('id', postId);\n// data[0] is the updated row. Filter is REQUIRED.\n```\n\nBulk update (multiple matching rows):\n```ts\nawait volcano\n  .update('posts', { status: 'archived' })\n  .lt('created_at', oneYearAgo)\n  .eq('status', 'published');\n```\n\n## DELETE\n```ts\nconst { data, error } = await volcano.delete('posts').eq('id', postId);\n```\n\n### Soft-delete pattern\n```ts\n// Mark deleted\nawait volcano.update('posts', { deleted_at: new Date().toISOString() }).eq('id', postId);\n// Filter from queries\nawait volcano.from('posts').select('*').is('deleted_at', null);\n```\n\n## Modifiers\n\n### `.single()`\nReturn a single row (not an array). Errors if zero or more-than-one row matches.\n```ts\n// After a unique-filter select\nconst { data: post, error } = await volcano\n  .from('posts')\n  .select('*')\n  .eq('id', postId)\n  .single();\n// data is the row, not data[0]\n\n// Inserts (and updates/deletes) do NOT support .select()/.single() chaining —\n// they return { data, error } with data as an array. Take data[0]:\nconst { data, error } = await volcano.insert('tasks', { title, user_id });\nconst task = data?.[0];\n```\n\n## Limitations\nThe query builder does NOT currently expose:\n- **Joins / nested embedded selects** (e.g., `select('*, comments(*)')`).\n- **Upserts** (insert-on-conflict-update).\n- **Multi-statement transactions.**\n- **`.select()` / `.single()` chained onto a mutation.** `insert()`, `update()`, and `delete()` return a `MutationBuilder` that supports only filters (`.eq()`, `.neq()`, `.in()`, … — the update/delete WHERE clause) and resolves to `{ data, error }` with `data` as an **array**. There is no `.select()` or `.single()` on a mutation: `await volcano.insert(...)` (or `.update(...).eq(...)`) and read `data[0]` for the written row. Chaining `.select()` fails at runtime with `volcano.insert(...).select is not a function`.\n\nWhen the work genuinely requires one of these, treat **direct Postgres access** (see below) as a last resort, not the default next step — first check whether the feature can be reshaped to fit the query builder (e.g., denormalize, add a lookup column, split into two queries client-side) before reaching for a raw connection. Consult the fallback reference if the SDK has gained these capabilities since this skill was last updated.\n\n## Direct Postgres Access (discouraged — last resort only)\n\n**Prefer the query builder (`volcano.from(...)`) for essentially everything.** Direct Postgres access inside a Function is an escape hatch for the three specific query-builder gaps above, not a general-purpose alternative data-access layer. Treat every use as exceptional and narrow:\n\n- Do NOT adopt a direct-connection/ORM layer as the project's default architecture. Introducing `pg`, Prisma, Sequelize, TypeORM, etc. opens unbounded surface area (arbitrary SQL, connection lifecycle, pooling bugs, migrations drift) that this skill cannot fully cover or guarantee support for.\n- Reach for it only when a specific piece of work is provably impossible with the query builder (a genuine join/upsert/multi-statement transaction), and scope the raw-SQL usage to that one function/query — not a rewrite of existing query-builder code.\n- Before using it, confirm with the user that this is a deliberate, scoped exception, since it carries more manual RLS-safety responsibility (see the `application_name` rewrite below) than the query builder, which enforces RLS automatically.\n\n**Never browser-side.** Direct connections are Function-only; `DATABASE_URL` is never exposed to browser code.\n\n### The `application_name` rewrite is mandatory\n`DATABASE_URL` is auto-injected into every function's environment, but it already carries `application_name=volcano_full_access` — full admin access that **bypasses RLS**. To scope a connection to the calling user (and get RLS enforcement), you MUST rewrite `application_name` to `volcano_user_access:{user_id}` before connecting:\n\n```js\n// functions/get-posts-with-authors.js\nconst { Pool } = require('pg');\n\n// One pool per auth user — the RLS identity is fixed at connection\n// startup, so a shared pool can't switch users per request. Capped so a\n// warm instance can't accumulate unbounded connections.\nconst MAX_POOLS = 50;\nconst poolsByUser = new Map();\n\nfunction poolForUser(userId) {\n  let pool = poolsByUser.get(userId);\n  if (pool) return pool;\n\n  // NOTE: FIFO eviction (oldest-inserted), not true LRU — swap in an\n  // LRU cache if recency (not insertion order) matters for your traffic.\n  if (poolsByUser.size >= MAX_POOLS) {\n    const oldestUserId = poolsByUser.keys().next().value;\n    poolsByUser.get(oldestUserId).end().catch((err) => console.error('pool.end() failed:', err)); // close before evicting; log instead of crashing on a disconnect error\n    poolsByUser.delete(oldestUserId);\n  }\n\n  const url = new URL(process.env.DATABASE_URL);\n  url.searchParams.set('application_name', `volcano_user_access:${userId}`); // REPLACE, don't append\n  pool = new Pool({ connectionString: url.toString(), max: 5 });\n  poolsByUser.set(userId, pool);\n  return pool;\n}\n\nexports.handler = async (event) => {\n  const auth = event.__volcano_auth;\n  if (!auth) return { statusCode: 401, body: JSON.stringify({ error: 'Unauthorized' }) };\n\n  const client = await poolForUser(auth.user_id).connect();\n  try {\n    // RLS is active: auth.uid() resolves to auth.user_id inside this query.\n    const { rows } = await client.query(`\n      SELECT p.*, u.name AS author_name\n      FROM posts p JOIN users u ON p.user_id = u.id\n      WHERE p.status = $1\n    `, ['published']);\n    return { statusCode: 200, body: JSON.stringify({ posts: rows }) };\n  } finally {\n    client.release();\n  }\n};\n```\n\n### Rules\n- **Rewrite `application_name`, never append** — a duplicate parameter leaves the startup mode up to the driver's dupe handling. Use `url.searchParams.set(...)`, not string concatenation.\n- **Never trust a client-supplied user id** — only use `event.__volcano_auth.user_id` (server-verified), never a value from the request body.\n- **The identity is fixed at connection startup** — `SET application_name` after `connect()` has no effect on RLS scoping. Pool per user (as above); don't share one pool across users.\n- **The per-user pool cache is capped** (`MAX_POOLS`, oldest-evicted with `pool.end()` before removal) — an unbounded cache lets total open connections grow as (distinct users served by this warm instance) × `max` and can exhaust the database's connection limit. Tune `MAX_POOLS × max` against the database's connection limit rather than removing the cap.\n- **Admin/bypass access** (background jobs, migrations, cross-user aggregation) uses the connection string as-is (`volcano_full_access`) or a dedicated service-role connection string — never expose this path to user-triggered requests.\n- **Default to the query builder.** Re-check this section's \"discouraged\" framing before writing new raw-SQL code — direct access should stay the exception, not grow into a parallel data layer.\n\n## Row-Level Security (RLS)\n\n### How it works\n1. Sign-in produces an access token containing the user's id.\n2. Every database call includes that token.\n3. Volcano sets the user context in the DB session.\n4. RLS policies use `auth.uid()` to reference the current user.\n\n### Example policies\n```sql\n-- Users can only read their own posts\nCREATE POLICY \"Users can read own posts\"\nON posts FOR SELECT\nUSING (user_id = auth.uid());\n\n-- Users can only update their own posts\nCREATE POLICY \"Users can update own posts\"\nON posts FOR UPDATE\nUSING (user_id = auth.uid());\n\n-- Users can only insert posts owned by themselves\nCREATE POLICY \"Users can insert own posts\"\nON posts FOR INSERT\nWITH CHECK (user_id = auth.uid());\n\n-- Public-read pattern\nCREATE POLICY \"Published posts are public\"\nON posts FOR SELECT\nUSING (status = 'published');\n```\n\n### Owner column & inserts\n\nPair every owner-scoped `WITH CHECK (user_id = auth.uid())` insert policy with a `DEFAULT auth.uid()` on the column it checks (`user_id uuid NOT NULL DEFAULT auth.uid()`) so the server fills the owner from the token. Otherwise every client insert must set `user_id` itself, and one that omits it is rejected with `new row violates row-level security policy` (`42501`) — Postgres evaluates the RLS `WITH CHECK` before the column's `NOT NULL` constraint, so the missing owner surfaces as a policy violation, not a not-null error. With the default, `insert('posts', { title })` works for the signed-in user without passing `user_id`, and the policy still enforces ownership.\n\nRLS only takes effect once the table has `ALTER TABLE <table> ENABLE ROW LEVEL SECURITY`. For the full `CREATE TABLE` + enable + policy sequence, follow the canonical `posts` migration in the `volcano-platform` skill (\"Canonical migration with RLS\") rather than re-declaring the table here. For a child table (e.g. `comments`), the FK (`post_id`) comes from the client while the owner (`user_id`) still defaults to `auth.uid()`.\n\nThe same client query returns different rows depending on the signed-in user. Do NOT emulate authorization in client code; rely on RLS.\n\n## TypeScript\n```ts\ninterface Post {\n  id: string;\n  title: string;\n  status: 'draft' | 'published' | 'archived';\n  created_at: string;\n}\nconst { data } = await volcano.from<Post>('posts').select('*').eq('status', 'published');\n// data: Post[] | null\n```\n\n## Error Handling\nAll operations return `{ data, error }`. Common messages:\n- `column does not exist` — invalid column name (developer error).\n- `permission denied` — RLS policy blocked the operation.\n- `No active session` — user not authenticated.\n- `Database name not set` — forgot `volcano.database(...)`.\n- `violates unique constraint` — duplicate value on insert.\n- `violates foreign key` — bad reference.\n- `violates check constraint` — invalid input.\n\n## Functions Integration\nWhen persistence belongs in a function, keep the same client pattern inside the handler:\n```ts\n// Client side\nconst { data, error } = await volcano.functions.invoke('get-dashboard-stats', { timeframe: 'last-30-days' });\n\n// Function side: same query-builder flow with a request-scoped client\nexport const handler = async (event: { __volcano_auth?: { access_token?: string } }) => {\n  const auth = event.__volcano_auth;\n  if (!auth) return { statusCode: 401, body: JSON.stringify({ error: 'Unauthorized' }) };\n  const volcano = createClient(auth);\n  const { data, error } = await volcano.from('posts').select('id, title, created_at').order('created_at', { ascending: false });\n  if (error) return { statusCode: 500, body: JSON.stringify({ error: error.message }) };\n  return { statusCode: 200, body: JSON.stringify({ posts: data ?? [] }) };\n};\n```\n\n## Best Practices\n- Always set the database name once after init.\n- Sign in (or accept anonymous) before queries that depend on RLS-bound rows.\n- Select only the columns you need.\n- Paginate large lists (`.limit(50)` is a sensible default).\n- Filter server-side; never fetch everything then filter in JS.\n- Return the `error` object from server handlers as `{ statusCode, body: JSON.stringify({ error: error.message }) }`.\n\n## Verification Checklist\n- `volcano.database(name)` is called before any query.\n- Filter, order, and pagination intent matches the request.\n- RLS policies are assumed to exist; no client-side pseudo-authorization.\n- Mutations have explicit error handling at the call site.\n- Functions doing persistence use a request-scoped client built from `event.__volcano_auth`.\n- The query builder was confirmed insufficient (a genuine join/upsert/multi-statement transaction) before reaching for direct Postgres access — it is not used as a default data-access layer.\n- If using direct Postgres access, `application_name` is rewritten to `volcano_user_access:{user_id}` before connecting — never a bare `DATABASE_URL` for user-scoped queries.\n\n## Optional Fallback Reference\n"
}