Backend for Developers
and Agents

5 tables · 7 SQL functions · 3 JS functions

Tables

SQL functions POST /rest/v1/rpc/<name>

• create_organization(p_name text, p_slug text) → uuid security definer
• add_org_member(p_org_id uuid, p_email text, p_role text) → void security definer
• remove_org_member(p_org_id uuid, p_user_id uuid) → void security definer
• set_member_role(p_org_id uuid, p_user_id uuid, p_role text) → void security definer
• org_members(p_org_id uuid) → table(user_id uuid, email text, role text, joined_at timestamptz) security definer
• has_active_plan(p_org_id uuid) → boolean
• org_usage(p_org_id uuid, p_since timestamptz) → table(event text, total bigint)

JavaScript functions Node.js

• create-subscription-checkout /functions/v1/create-subscription-checkout signed-in users · 30s
• create-billing-portal /functions/v1/create-billing-portal signed-in users · 30s
• stripe-webhook /functions/v1/stripe-webhook public · 30s

version: 1
project:
  name: SaaS Starter
  description: Organizations, roles, Stripe subscriptions and usage events
auth:
  jwt_expiry: 1h
  refresh_tokens: true
  refresh_token_expiry: 7d
tables:
  plans:
    fields:
      - name: id
        type: text
        primary_key: true
        pattern: "^[a-z][a-z0-9_]*$"
      - name: name
        type: text
        required: true
        check: "char_length(name) <= 100"
      - name: price_cents
        type: integer
        required: true
        default: 0
        min: 0
      - name: seat_limit
        type: integer
        min: 1
      - name: monthly_event_limit
        type: integer
        min: 0
    rls:
      - operations: [select]
        using: "true"
  organizations:
    fields:
      - name: id
        type: uuid
        primary_key: true
        default: uuid_v7()
      - name: name
        type: text
        required: true
        check: "char_length(btrim(name)) BETWEEN 1 AND 100"
      - name: slug
        type: text
        required: true
        pattern: "^[a-z0-9]+(-[a-z0-9]+)*$"
        check: "char_length(slug) <= 63"
      - name: created_by
        foreign_key: { references: auth.users.id, on_delete: set_null }
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [slug]
        unique: true
    rls:
      - operations: [select]
        using: "EXISTS (SELECT 1 FROM memberships m WHERE m.org_id = organizations.id AND m.user_id = auth.uid())"
      - operations: [update]
        using: "EXISTS (SELECT 1 FROM memberships m WHERE m.org_id = organizations.id AND m.user_id = auth.uid() AND m.role IN ('owner', 'admin'))"
        with_check: "EXISTS (SELECT 1 FROM memberships m WHERE m.org_id = organizations.id AND m.user_id = auth.uid() AND m.role IN ('owner', 'admin'))"
      - operations: [delete]
        using: "EXISTS (SELECT 1 FROM memberships m WHERE m.org_id = organizations.id AND m.user_id = auth.uid() AND m.role = 'owner')"
  memberships:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: org_id
        type: uuid
        required: true
        foreign_key: { references: organizations.id, on_delete: cascade }
      - name: user_id
        required: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: role
        type: text
        required: true
        default: member
        enum: [owner, admin, member]
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [org_id, user_id]
        unique: true
      - columns: [user_id]
    rls:
      - operations: [select]
        using: "user_id = auth.uid()"
      - operations: [delete]
        using: "user_id = auth.uid() AND role <> 'owner'"
  subscriptions:
    fields:
      - name: org_id
        type: uuid
        primary_key: true
        foreign_key: { references: organizations.id, on_delete: cascade }
      - name: stripe_customer_id
        type: text
      - name: stripe_subscription_id
        type: text
      - name: plan_id
        type: text
      - name: status
        type: text
        required: true
        default: incomplete
        enum: [incomplete, incomplete_expired, trialing, active, past_due, canceled, unpaid, paused]
      - name: current_period_end
        type: timestamptz
      - name: cancel_at_period_end
        type: boolean
        required: true
        default: false
      - name: updated_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [stripe_customer_id]
      - columns: [stripe_subscription_id]
        unique: true
    rls:
      - operations: [select]
        using: "EXISTS (SELECT 1 FROM memberships m WHERE m.org_id = subscriptions.org_id AND m.user_id = auth.uid())"
  usage_events:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: org_id
        type: uuid
        required: true
        foreign_key: { references: organizations.id, on_delete: cascade }
      - name: user_id
        foreign_key: { references: auth.users.id, on_delete: set_null }
      - name: event
        type: text
        required: true
        check: "char_length(event) BETWEEN 1 AND 100"
      - name: quantity
        type: integer
        required: true
        default: 1
        min: 1
        max: 1000000
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [org_id, event, created_at]
      - columns: [user_id]
    rls:
      - operations: [select]
        using: "EXISTS (SELECT 1 FROM memberships m WHERE m.org_id = usage_events.org_id AND m.user_id = auth.uid())"
      - operations: [insert]
        with_check: "user_id = auth.uid() AND EXISTS (SELECT 1 FROM memberships m WHERE m.org_id = usage_events.org_id AND m.user_id = auth.uid())"
rpc:
  create_organization:
    description: Creates an organization with the caller as owner. Returns the organization id.
    auth_required: true
    language: plpgsql
    volatility: volatile
    security: definer
    set:
      search_path: public
    args:
      - name: p_name
        type: text
        required: true
      - name: p_slug
        type: text
        required: true
    returns:
      type: uuid
    body: |
      DECLARE
        v_id uuid;
      BEGIN
        IF auth.uid() IS NULL THEN
          RAISE EXCEPTION 'sign in required' USING ERRCODE = '42501';
        END IF;
        IF btrim(coalesce(p_name, '')) = '' OR length(p_name) > 100 THEN
          RAISE EXCEPTION 'name must be 1-100 characters';
        END IF;
        INSERT INTO public.organizations (name, slug, created_by) VALUES (btrim(p_name), lower(btrim(p_slug)), auth.uid()) RETURNING id INTO v_id;
        INSERT INTO public.memberships (org_id, user_id, role) VALUES (v_id, auth.uid(), 'owner');
        RETURN v_id;
      END;
  add_org_member:
    description: Owner or admin adds an existing user by email. Returns the same result whether or not the email has an account. Only owners can add owners.
    auth_required: true
    language: plpgsql
    volatility: volatile
    security: definer
    set:
      search_path: public
    args:
      - name: p_org_id
        type: uuid
        required: true
      - name: p_email
        type: text
        required: true
      - name: p_role
        type: text
        default: member
    returns:
      type: void
    body: |
      DECLARE
        v_caller text;
        v_user uuid;
        v_role text := coalesce(p_role, 'member');
        v_limit integer;
      BEGIN
        PERFORM 1 FROM public.organizations WHERE id = p_org_id FOR UPDATE;
        SELECT role INTO v_caller FROM public.memberships WHERE org_id = p_org_id AND user_id = auth.uid();
        IF v_caller IS NULL OR v_caller = 'member' THEN
          RAISE EXCEPTION 'owner or admin required' USING ERRCODE = '42501';
        END IF;
        IF v_role NOT IN ('owner', 'admin', 'member') OR (v_role = 'owner' AND v_caller <> 'owner') THEN
          RAISE EXCEPTION 'role not allowed' USING ERRCODE = '42501';
        END IF;
        SELECT p.seat_limit INTO v_limit FROM public.plans p
        WHERE p.id = coalesce(
          (SELECT s.plan_id FROM public.subscriptions s WHERE s.org_id = p_org_id AND s.status IN ('active', 'trialing')),
          'free');
        IF v_limit IS NOT NULL AND (SELECT count(*) FROM public.memberships WHERE org_id = p_org_id) >= v_limit THEN
          RAISE EXCEPTION 'seat limit reached for this plan';
        END IF;
        SELECT id INTO v_user FROM auth.users WHERE lower(email) = lower(btrim(p_email));
        IF v_user IS NULL THEN
          RETURN;
        END IF;
        INSERT INTO public.memberships (org_id, user_id, role) VALUES (p_org_id, v_user, v_role)
        ON CONFLICT (org_id, user_id) DO NOTHING;
      END;
  remove_org_member:
    description: Owner or admin removes a member. The last owner cannot be removed.
    auth_required: true
    language: plpgsql
    volatility: volatile
    security: definer
    set:
      search_path: public
    args:
      - name: p_org_id
        type: uuid
        required: true
      - name: p_user_id
        type: uuid
        required: true
    returns:
      type: void
    body: |
      DECLARE
        v_caller text;
        v_target text;
      BEGIN
        PERFORM 1 FROM public.organizations WHERE id = p_org_id FOR UPDATE;
        SELECT role INTO v_caller FROM public.memberships WHERE org_id = p_org_id AND user_id = auth.uid();
        IF v_caller IS NULL OR v_caller = 'member' THEN
          RAISE EXCEPTION 'owner or admin required' USING ERRCODE = '42501';
        END IF;
        SELECT role INTO v_target FROM public.memberships WHERE org_id = p_org_id AND user_id = p_user_id;
        IF v_target IS NULL THEN
          RETURN;
        END IF;
        IF v_target = 'owner' AND (v_caller <> 'owner' OR (SELECT count(*) FROM public.memberships WHERE org_id = p_org_id AND role = 'owner') < 2) THEN
          RAISE EXCEPTION 'cannot remove this owner' USING ERRCODE = '42501';
        END IF;
        DELETE FROM public.memberships WHERE org_id = p_org_id AND user_id = p_user_id;
      END;
  set_member_role:
    description: Owner changes a member's role. The last owner cannot be demoted.
    auth_required: true
    language: plpgsql
    volatility: volatile
    security: definer
    set:
      search_path: public
    args:
      - name: p_org_id
        type: uuid
        required: true
      - name: p_user_id
        type: uuid
        required: true
      - name: p_role
        type: text
        required: true
    returns:
      type: void
    body: |
      DECLARE
        v_target text;
      BEGIN
        PERFORM 1 FROM public.organizations WHERE id = p_org_id FOR UPDATE;
        IF NOT EXISTS (SELECT 1 FROM public.memberships WHERE org_id = p_org_id AND user_id = auth.uid() AND role = 'owner') THEN
          RAISE EXCEPTION 'owner required' USING ERRCODE = '42501';
        END IF;
        IF p_role IS NULL OR p_role NOT IN ('owner', 'admin', 'member') THEN
          RAISE EXCEPTION 'role not allowed';
        END IF;
        SELECT role INTO v_target FROM public.memberships WHERE org_id = p_org_id AND user_id = p_user_id;
        IF v_target IS NULL THEN
          RAISE EXCEPTION 'not a member';
        END IF;
        IF v_target = 'owner' AND p_role <> 'owner' AND (SELECT count(*) FROM public.memberships WHERE org_id = p_org_id AND role = 'owner') < 2 THEN
          RAISE EXCEPTION 'cannot demote the last owner' USING ERRCODE = '42501';
        END IF;
        UPDATE public.memberships SET role = p_role WHERE org_id = p_org_id AND user_id = p_user_id;
      END;
  org_members:
    description: Members of an organization with their emails. Callable by any member.
    auth_required: true
    language: sql
    volatility: stable
    security: definer
    set:
      search_path: public
    args:
      - name: p_org_id
        type: uuid
        required: true
    returns:
      type: "table(user_id uuid, email text, role text, joined_at timestamptz)"
    body: |
      SELECT m.user_id, u.email::text, m.role, m.created_at
      FROM public.memberships m JOIN auth.users u ON u.id = m.user_id
      WHERE m.org_id = p_org_id
        AND EXISTS (SELECT 1 FROM public.memberships me WHERE me.org_id = p_org_id AND me.user_id = auth.uid())
      ORDER BY m.created_at
  has_active_plan:
    description: True when the organization has a paid subscription that is active or trialing. Free orgs return false.
    auth_required: true
    language: sql
    volatility: stable
    args:
      - name: p_org_id
        type: uuid
        required: true
    returns:
      type: boolean
    body: |
      SELECT EXISTS (
        SELECT 1 FROM subscriptions s
        WHERE s.org_id = p_org_id
          AND s.status IN ('active', 'trialing')
          AND (s.current_period_end IS NULL OR s.current_period_end > now() - interval '1 day')
      )
  org_usage:
    description: Total usage per event since a point in time, for members of the organization
    auth_required: true
    language: sql
    volatility: stable
    args:
      - name: p_org_id
        type: uuid
        required: true
      - name: p_since
        type: timestamptz
        default: "CAST(NULL AS timestamptz)"
    returns:
      type: "table(event text, total bigint)"
    body: |
      SELECT u.event, sum(u.quantity)::bigint
      FROM usage_events u
      WHERE u.org_id = p_org_id AND u.created_at >= COALESCE(p_since, date_trunc('month', now()))
      GROUP BY u.event
      ORDER BY 2 DESC, 1
      LIMIT 1000
functions:
  create-subscription-checkout:
    runtime: node
    file: functions/create-subscription-checkout.js
    auth_required: true
    timeout: 30s
    env:
      STRIPE_SECRET_KEY: ${INSTANCEZ_ENV_STRIPE_SECRET_KEY:-}
      STRIPE_PRICE_PRO: ${INSTANCEZ_ENV_STRIPE_PRICE_PRO:-}
      STRIPE_PRICE_TEAM: ${INSTANCEZ_ENV_STRIPE_PRICE_TEAM:-}
  create-billing-portal:
    runtime: node
    file: functions/create-billing-portal.js
    auth_required: true
    timeout: 30s
    env:
      STRIPE_SECRET_KEY: ${INSTANCEZ_ENV_STRIPE_SECRET_KEY:-}
  stripe-webhook:
    runtime: node
    file: functions/stripe-webhook.js
    auth_required: false
    timeout: 30s
    env:
      STRIPE_SECRET_KEY: ${INSTANCEZ_ENV_STRIPE_SECRET_KEY:-}
      STRIPE_WEBHOOK_SECRET: ${INSTANCEZ_ENV_STRIPE_WEBHOOK_SECRET:-}
      STRIPE_PRICE_PRO: ${INSTANCEZ_ENV_STRIPE_PRICE_PRO:-}
      STRIPE_PRICE_TEAM: ${INSTANCEZ_ENV_STRIPE_PRICE_TEAM:-}

4 tables · 2 SQL functions · 1 JS function

Tables

SQL functions POST /rest/v1/rpc/<name>

• consume_message_quota() → jsonb security definer
• refund_message_quota() → void security definer

JavaScript functions Node.js

• chat /functions/v1/chat signed-in users · 60s

version: 1
project:
  name: AI Chat
  description: Private Claude conversations with a daily cap
auth:
  jwt_expiry: 1h
  refresh_tokens: true
  refresh_token_expiry: 7d
tables:
  conversations:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: user_id
        required: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: title
        type: text
        required: true
        default: New chat
        check: "char_length(title) between 1 and 200"
      - name: created_at
        type: timestamptz
        required: true
        default: now()
      - name: updated_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [user_id, updated_at]
    rls:
      - operations: [select]
        using: "user_id = auth.uid()"
      - operations: [insert]
        with_check: "user_id = auth.uid()"
      - operations: [update]
        using: "user_id = auth.uid()"
        with_check: "user_id = auth.uid()"
      - operations: [delete]
        using: "user_id = auth.uid()"
  messages:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: conversation_id
        type: bigint
        required: true
        foreign_key: { references: conversations.id, on_delete: cascade }
      - name: user_id
        required: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: role
        type: text
        required: true
        enum: [user, assistant]
      - name: content
        type: text
        required: true
        check: "char_length(content) <= 20000"
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [conversation_id, id]
      - columns: [user_id]
    rls:
      # Only the chat function writes messages, so users cannot forge assistant replies.
      - operations: [select]
        using: "user_id = auth.uid()"
      - operations: [delete]
        using: "user_id = auth.uid()"
  app_settings:
    fields:
      - name: key
        type: text
        primary_key: true
      - name: value
        type: text
        required: true
    rls:
      - operations: [select]
        using: "true"
  usage_daily:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: user_id
        required: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: day
        type: date
        required: true
      - name: message_count
        type: integer
        required: true
        default: 0
        min: 0
    indexes:
      - columns: [user_id, day]
        unique: true
    rls:
      - operations: [select]
        using: "user_id = auth.uid()"
functions:
  chat:
    runtime: node
    file: functions/chat.js
    auth_required: true
    timeout: 60s
    env:
      ANTHROPIC_API_KEY: ${INSTANCEZ_ENV_ANTHROPIC_API_KEY:-}
      ANTHROPIC_MODEL: ${INSTANCEZ_ENV_ANTHROPIC_MODEL:-claude-haiku-4-5-20251001}
rpc:
  consume_message_quota:
    description: Count one message against today's cap; allowed is false once the cap is hit
    auth_required: true
    language: plpgsql
    volatility: volatile
    security: definer
    set: { search_path: "" }
    returns:
      type: jsonb
    body: |
      DECLARE
        v_uid uuid := auth.uid();
        v_cap integer;
        v_used integer;
      BEGIN
        IF v_uid IS NULL THEN RAISE EXCEPTION 'not authenticated'; END IF;
        SELECT CASE WHEN value ~ '^[0-9]{1,6}$' THEN value::integer END INTO v_cap
          FROM public.app_settings WHERE key = 'daily_message_cap';
        v_cap := COALESCE(v_cap, 50);
        INSERT INTO public.usage_daily (user_id, day, message_count)
          VALUES (v_uid, (now() AT TIME ZONE 'utc')::date, 0)
          ON CONFLICT (user_id, day) DO NOTHING;
        UPDATE public.usage_daily SET message_count = message_count + 1
          WHERE user_id = v_uid AND day = (now() AT TIME ZONE 'utc')::date AND message_count < v_cap
          RETURNING message_count INTO v_used;
        IF v_used IS NULL THEN
          RETURN jsonb_build_object('allowed', false, 'used', v_cap, 'cap', v_cap);
        END IF;
        RETURN jsonb_build_object('allowed', true, 'used', v_used, 'cap', v_cap);
      END;
  refund_message_quota:
    description: Give back one message when the model call failed
    auth_required: true
    language: plpgsql
    volatility: volatile
    security: definer
    set: { search_path: "" }
    returns:
      type: void
    body: |
      BEGIN
        IF auth.uid() IS NULL THEN RAISE EXCEPTION 'not authenticated'; END IF;
        UPDATE public.usage_daily SET message_count = greatest(message_count - 1, 0)
          WHERE user_id = auth.uid() AND day = (now() AT TIME ZONE 'utc')::date;
      END;

6 tables · 1 SQL function · 2 JS functions · 1 bucket

Tables

SQL functions POST /rest/v1/rpc/<name>

• is_admin() → boolean security definer

JavaScript functions Node.js

• checkout /functions/v1/checkout signed-in users · 30s
• stripe-webhook /functions/v1/stripe-webhook public · 30s

Storage

• product_images image/png, image/jpeg, image/webp, image/gif, image/avif public · 5MB · 3 policies

version: 1
project:
  name: Online Store
  description: Products, carts, orders and Stripe Checkout
auth:
  jwt_expiry: 1h
  refresh_tokens: true
  refresh_token_expiry: 7d
tables:
  profiles:
    fields:
      - name: id
        primary_key: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: full_name
        type: text
        check: "char_length(full_name) <= 100"
      - name: role
        type: text
        required: true
        default: customer
        enum: [customer, admin]
    rls:
      - operations: [select]
        using: "auth.uid() = id OR public.is_admin()"
      - operations: [insert]
        with_check: "auth.uid() = id AND role = 'customer'"
      - operations: [update]
        using: "auth.uid() = id"
        with_check: "auth.uid() = id AND (role = 'customer' OR public.is_admin())"
  categories:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: name
        type: text
        required: true
        check: "char_length(name) BETWEEN 1 AND 100"
      - name: slug
        type: text
        required: true
        pattern: "^[a-z0-9-]+$"
    indexes:
      - columns: [slug]
        unique: true
    rls:
      - operations: [select]
        using: "true"
      - operations: [insert]
        with_check: "public.is_admin()"
      - operations: [update]
        using: "public.is_admin()"
        with_check: "public.is_admin()"
      - operations: [delete]
        using: "public.is_admin()"
  products:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: category_id
        type: bigint
        foreign_key: { references: categories.id, on_delete: set_null }
      - name: name
        type: text
        required: true
        check: "char_length(name) BETWEEN 1 AND 200"
      - name: description
        type: text
        check: "char_length(description) <= 5000"
      - name: price_cents
        type: integer
        required: true
        min: 0
        max: 10000000
      - name: currency
        type: text
        required: true
        default: usd
        pattern: "^[a-z]{3}$"
      - name: stock
        type: integer
        required: true
        default: 0
        min: 0
        max: 1000000
      - name: image_path
        type: text
      - name: active
        type: boolean
        required: true
        default: true
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [category_id]
      - columns: [active, created_at]
    rls:
      - operations: [select]
        using: "active = true OR public.is_admin()"
      - operations: [insert]
        with_check: "public.is_admin()"
      - operations: [update]
        using: "public.is_admin()"
        with_check: "public.is_admin()"
      - operations: [delete]
        using: "public.is_admin()"
  cart_items:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: user_id
        required: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: product_id
        type: bigint
        required: true
        foreign_key: { references: products.id, on_delete: cascade }
      - name: quantity
        type: integer
        required: true
        default: 1
        min: 1
        max: 99
    indexes:
      - columns: [user_id, product_id]
        unique: true
      - columns: [product_id]
    rls:
      - operations: [select]
        using: "user_id = auth.uid()"
      - operations: [insert]
        with_check: "user_id = auth.uid() AND EXISTS (SELECT 1 FROM products p WHERE p.id = product_id AND p.active)"
      - operations: [update]
        using: "user_id = auth.uid()"
        with_check: "user_id = auth.uid()"
      - operations: [delete]
        using: "user_id = auth.uid()"
  orders:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: user_id
        required: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: status
        type: text
        required: true
        default: pending
        enum: [pending, paid, shipped, cancelled, refunded]
      - name: total_cents
        type: integer
        required: true
        min: 1
      - name: currency
        type: text
        required: true
        pattern: "^[a-z]{3}$"
      - name: stripe_session_id
        type: text
      - name: shipping
        type: jsonb
      - name: created_at
        type: timestamptz
        required: true
        default: now()
      - name: paid_at
        type: timestamptz
    indexes:
      - columns: [user_id, created_at]
      - columns: [stripe_session_id]
        unique: true
    rls:
      - operations: [select]
        using: "user_id = auth.uid() OR public.is_admin()"
      - operations: [update]
        using: "public.is_admin()"
        with_check: "public.is_admin()"
  order_items:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: order_id
        type: bigint
        required: true
        foreign_key: { references: orders.id, on_delete: cascade }
      - name: product_id
        type: bigint
        foreign_key: { references: products.id, on_delete: set_null }
      - name: name
        type: text
        required: true
      - name: unit_price_cents
        type: integer
        required: true
        min: 0
      - name: quantity
        type: integer
        required: true
        min: 1
    indexes:
      - columns: [order_id]
      - columns: [product_id]
    rls:
      - operations: [select]
        using: "EXISTS (SELECT 1 FROM orders o WHERE o.id = order_id AND (o.user_id = auth.uid() OR public.is_admin()))"
storage:
  product_images:
    public: true
    max_size: 5MB
    types: [image/png, image/jpeg, image/webp, image/gif, image/avif]
    rls:
      - operations: [insert]
        with_check: "public.is_admin()"
      - operations: [update]
        using: "public.is_admin()"
        with_check: "public.is_admin()"
      - operations: [delete]
        using: "public.is_admin()"
functions:
  checkout:
    runtime: node
    file: functions/checkout.js
    auth_required: true
    timeout: 30s
    env:
      STRIPE_SECRET_KEY: ${INSTANCEZ_ENV_STRIPE_SECRET_KEY:-}
  stripe-webhook:
    runtime: node
    file: functions/stripe-webhook.js
    auth_required: false
    timeout: 30s
    env:
      STRIPE_WEBHOOK_SECRET: ${INSTANCEZ_ENV_STRIPE_WEBHOOK_SECRET:-}
rpc:
  is_admin:
    language: plpgsql
    volatility: stable
    security: definer
    set:
      search_path: public
    returns:
      type: boolean
    body: |
      BEGIN
        RETURN EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role = 'admin');
      END;

6 tables · 13 SQL functions

Tables

SQL functions POST /rest/v1/rpc/<name>

• is_member(p_workspace_id bigint) → boolean security definer
• is_user_member(p_workspace_id bigint, p_user_id uuid) → boolean security definer
• ws_role(p_workspace_id bigint) → text security definer
• shares_workspace(p_user_id uuid) → boolean security definer
• task_creator(p_task_id bigint) → uuid security definer
• task_workspace(p_task_id bigint) → bigint security definer
• create_workspace(p_name text) → bigint security definer
• create_invite(p_workspace_id bigint, p_role text, p_days integer, p_max_uses integer) → text security definer
• accept_invite(p_token text) → bigint security definer
• set_member_role(p_workspace_id bigint, p_user_id uuid, p_role text) → boolean security definer
• remove_member(p_workspace_id bigint, p_user_id uuid) → boolean security definer
• transfer_ownership(p_workspace_id bigint, p_user_id uuid) → boolean security definer
• workspace_summary(p_workspace_id bigint) → jsonb

version: 1
project:
  name: Team Todo
  description: Workspaces, members, invites, tasks and comments
auth:
  jwt_expiry: 1h
  refresh_tokens: true
  refresh_token_expiry: 7d
tables:
  profiles:
    fields:
      - name: id
        primary_key: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: display_name
        type: text
        check: "char_length(display_name) <= 80"
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    rls:
      - operations: [select]
        using: "id = auth.uid() OR public.shares_workspace(id)"
      - operations: [insert]
        with_check: "id = auth.uid()"
      - operations: [update]
        using: "id = auth.uid()"
        with_check: "id = auth.uid()"
  workspaces:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: name
        type: text
        required: true
        check: "char_length(btrim(name)) BETWEEN 1 AND 80"
      - name: owner_id
        required: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [owner_id]
    rls:
      - operations: [select]
        using: "public.is_member(id)"
      - operations: [update]
        using: "public.ws_role(id) = 'owner'"
        with_check: "public.ws_role(id) = 'owner' AND owner_id = auth.uid()"
      - operations: [delete]
        using: "public.ws_role(id) = 'owner'"
  workspace_members:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: workspace_id
        type: bigint
        required: true
        foreign_key: { references: workspaces.id, on_delete: cascade }
      - name: user_id
        type: uuid
        required: true
        foreign_key: { references: profiles.id, on_delete: cascade }
      - name: role
        type: text
        required: true
        default: member
        enum: [owner, admin, member]
      - name: joined_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [workspace_id, user_id]
        unique: true
      - columns: [user_id]
      - columns: [workspace_id]
        unique: true
        where: "role = 'owner'"
    rls:
      - operations: [select]
        using: "public.is_member(workspace_id)"
  invites:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: workspace_id
        type: bigint
        required: true
        foreign_key: { references: workspaces.id, on_delete: cascade }
      - name: token
        type: text
        required: true
      - name: role
        type: text
        required: true
        default: member
        enum: [admin, member]
      - name: created_by
        foreign_key: { references: auth.users.id, on_delete: set_null }
      - name: expires_at
        type: timestamptz
        required: true
      - name: max_uses
        type: integer
        required: true
        default: 1
        min: 1
      - name: uses
        type: integer
        required: true
        default: 0
        min: 0
    indexes:
      - columns: [token]
        unique: true
      - columns: [workspace_id]
      - columns: [created_by]
    rls:
      - operations: [select]
        using: "public.ws_role(workspace_id) IN ('owner', 'admin')"
      - operations: [delete]
        using: "public.ws_role(workspace_id) IN ('owner', 'admin')"
  tasks:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: workspace_id
        type: bigint
        required: true
        foreign_key: { references: workspaces.id, on_delete: cascade }
      - name: title
        type: text
        required: true
        check: "char_length(btrim(title)) BETWEEN 1 AND 200"
      - name: description
        type: text
        check: "char_length(description) <= 5000"
      - name: status
        type: text
        required: true
        default: todo
        enum: [todo, doing, done]
      - name: priority
        type: smallint
        required: true
        default: 2
        min: 1
        max: 3
      - name: assignee_id
        type: uuid
        foreign_key: { references: profiles.id, on_delete: set_null }
      - name: due_date
        type: date
      - name: created_by
        foreign_key: { references: auth.users.id, on_delete: set_null }
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [workspace_id, status]
      - columns: [assignee_id, due_date]
      - columns: [created_by]
    rls:
      - operations: [select]
        using: "public.is_member(workspace_id)"
      - operations: [insert]
        with_check: "created_by = auth.uid() AND public.is_member(workspace_id) AND (assignee_id IS NULL OR public.is_user_member(workspace_id, assignee_id))"
      - operations: [update]
        using: "public.is_member(workspace_id)"
        with_check: "public.is_member(workspace_id) AND created_by IS NOT DISTINCT FROM public.task_creator(id) AND workspace_id = public.task_workspace(id) AND (assignee_id IS NULL OR public.is_user_member(workspace_id, assignee_id))"
      - operations: [delete]
        using: "public.is_member(workspace_id) AND (created_by = auth.uid() OR public.ws_role(workspace_id) IN ('owner', 'admin'))"
  task_comments:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: task_id
        type: bigint
        required: true
        foreign_key: { references: tasks.id, on_delete: cascade }
      - name: user_id
        type: uuid
        required: true
        foreign_key: { references: profiles.id, on_delete: cascade }
      - name: body
        type: text
        required: true
        pattern: "\\S"
        check: "char_length(body) <= 5000"
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [task_id, created_at]
      - columns: [user_id]
    rls:
      - operations: [select]
        using: "EXISTS (SELECT 1 FROM tasks t WHERE t.id = task_id)"
      - operations: [insert]
        with_check: "user_id = auth.uid() AND EXISTS (SELECT 1 FROM tasks t WHERE t.id = task_id)"
      - operations: [update]
        using: "user_id = auth.uid() AND EXISTS (SELECT 1 FROM tasks t WHERE t.id = task_id)"
        with_check: "user_id = auth.uid() AND EXISTS (SELECT 1 FROM tasks t WHERE t.id = task_id)"
      - operations: [delete]
        using: "user_id = auth.uid()"
rpc:
  is_member:
    language: plpgsql
    volatility: stable
    security: definer
    set:
      search_path: public
    args:
      - name: p_workspace_id
        type: bigint
        required: true
    returns:
      type: boolean
    body: |
      BEGIN
        RETURN EXISTS (SELECT 1 FROM workspace_members WHERE workspace_id = p_workspace_id AND user_id = auth.uid());
      END;
  is_user_member:
    language: plpgsql
    volatility: stable
    security: definer
    set:
      search_path: public
    args:
      - name: p_workspace_id
        type: bigint
        required: true
      - name: p_user_id
        type: uuid
        required: true
    returns:
      type: boolean
    body: |
      BEGIN
        RETURN is_member(p_workspace_id) AND EXISTS (SELECT 1 FROM workspace_members WHERE workspace_id = p_workspace_id AND user_id = p_user_id);
      END;
  ws_role:
    language: plpgsql
    volatility: stable
    security: definer
    set:
      search_path: public
    args:
      - name: p_workspace_id
        type: bigint
        required: true
    returns:
      type: text
    body: |
      BEGIN
        RETURN (SELECT role FROM workspace_members WHERE workspace_id = p_workspace_id AND user_id = auth.uid());
      END;
  shares_workspace:
    language: plpgsql
    volatility: stable
    security: definer
    set:
      search_path: public
    args:
      - name: p_user_id
        type: uuid
        required: true
    returns:
      type: boolean
    body: |
      BEGIN
        RETURN EXISTS (SELECT 1 FROM workspace_members a JOIN workspace_members b ON a.workspace_id = b.workspace_id
                       WHERE a.user_id = auth.uid() AND b.user_id = p_user_id);
      END;
  task_creator:
    language: plpgsql
    volatility: stable
    security: definer
    set:
      search_path: public
    args:
      - name: p_task_id
        type: bigint
        required: true
    returns:
      type: uuid
    body: |
      BEGIN
        RETURN (SELECT created_by FROM tasks WHERE id = p_task_id AND is_member(workspace_id));
      END;
  task_workspace:
    language: plpgsql
    volatility: stable
    security: definer
    set:
      search_path: public
    args:
      - name: p_task_id
        type: bigint
        required: true
    returns:
      type: bigint
    body: |
      BEGIN
        RETURN (SELECT workspace_id FROM tasks WHERE id = p_task_id AND is_member(workspace_id));
      END;
  create_workspace:
    auth_required: true
    language: plpgsql
    volatility: volatile
    security: definer
    set:
      search_path: public
    args:
      - name: p_name
        type: text
        required: true
    returns:
      type: bigint
    body: |
      DECLARE
        ws_id bigint;
      BEGIN
        IF length(btrim(coalesce(p_name, ''))) NOT BETWEEN 1 AND 80 THEN RAISE EXCEPTION 'name must be 1-80 characters'; END IF;
        IF (SELECT count(*) FROM workspaces WHERE owner_id = auth.uid()) >= 20 THEN RAISE EXCEPTION 'workspace limit reached (20)'; END IF;
        INSERT INTO profiles (id) VALUES (auth.uid()) ON CONFLICT DO NOTHING;
        INSERT INTO workspaces (name, owner_id) VALUES (btrim(p_name), auth.uid()) RETURNING id INTO ws_id;
        INSERT INTO workspace_members (workspace_id, user_id, role) VALUES (ws_id, auth.uid(), 'owner');
        RETURN ws_id;
      END;
  create_invite:
    auth_required: true
    language: plpgsql
    volatility: volatile
    security: definer
    set:
      search_path: public
    args:
      - name: p_workspace_id
        type: bigint
        required: true
      - name: p_role
        type: text
        default: member
      - name: p_days
        type: integer
        default: "7"
      - name: p_max_uses
        type: integer
        default: "1"
    returns:
      type: text
    body: |
      DECLARE
        t text := replace(gen_random_uuid()::text, '-', '');
      BEGIN
        IF coalesce(ws_role(p_workspace_id), '') NOT IN ('owner', 'admin') THEN RAISE EXCEPTION 'only owners and admins can invite'; END IF;
        IF p_role IS NULL OR p_role NOT IN ('admin', 'member') THEN RAISE EXCEPTION 'role must be admin or member'; END IF;
        IF p_role = 'admin' AND ws_role(p_workspace_id) <> 'owner' THEN RAISE EXCEPTION 'only the owner can invite admins'; END IF;
        IF p_days IS NULL OR p_max_uses IS NULL OR p_days NOT BETWEEN 1 AND 90 OR p_max_uses NOT BETWEEN 1 AND 1000 THEN RAISE EXCEPTION 'days must be 1-90 and max uses 1-1000'; END IF;
        INSERT INTO invites (workspace_id, token, role, created_by, expires_at, max_uses)
          VALUES (p_workspace_id, t, p_role, auth.uid(), now() + make_interval(days => p_days), p_max_uses);
        RETURN t;
      END;
  accept_invite:
    auth_required: true
    language: plpgsql
    volatility: volatile
    security: definer
    set:
      search_path: public
    args:
      - name: p_token
        type: text
        required: true
    returns:
      type: bigint
    body: |
      DECLARE
        inv invites%ROWTYPE;
      BEGIN
        SELECT * INTO inv FROM invites WHERE token = p_token FOR UPDATE;
        IF NOT FOUND THEN RAISE EXCEPTION 'invite is invalid or expired'; END IF;
        IF is_member(inv.workspace_id) THEN RETURN inv.workspace_id; END IF;
        IF inv.expires_at <= now() OR inv.uses >= inv.max_uses THEN RAISE EXCEPTION 'invite is invalid or expired'; END IF;
        INSERT INTO profiles (id) VALUES (auth.uid()) ON CONFLICT DO NOTHING;
        INSERT INTO workspace_members (workspace_id, user_id, role) VALUES (inv.workspace_id, auth.uid(), inv.role);
        UPDATE invites SET uses = uses + 1 WHERE id = inv.id;
        RETURN inv.workspace_id;
      END;
  set_member_role:
    auth_required: true
    language: plpgsql
    volatility: volatile
    security: definer
    set:
      search_path: public
    args:
      - name: p_workspace_id
        type: bigint
        required: true
      - name: p_user_id
        type: uuid
        required: true
      - name: p_role
        type: text
        required: true
    returns:
      type: boolean
    body: |
      BEGIN
        IF ws_role(p_workspace_id) IS DISTINCT FROM 'owner' THEN RAISE EXCEPTION 'only the owner can change roles'; END IF;
        IF p_role IS NULL OR p_role NOT IN ('admin', 'member') THEN RAISE EXCEPTION 'role must be admin or member'; END IF;
        UPDATE workspace_members SET role = p_role
          WHERE workspace_id = p_workspace_id AND user_id = p_user_id AND role <> 'owner';
        RETURN FOUND;
      END;
  remove_member:
    auth_required: true
    language: plpgsql
    volatility: volatile
    security: definer
    set:
      search_path: public
    args:
      - name: p_workspace_id
        type: bigint
        required: true
      - name: p_user_id
        type: uuid
        required: true
    returns:
      type: boolean
    body: |
      DECLARE
        me text := ws_role(p_workspace_id);
        target uuid := p_user_id;
        target_role text;
      BEGIN
        IF me IS NULL THEN RAISE EXCEPTION 'not a member of this workspace'; END IF;
        SELECT role INTO target_role FROM workspace_members WHERE workspace_id = p_workspace_id AND user_id = target;
        IF target_role IS NULL THEN RETURN false; END IF;
        IF target_role = 'owner' THEN RAISE EXCEPTION 'the owner cannot be removed; transfer ownership first'; END IF;
        IF target <> auth.uid() AND NOT (me = 'owner' OR (me = 'admin' AND target_role = 'member')) THEN
          RAISE EXCEPTION 'not allowed to remove this member';
        END IF;
        UPDATE tasks SET assignee_id = NULL WHERE workspace_id = p_workspace_id AND assignee_id = target;
        DELETE FROM workspace_members WHERE workspace_id = p_workspace_id AND user_id = target;
        RETURN true;
      END;
  transfer_ownership:
    auth_required: true
    language: plpgsql
    volatility: volatile
    security: definer
    set:
      search_path: public
    args:
      - name: p_workspace_id
        type: bigint
        required: true
      - name: p_user_id
        type: uuid
        required: true
    returns:
      type: boolean
    body: |
      BEGIN
        IF ws_role(p_workspace_id) IS DISTINCT FROM 'owner' THEN RAISE EXCEPTION 'only the owner can transfer ownership'; END IF;
        IF p_user_id IS NULL OR p_user_id = auth.uid() OR NOT is_user_member(p_workspace_id, p_user_id) THEN
          RAISE EXCEPTION 'pick another member of this workspace';
        END IF;
        UPDATE workspace_members SET role = 'admin' WHERE workspace_id = p_workspace_id AND user_id = auth.uid();
        UPDATE workspace_members SET role = 'owner' WHERE workspace_id = p_workspace_id AND user_id = p_user_id;
        UPDATE workspaces SET owner_id = p_user_id WHERE id = p_workspace_id;
        RETURN true;
      END;
  workspace_summary:
    auth_required: true
    language: plpgsql
    volatility: stable
    security: invoker
    set:
      search_path: public
    args:
      - name: p_workspace_id
        type: bigint
        required: true
    returns:
      type: jsonb
    body: |
      BEGIN
        IF NOT is_member(p_workspace_id) THEN RAISE EXCEPTION 'workspace not found'; END IF;
        RETURN (SELECT jsonb_build_object(
          'todo', count(*) FILTER (WHERE status = 'todo'),
          'doing', count(*) FILTER (WHERE status = 'doing'),
          'done', count(*) FILTER (WHERE status = 'done'),
          'overdue', count(*) FILTER (WHERE status <> 'done' AND due_date < current_date))
          FROM tasks WHERE workspace_id = p_workspace_id);
      END;

3 tables · 3 SQL functions · 2 JS functions

Tables

SQL functions POST /rest/v1/rpc/<name>

• join_waitlist(p_email text, p_ref text, p_source text, p_ip text) → table(referral_code text, queue_position bigint, queue_total bigint, is_new boolean) security definer
• waitlist_status(p_code text) → table(queue_position bigint, queue_total bigint, referral_count integer, status text) security definer
• invite_batch(p_count integer) → table(email text, referral_code text)

JavaScript functions Node.js

• join /functions/v1/join public · 30s
• export-signups /functions/v1/export-signups signed-in users · 60s

version: 1
project:
  name: Launch Waitlist
  description: Waitlist with referral boosts, queue positions and admin export
auth:
  jwt_expiry: 1h
  refresh_tokens: true
  refresh_token_expiry: 7d
tables:
  waitlist_admins:
    fields:
      - name: user_id
        primary_key: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
    rls:
      - operations: [select]
        using: "user_id = auth.uid()"
  signups:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: email
        type: text
        required: true
        check: "length(email) <= 254 AND email = lower(email)"
      - name: referral_code
        type: text
        required: true
      - name: referred_by
        type: bigint
        foreign_key: { references: signups.id, on_delete: set_null }
      - name: referral_count
        type: integer
        required: true
        default: 0
        min: 0
      - name: status
        type: text
        required: true
        default: waiting
        enum: [waiting, invited]
      - name: source
        type: text
      - name: invited_at
        type: timestamptz
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [email]
        unique: true
      - columns: [referral_code]
        unique: true
      - columns: [status, id]
    rls:
      - operations: [select]
        using: "EXISTS (SELECT 1 FROM waitlist_admins a WHERE a.user_id = auth.uid())"
      - operations: [update]
        using: "EXISTS (SELECT 1 FROM waitlist_admins a WHERE a.user_id = auth.uid())"
        with_check: "EXISTS (SELECT 1 FROM waitlist_admins a WHERE a.user_id = auth.uid())"
      - operations: [delete]
        using: "EXISTS (SELECT 1 FROM waitlist_admins a WHERE a.user_id = auth.uid())"
  join_attempts:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: ip
        type: text
        required: true
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [ip, created_at]
      - columns: [created_at]
    rls:
      - operations: [select]
        using: "false"
rpc:
  join_waitlist:
    description: Called by the join function only (needs the secret key). Validates and dedupes the email, rate limits per IP, credits the referrer, returns code and position.
    auth_required: true
    language: plpgsql
    volatility: volatile
    security: definer
    set:
      search_path: public
    args:
      - name: p_email
        type: text
        required: true
      - name: p_ref
        type: text
      - name: p_source
        type: text
      - name: p_ip
        type: text
    returns:
      type: "table(referral_code text, queue_position bigint, queue_total bigint, is_new boolean)"
    body: |
      DECLARE
        v_email text := lower(btrim(coalesce(p_email, '')));
        v_ip text := nullif(left(btrim(coalesce(p_ip, '')), 64), '');
        v_ref bigint;
        v_row public.signups;
        v_new boolean := false;
        v_tries integer := 0;
      BEGIN
        IF auth.uid() IS NOT NULL THEN
          RAISE EXCEPTION 'use the join function' USING ERRCODE = '42501';
        END IF;
        IF length(v_email) > 254 OR v_email !~ '^[^@\s]+@[^@\s]+\.[^@\s]+$' THEN
          RAISE EXCEPTION 'invalid email';
        END IF;
        SELECT * INTO v_row FROM public.signups WHERE email = v_email;
        IF NOT FOUND THEN
          DELETE FROM public.join_attempts WHERE created_at < now() - interval '1 day';
          IF v_ip IS NOT NULL THEN
            PERFORM pg_advisory_xact_lock(hashtext('join:' || v_ip));
            IF (SELECT count(*) FROM public.join_attempts WHERE ip = v_ip AND created_at > now() - interval '1 hour') >= 10 THEN
              RAISE EXCEPTION 'too many signups, try again later';
            END IF;
            INSERT INTO public.join_attempts (ip) VALUES (v_ip);
          END IF;
          SELECT s.id INTO v_ref FROM public.signups s WHERE s.referral_code = lower(btrim(coalesce(p_ref, '')));
          LOOP
            BEGIN
              INSERT INTO public.signups (email, referral_code, referred_by, source)
              VALUES (v_email, substr(md5(random()::text || clock_timestamp()::text), 1, 8), v_ref, left(btrim(coalesce(p_source, '')), 100))
              ON CONFLICT (email) DO NOTHING
              RETURNING * INTO v_row;
              EXIT;
            EXCEPTION WHEN unique_violation THEN
              v_tries := v_tries + 1;
              IF v_tries > 5 THEN RAISE; END IF;
            END;
          END LOOP;
          IF v_row.id IS NULL THEN
            SELECT * INTO v_row FROM public.signups WHERE email = v_email;
          ELSE
            v_new := true;
            IF v_ref IS NOT NULL THEN
              UPDATE public.signups SET referral_count = referral_count + 1 WHERE id = v_ref;
            END IF;
          END IF;
        END IF;
        RETURN QUERY
        SELECT v_row.referral_code,
               1 + (SELECT count(*) FROM public.signups o WHERE o.status = 'waiting'
                    AND (o.id - 10 * LEAST(o.referral_count, 20), o.id) < (v_row.id - 10 * LEAST(v_row.referral_count, 20), v_row.id)),
               (SELECT count(*) FROM public.signups WHERE status = 'waiting'),
               v_new;
      END;
  waitlist_status:
    description: Queue position and referral count for a referral code
    auth_required: false
    language: sql
    volatility: stable
    security: definer
    set:
      search_path: public
    args:
      - name: p_code
        type: text
        required: true
    returns:
      type: "table(queue_position bigint, queue_total bigint, referral_count integer, status text)"
    body: |
      SELECT CASE WHEN me.status = 'waiting' THEN 1 + (SELECT count(*) FROM public.signups o WHERE o.status = 'waiting'
                    AND (o.id - 10 * LEAST(o.referral_count, 20), o.id) < (me.id - 10 * LEAST(me.referral_count, 20), me.id)) END,
             (SELECT count(*) FROM public.signups WHERE status = 'waiting'),
             me.referral_count, me.status
      FROM public.signups me WHERE me.referral_code = lower(btrim(p_code))
  invite_batch:
    description: Admin only. Marks the next waiting signups as invited, in queue order, and returns them.
    auth_required: true
    language: plpgsql
    volatility: volatile
    args:
      - name: p_count
        type: integer
        required: true
    returns:
      type: "table(email text, referral_code text)"
    body: |
      BEGIN
        IF NOT EXISTS (SELECT 1 FROM public.waitlist_admins a WHERE a.user_id = auth.uid()) THEN
          RAISE EXCEPTION 'admins only' USING ERRCODE = '42501';
        END IF;
        RETURN QUERY
        WITH picked AS MATERIALIZED (
          SELECT s.id FROM public.signups s WHERE s.status = 'waiting'
          ORDER BY s.id - 10 * LEAST(s.referral_count, 20), s.id
          LIMIT GREATEST(LEAST(COALESCE(p_count, 0), 500), 0)
          FOR UPDATE SKIP LOCKED
        ), invited AS (
          UPDATE public.signups u SET status = 'invited', invited_at = now()
          FROM picked WHERE u.id = picked.id
          RETURNING u.email, u.referral_code
        )
        SELECT i.email, i.referral_code FROM invited i;
      END;
functions:
  join:
    runtime: node
    file: functions/join.js
    auth_required: false
    timeout: 30s
    env:
      RESEND_API_KEY: ${INSTANCEZ_ENV_RESEND_API_KEY:-}
      MAIL_FROM: ${INSTANCEZ_ENV_MAIL_FROM:-}
      LANDING_URL: ${INSTANCEZ_ENV_LANDING_URL:-}
  export-signups:
    runtime: node
    file: functions/export-signups.js
    auth_required: true
    timeout: 60s

5 tables · 3 SQL functions · 1 JS function

Tables

SQL functions POST /rest/v1/rpc/<name>

• create_project(p_name text, p_slug text) → jsonb security definer
• set_flag_config(p_flag_id bigint, p_environment_id bigint, p_enabled boolean, p_rollout integer, p_rules jsonb) → jsonb security definer
• flag_snapshot(p_client_key text) → jsonb security definer

JavaScript functions Node.js

• evaluate /functions/v1/evaluate public · 10s

version: 1
project:
  name: Feature Flags
  description: Flags, rollouts, targeting and audit log
auth:
  jwt_expiry: 1h
  refresh_tokens: true
  refresh_token_expiry: 7d
tables:
  projects:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: owner_id
        required: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: name
        type: text
        required: true
        check: "char_length(name) between 1 and 100"
      - name: slug
        type: text
        required: true
        pattern: "^[a-z0-9-]{1,50}$"
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [slug]
        unique: true
      - columns: [owner_id]
    rls:
      # Created through create_project so every project starts with environments.
      - operations: [select]
        using: "owner_id = auth.uid()"
      - operations: [update]
        using: "owner_id = auth.uid()"
        with_check: "owner_id = auth.uid()"
      - operations: [delete]
        using: "owner_id = auth.uid()"
  environments:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: project_id
        type: bigint
        required: true
        foreign_key: { references: projects.id, on_delete: cascade }
      - name: key
        type: text
        required: true
        pattern: "^[a-z0-9-]{1,50}$"
      - name: client_key
        type: text
        required: true
        default: uuid_v4()
        check: "char_length(client_key) >= 16"
    indexes:
      - columns: [project_id, key]
        unique: true
      - columns: [client_key]
        unique: true
    rls:
      - operations: [select]
        using: "EXISTS (SELECT 1 FROM projects p WHERE p.id = environments.project_id AND p.owner_id = auth.uid())"
      - operations: [insert]
        with_check: "EXISTS (SELECT 1 FROM projects p WHERE p.id = environments.project_id AND p.owner_id = auth.uid())"
      - operations: [update]
        using: "EXISTS (SELECT 1 FROM projects p WHERE p.id = environments.project_id AND p.owner_id = auth.uid())"
        with_check: "EXISTS (SELECT 1 FROM projects p WHERE p.id = environments.project_id AND p.owner_id = auth.uid())"
      - operations: [delete]
        using: "EXISTS (SELECT 1 FROM projects p WHERE p.id = environments.project_id AND p.owner_id = auth.uid())"
  flags:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: project_id
        type: bigint
        required: true
        foreign_key: { references: projects.id, on_delete: cascade }
      - name: key
        type: text
        required: true
        pattern: "^[a-z0-9_.-]{1,100}$"
      - name: description
        type: text
        check: "char_length(description) <= 500"
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [project_id, key]
        unique: true
    rls:
      - operations: [select]
        using: "EXISTS (SELECT 1 FROM projects p WHERE p.id = flags.project_id AND p.owner_id = auth.uid())"
      - operations: [insert]
        with_check: "EXISTS (SELECT 1 FROM projects p WHERE p.id = flags.project_id AND p.owner_id = auth.uid())"
      - operations: [update]
        using: "EXISTS (SELECT 1 FROM projects p WHERE p.id = flags.project_id AND p.owner_id = auth.uid())"
        with_check: "EXISTS (SELECT 1 FROM projects p WHERE p.id = flags.project_id AND p.owner_id = auth.uid())"
      - operations: [delete]
        using: "EXISTS (SELECT 1 FROM projects p WHERE p.id = flags.project_id AND p.owner_id = auth.uid())"
  flag_configs:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: flag_id
        type: bigint
        required: true
        foreign_key: { references: flags.id, on_delete: cascade }
      - name: environment_id
        type: bigint
        required: true
        foreign_key: { references: environments.id, on_delete: cascade }
      - name: enabled
        type: boolean
        required: true
        default: false
      - name: rollout_percentage
        type: integer
        required: true
        default: 100
        min: 0
        max: 100
      - name: rules
        type: jsonb
        required: true
        default: "[]"
      - name: updated_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [flag_id, environment_id]
        unique: true
      - columns: [environment_id]
    rls:
      # Writes go through set_flag_config so each change is audited.
      - operations: [select]
        using: "EXISTS (SELECT 1 FROM flags f JOIN projects p ON p.id = f.project_id WHERE f.id = flag_configs.flag_id AND p.owner_id = auth.uid())"
  audit_log:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: project_id
        type: bigint
        required: true
        foreign_key: { references: projects.id, on_delete: cascade }
      - name: actor_id
        foreign_key: { references: auth.users.id, on_delete: set_null }
      - name: flag_key
        type: text
        required: true
      - name: environment_key
        type: text
        required: true
      - name: before
        type: jsonb
      - name: after
        type: jsonb
        required: true
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [project_id, created_at]
      - columns: [actor_id]
    rls:
      - operations: [select]
        using: "EXISTS (SELECT 1 FROM projects p WHERE p.id = audit_log.project_id AND p.owner_id = auth.uid())"
functions:
  evaluate:
    runtime: node
    file: functions/evaluate.js
    auth_required: false
    timeout: 10s
rpc:
  create_project:
    description: Create a project with development and production environments
    auth_required: true
    language: plpgsql
    volatility: volatile
    security: definer
    set: { search_path: "" }
    args:
      - name: p_name
        type: text
        required: true
      - name: p_slug
        type: text
        required: true
    returns:
      type: jsonb
    body: |
      DECLARE
        v_id bigint;
      BEGIN
        IF auth.uid() IS NULL THEN RAISE EXCEPTION 'not authenticated'; END IF;
        IF p_name IS NULL OR char_length(btrim(p_name)) NOT BETWEEN 1 AND 100 THEN RAISE EXCEPTION 'name must be 1-100 characters'; END IF;
        INSERT INTO public.projects (owner_id, name, slug) VALUES (auth.uid(), btrim(p_name), p_slug) RETURNING id INTO v_id;
        INSERT INTO public.environments (project_id, key) VALUES (v_id, 'development'), (v_id, 'production');
        RETURN jsonb_build_object('id', v_id, 'slug', p_slug);
      END;
  set_flag_config:
    description: Upsert a flag's config for one environment and write the audit log
    auth_required: true
    language: plpgsql
    volatility: volatile
    security: definer
    set: { search_path: "" }
    args:
      - name: p_flag_id
        type: bigint
        required: true
      - name: p_environment_id
        type: bigint
        required: true
      - name: p_enabled
        type: boolean
        required: true
      - name: p_rollout
        type: integer
        default: 100
      - name: p_rules
        type: jsonb
        default: "[]"
    returns:
      type: jsonb
    body: |
      DECLARE
        v_project bigint;
        v_flag text;
        v_env text;
        v_before jsonb;
        v_after jsonb;
      BEGIN
        IF auth.uid() IS NULL THEN RAISE EXCEPTION 'not authenticated'; END IF;
        SELECT f.project_id, f.key INTO v_project, v_flag
          FROM public.flags f JOIN public.projects p ON p.id = f.project_id
          WHERE f.id = p_flag_id AND p.owner_id = auth.uid()
          FOR UPDATE OF f;
        IF v_project IS NULL THEN RAISE EXCEPTION 'flag not found'; END IF;
        SELECT e.key INTO v_env FROM public.environments e WHERE e.id = p_environment_id AND e.project_id = v_project;
        IF v_env IS NULL THEN RAISE EXCEPTION 'environment not found'; END IF;
        IF p_rollout IS NULL OR p_rollout < 0 OR p_rollout > 100 THEN RAISE EXCEPTION 'rollout must be 0-100'; END IF;
        IF p_rules IS NULL OR jsonb_typeof(p_rules) <> 'array' THEN RAISE EXCEPTION 'rules must be a JSON array'; END IF;
        IF jsonb_array_length(p_rules) > 50 THEN RAISE EXCEPTION 'at most 50 rules'; END IF;
        IF EXISTS (
          SELECT 1 FROM jsonb_array_elements(p_rules) r
          WHERE jsonb_typeof(r) IS DISTINCT FROM 'object'
             OR jsonb_typeof(r->'attribute') IS DISTINCT FROM 'string'
             OR NOT (COALESCE(r->>'op', '') = ANY (ARRAY['eq','neq','in','not_in','contains','gt','lt']))
             OR (r->>'op' IN ('in','not_in') AND jsonb_typeof(r->'value') IS DISTINCT FROM 'array')
             OR (r->>'op' NOT IN ('in','not_in') AND COALESCE(jsonb_typeof(r->'value'), '') NOT IN ('string','number','boolean'))
        ) THEN RAISE EXCEPTION 'each rule needs attribute, op and value (in/not_in take an array)'; END IF;

        SELECT jsonb_build_object('enabled', enabled, 'rollout', rollout_percentage, 'rules', rules) INTO v_before
          FROM public.flag_configs WHERE flag_id = p_flag_id AND environment_id = p_environment_id;
        INSERT INTO public.flag_configs (flag_id, environment_id, enabled, rollout_percentage, rules)
          VALUES (p_flag_id, p_environment_id, p_enabled, p_rollout, p_rules)
          ON CONFLICT (flag_id, environment_id) DO UPDATE
          SET enabled = EXCLUDED.enabled, rollout_percentage = EXCLUDED.rollout_percentage,
              rules = EXCLUDED.rules, updated_at = now();
        v_after := jsonb_build_object('enabled', p_enabled, 'rollout', p_rollout, 'rules', p_rules);
        INSERT INTO public.audit_log (project_id, actor_id, flag_key, environment_key, before, after)
          VALUES (v_project, auth.uid(), v_flag, v_env, v_before, v_after);
        RETURN v_after;
      END;
  flag_snapshot:
    description: All flag configs for an environment client key, used by the evaluate function
    language: sql
    volatility: stable
    security: definer
    set: { search_path: "" }
    args:
      - name: p_client_key
        type: text
        required: true
    returns:
      type: jsonb
    body: |
      SELECT CASE WHEN EXISTS (SELECT 1 FROM public.environments e0 WHERE e0.client_key = p_client_key) THEN (
        SELECT COALESCE(jsonb_agg(jsonb_build_object(
                 'key', f.key, 'enabled', c.enabled, 'rollout', c.rollout_percentage, 'rules', c.rules)), '[]'::jsonb)
        FROM public.environments e
        JOIN public.flags f ON f.project_id = e.project_id
        LEFT JOIN public.flag_configs c ON c.flag_id = f.id AND c.environment_id = e.id
        WHERE e.client_key = p_client_key
      ) END

5 tables · 3 SQL functions · 1 bucket

Tables

SQL functions POST /rest/v1/rpc/<name>

• home_feed(p_limit integer, p_before timestamptz, p_before_id bigint) → table(id bigint, author_id uuid, body text, image_path text, created_at timestamptz, like_count bigint, liked_by_me boolean, comment_count bigint)
• follow_user(p_user_id uuid) → void
• unfollow_user(p_user_id uuid) → void

Storage

• post_images image/* public · 5MB · 3 policies

version: 1
project:
  name: Social Feed
  description: Posts, follows, likes and a home feed
auth:
  jwt_expiry: 1h
  refresh_tokens: true
  refresh_token_expiry: 7d
tables:
  profiles:
    fields:
      - name: id
        primary_key: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: username
        type: text
        required: true
        pattern: "^[a-z0-9_]{3,20}$"
      - name: display_name
        type: text
        check: "char_length(display_name) <= 50"
      - name: bio
        type: text
        check: "char_length(bio) <= 160"
      - name: avatar_path
        type: text
        check: "char_length(avatar_path) <= 300"
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [username]
        unique: true
    rls:
      - operations: [select]
        using: "true"
      - operations: [insert]
        with_check: "auth.uid() = id AND (avatar_path IS NULL OR avatar_path LIKE auth.uid()::text || '/%')"
      - operations: [update]
        using: "auth.uid() = id"
        with_check: "auth.uid() = id AND (avatar_path IS NULL OR avatar_path LIKE auth.uid()::text || '/%')"
  posts:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: author_id
        required: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: body
        type: text
        required: true
        check: "char_length(btrim(body)) BETWEEN 1 AND 500"
      - name: image_path
        type: text
        check: "char_length(image_path) <= 300"
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [author_id, created_at]
      - columns: [created_at]
    rls:
      - operations: [select]
        using: "true"
      - operations: [insert]
        with_check: "author_id = auth.uid() AND created_at <= now() AND (image_path IS NULL OR image_path LIKE auth.uid()::text || '/%')"
      - operations: [update]
        using: "author_id = auth.uid()"
        with_check: "author_id = auth.uid() AND created_at <= now() AND (image_path IS NULL OR image_path LIKE auth.uid()::text || '/%')"
      - operations: [delete]
        using: "author_id = auth.uid()"
  comments:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: post_id
        type: bigint
        required: true
        foreign_key: { references: posts.id, on_delete: cascade }
      - name: author_id
        required: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: body
        type: text
        required: true
        check: "char_length(btrim(body)) BETWEEN 1 AND 500"
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [post_id, created_at]
      - columns: [author_id]
    rls:
      - operations: [select]
        using: "true"
      - operations: [insert]
        with_check: "author_id = auth.uid() AND created_at <= now()"
      - operations: [update]
        using: "author_id = auth.uid()"
        with_check: "author_id = auth.uid() AND created_at <= now()"
      - operations: [delete]
        using: "author_id = auth.uid() OR EXISTS (SELECT 1 FROM posts p WHERE p.id = post_id AND p.author_id = auth.uid())"
  follows:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: follower_id
        required: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: followee_id
        required: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [follower_id, followee_id]
        unique: true
      - columns: [followee_id]
    rls:
      - operations: [select]
        using: "true"
      - operations: [insert]
        with_check: "follower_id = auth.uid() AND follower_id <> followee_id"
      - operations: [delete]
        using: "follower_id = auth.uid()"
  likes:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: user_id
        required: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: post_id
        type: bigint
        required: true
        foreign_key: { references: posts.id, on_delete: cascade }
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [user_id, post_id]
        unique: true
      - columns: [post_id]
    rls:
      - operations: [select]
        using: "true"
      - operations: [insert]
        with_check: "user_id = auth.uid()"
      - operations: [delete]
        using: "user_id = auth.uid()"
storage:
  post_images:
    public: true
    max_size: 5MB
    types: [image/*]
    rls:
      - operations: [insert]
        with_check: "auth.uid() IS NOT NULL AND name LIKE auth.uid()::text || '/%'"
      - operations: [update]
        using: "auth.uid() IS NOT NULL AND name LIKE auth.uid()::text || '/%'"
        with_check: "auth.uid() IS NOT NULL AND name LIKE auth.uid()::text || '/%'"
      - operations: [delete]
        using: "auth.uid() IS NOT NULL AND name LIKE auth.uid()::text || '/%'"
rpc:
  home_feed:
    description: Posts from followed users and yourself, newest first, cursor paginated
    auth_required: true
    language: sql
    volatility: stable
    args:
      - name: p_limit
        type: integer
        default: 20
      - name: p_before
        type: timestamptz
        default: infinity
      - name: p_before_id
        type: bigint
        default: 9223372036854775807
    returns:
      type: "table(id bigint, author_id uuid, body text, image_path text, created_at timestamptz, like_count bigint, liked_by_me boolean, comment_count bigint)"
    body: |
      SELECT p.id, p.author_id, p.body, p.image_path, p.created_at,
             (SELECT count(*) FROM likes l WHERE l.post_id = p.id) AS like_count,
             EXISTS (SELECT 1 FROM likes l WHERE l.post_id = p.id AND l.user_id = auth.uid()) AS liked_by_me,
             (SELECT count(*) FROM comments c WHERE c.post_id = p.id) AS comment_count
      FROM posts p
      WHERE (p.created_at, p.id) < (COALESCE(p_before, 'infinity'::timestamptz), COALESCE(p_before_id, 9223372036854775807))
        AND (p.author_id = auth.uid()
             OR p.author_id IN (SELECT f.followee_id FROM follows f WHERE f.follower_id = auth.uid()))
      ORDER BY p.created_at DESC, p.id DESC
      LIMIT LEAST(GREATEST(COALESCE(p_limit, 20), 1), 100)
  follow_user:
    description: Follow a user; repeating it is a no-op
    auth_required: true
    language: sql
    volatility: volatile
    args:
      - name: p_user_id
        type: uuid
        required: true
    returns:
      type: void
    body: |
      INSERT INTO follows (follower_id, followee_id) VALUES (auth.uid(), p_user_id)
      ON CONFLICT (follower_id, followee_id) DO NOTHING
  unfollow_user:
    description: Unfollow a user; repeating it is a no-op
    auth_required: true
    language: sql
    volatility: volatile
    args:
      - name: p_user_id
        type: uuid
        required: true
    returns:
      type: void
    body: |
      DELETE FROM follows WHERE follower_id = auth.uid() AND followee_id = p_user_id

5 tables · 4 SQL functions · 2 JS functions

Tables

SQL functions POST /rest/v1/rpc/<name>

• is_admin() → boolean security definer
• submit_ticket(p_email text, p_name text, p_subject text, p_body text) → table(ticket_id bigint, public_token uuid) security definer
• ticket_thread(p_token uuid) → jsonb security definer
• customer_reply(p_token uuid, p_body text) → bigint security definer

JavaScript functions Node.js

• submit-ticket /functions/v1/submit-ticket public · 30s
• reply-ticket /functions/v1/reply-ticket signed-in users · 30s

version: 1
project:
  name: Helpdesk
  description: Public ticket intake with agents, internal notes and SLA due times
auth:
  jwt_expiry: 1h
  refresh_tokens: true
  refresh_token_expiry: 7d
tables:
  agents:
    fields:
      - name: user_id
        primary_key: true
        foreign_key: { references: auth.users.id, on_delete: cascade }
      - name: name
        type: text
        required: true
        check: "length(name) BETWEEN 1 AND 120"
      - name: role
        type: text
        required: true
        default: agent
        enum: [agent, admin]
    rls:
      - operations: [select]
        using: "user_id = auth.uid() OR public.is_admin()"
  tickets:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: public_token
        type: uuid
        required: true
        default: uuid_v4()
      - name: customer_email
        type: text
        required: true
        check: "length(customer_email) <= 254 AND customer_email = lower(customer_email)"
      - name: customer_name
        type: text
        check: "length(customer_name) <= 120"
      - name: subject
        type: text
        required: true
        check: "length(subject) BETWEEN 1 AND 200"
      - name: status
        type: text
        required: true
        default: open
        enum: [open, pending, resolved, closed]
      - name: priority
        type: text
        required: true
        default: normal
        enum: [low, normal, high, urgent]
      - name: assigned_to
        foreign_key: { references: auth.users.id, on_delete: set_null }
      - name: first_response_due_at
        type: timestamptz
      - name: resolution_due_at
        type: timestamptz
      - name: first_responded_at
        type: timestamptz
      - name: resolved_at
        type: timestamptz
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [public_token]
        unique: true
      - columns: [status, priority, created_at]
      - columns: [assigned_to, status]
      - columns: [customer_email]
    rls:
      - operations: [select]
        using: "EXISTS (SELECT 1 FROM agents WHERE user_id = auth.uid())"
      - operations: [update]
        using: "EXISTS (SELECT 1 FROM agents WHERE user_id = auth.uid())"
        with_check: "EXISTS (SELECT 1 FROM agents WHERE user_id = auth.uid())"
      - operations: [delete]
        using: "EXISTS (SELECT 1 FROM agents WHERE user_id = auth.uid() AND role = 'admin')"
  ticket_messages:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: ticket_id
        type: bigint
        required: true
        foreign_key: { references: tickets.id, on_delete: cascade }
      - name: kind
        type: text
        required: true
        enum: [customer, reply, note]
      - name: author_id
        foreign_key: { references: auth.users.id, on_delete: set_null }
      - name: body
        type: text
        required: true
        check: "length(body) BETWEEN 1 AND 10000"
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [ticket_id, created_at]
      - columns: [author_id]
    rls:
      - operations: [select]
        using: "EXISTS (SELECT 1 FROM agents WHERE user_id = auth.uid())"
      - operations: [insert]
        with_check: "author_id = auth.uid() AND kind IN ('reply', 'note') AND EXISTS (SELECT 1 FROM agents WHERE user_id = auth.uid())"
      - operations: [delete]
        using: "author_id = auth.uid() AND kind = 'note'"
  canned_replies:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: title
        type: text
        required: true
        check: "length(title) BETWEEN 1 AND 120"
      - name: body
        type: text
        required: true
        check: "length(body) BETWEEN 1 AND 10000"
    rls:
      - operations: [select]
        using: "EXISTS (SELECT 1 FROM agents WHERE user_id = auth.uid())"
      - operations: [insert]
        with_check: "EXISTS (SELECT 1 FROM agents WHERE user_id = auth.uid() AND role = 'admin')"
      - operations: [update]
        using: "EXISTS (SELECT 1 FROM agents WHERE user_id = auth.uid() AND role = 'admin')"
        with_check: "EXISTS (SELECT 1 FROM agents WHERE user_id = auth.uid() AND role = 'admin')"
      - operations: [delete]
        using: "EXISTS (SELECT 1 FROM agents WHERE user_id = auth.uid() AND role = 'admin')"
  intake_attempts:
    fields:
      - name: id
        type: bigserial
        primary_key: true
      - name: bucket
        type: text
        required: true
      - name: created_at
        type: timestamptz
        required: true
        default: now()
    indexes:
      - columns: [bucket, created_at]
      - columns: [created_at]
    rls:
      - operations: [select]
        using: "false"
rpc:
  is_admin:
    auth_required: false
    language: plpgsql
    volatility: stable
    security: definer
    set: { search_path: public }
    returns:
      type: boolean
    body: |
      BEGIN RETURN EXISTS (SELECT 1 FROM agents WHERE user_id = auth.uid() AND role = 'admin'); END;
  submit_ticket:
    description: Public intake. Validates, rate limits per email and globally, then opens the ticket with SLA due times.
    auth_required: false
    language: plpgsql
    volatility: volatile
    security: definer
    set:
      search_path: public
    args:
      - name: p_email
        type: text
        required: true
      - name: p_name
        type: text
      - name: p_subject
        type: text
        required: true
      - name: p_body
        type: text
        required: true
    returns:
      type: "table(ticket_id bigint, public_token uuid)"
    body: |
      DECLARE
        v_email text := lower(btrim(coalesce(p_email, '')));
        v_subject text := btrim(regexp_replace(coalesce(p_subject, ''), '[[:cntrl:]]+', ' ', 'g'));
        v_body text := btrim(coalesce(p_body, ''));
        v_id bigint;
        v_token uuid;
      BEGIN
        IF length(v_email) > 254 OR v_email !~ '^[^@\s]+@[^@\s]+\.[^@\s]+$' THEN
          RAISE EXCEPTION 'invalid email';
        END IF;
        IF v_subject = '' OR length(v_subject) > 200 THEN
          RAISE EXCEPTION 'subject must be 1-200 characters';
        END IF;
        IF v_body = '' OR length(v_body) > 10000 THEN
          RAISE EXCEPTION 'body must be 1-10000 characters';
        END IF;

        PERFORM pg_advisory_xact_lock(hashtext('intake:' || v_email));
        DELETE FROM public.intake_attempts WHERE created_at < now() - interval '1 day';
        IF (SELECT count(*) FROM public.intake_attempts WHERE bucket = 'email:' || v_email AND created_at > now() - interval '1 hour') >= 5
           OR (SELECT count(*) FROM public.intake_attempts WHERE bucket = 'global' AND created_at > now() - interval '1 hour') >= 200 THEN
          RAISE EXCEPTION 'too many tickets, try again later';
        END IF;
        INSERT INTO public.intake_attempts (bucket) VALUES ('email:' || v_email), ('global');

        INSERT INTO public.tickets (customer_email, customer_name, subject, first_response_due_at, resolution_due_at)
        VALUES (v_email, nullif(left(btrim(coalesce(p_name, '')), 120), ''), v_subject, now() + interval '24 hours', now() + interval '72 hours')
        RETURNING id, tickets.public_token INTO v_id, v_token;
        INSERT INTO public.ticket_messages (ticket_id, kind, body) VALUES (v_id, 'customer', v_body);
        RETURN QUERY SELECT v_id, v_token;
      END;
  ticket_thread:
    description: A customer's ticket and its public messages, looked up by the private token. Internal notes are never returned.
    auth_required: false
    language: sql
    volatility: stable
    security: definer
    set:
      search_path: public
    args:
      - name: p_token
        type: uuid
        required: true
    returns:
      type: jsonb
    body: |
      SELECT jsonb_build_object(
        'id', t.id, 'subject', t.subject, 'status', t.status, 'created_at', t.created_at,
        'messages', COALESCE((
          SELECT jsonb_agg(jsonb_build_object('kind', m.kind, 'body', m.body, 'created_at', m.created_at) ORDER BY m.created_at, m.id)
          FROM public.ticket_messages m WHERE m.ticket_id = t.id AND m.kind IN ('customer', 'reply')
        ), '[]'::jsonb))
      FROM public.tickets t WHERE t.public_token = p_token
  customer_reply:
    description: A customer answers on their ticket. Reopens it and rate limits per ticket.
    auth_required: false
    language: plpgsql
    volatility: volatile
    security: definer
    set:
      search_path: public
    args:
      - name: p_token
        type: uuid
        required: true
      - name: p_body
        type: text
        required: true
    returns:
      type: bigint
    body: |
      DECLARE
        v_body text := btrim(coalesce(p_body, ''));
        v_ticket public.tickets;
        v_id bigint;
      BEGIN
        IF v_body = '' OR length(v_body) > 10000 THEN
          RAISE EXCEPTION 'body must be 1-10000 characters';
        END IF;
        SELECT * INTO v_ticket FROM public.tickets WHERE public_token = p_token;
        IF NOT FOUND OR v_ticket.status = 'closed' THEN
          RAISE EXCEPTION 'ticket not found or closed';
        END IF;
        IF (SELECT count(*) FROM public.ticket_messages WHERE ticket_id = v_ticket.id AND kind = 'customer' AND created_at > now() - interval '1 hour') >= 20 THEN
          RAISE EXCEPTION 'too many messages, try again later';
        END IF;
        INSERT INTO public.ticket_messages (ticket_id, kind, body) VALUES (v_ticket.id, 'customer', v_body) RETURNING id INTO v_id;
        UPDATE public.tickets SET status = 'open', resolved_at = NULL WHERE id = v_ticket.id;
        RETURN v_id;
      END;
functions:
  submit-ticket:
    runtime: node
    file: functions/submit-ticket.js
    auth_required: false
    timeout: 30s
    env:
      RESEND_API_KEY: ${INSTANCEZ_ENV_RESEND_API_KEY:-}
      MAIL_FROM: ${INSTANCEZ_ENV_MAIL_FROM:-}
  reply-ticket:
    runtime: node
    file: functions/reply-ticket.js
    auth_required: true
    timeout: 30s
    env:
      RESEND_API_KEY: ${INSTANCEZ_ENV_RESEND_API_KEY:-}
      MAIL_FROM: ${INSTANCEZ_ENV_MAIL_FROM:-}