Permission Object

interface Permission {
  /** Human-readable name for display in admin UI */
  name: string

  /** Optional description explaining the permission's purpose */
  description?: string

  /** Target table in connection.table format */
  table: string

  /** Which CRUD operations this permission grants */
  operations: {
    select?: boolean
    insert?: boolean
    update?: boolean
    delete?: boolean
  }

  /** Allowed columns (allowlist). Omit to allow all columns. */
  columns?: string[]

  /** Row-level filter injected into WHERE clauses (SELECT, UPDATE, DELETE) */
  filter?: Record<string, any>

  /** Validation condition for incoming data (INSERT, UPDATE) */
  check?: Record<string, any>

  /** Server-injected values that override client data (INSERT, UPDATE) */
  preset?: Record<string, any>

  /** Maximum number of rows this permission can return per query */
  limit?: number

  /** Custom SQL fragment for advanced filtering (use with caution) */
  customSql?: string

  /** Custom TypeScript that wraps query execution */
  middleware?: (
    params: { user, db, table, operation, columns, query, input?, filter? },
    next: (overrides?) => Promise<any[]>,
  ) => Promise<any[]>
}

name: 'View own orders'

description: 'Allows users to read orders belonging to their organization. Restricted to non-financial columns.'

table: 'main.orders'
table: 'warehouse.events'
table: 'imports.products'

// Read-only
operations: { select: true }

// Full CRUD
operations: { select: true, insert: true, update: true, delete: true }

// Write-only (insert and update, no read)
operations: { insert: true, update: true }

// Only these columns are visible/writable
columns: ['id', 'amount', 'status', 'created_at']

// All columns allowed (omit the field)
// columns: undefined

// Simple equality
filter: { customer_id: { $eq: '$user.customer_id' } }

// Array membership
filter: { organization_id: { $in: '$user.org_ids' } }

// Multiple conditions (AND)
filter: {
  organization_id: { $eq: '$user.current_org_id' },
  status: { $ne: 'archived' },
}

// Nested relationship filter
filter: {
  organization: {
    members: { user_id: { $eq: '$user.id' } },
  },
}

// Ensure amount is non-negative
check: { amount: { $gte: 0 } }

// Ensure status is one of the allowed values
check: { status: { $in: ['draft', 'active', 'cancelled'] } }

// Multiple validations
check: {
  amount: { $gte: 0, $lte: 1_000_000 },
  status: { $in: ['draft', 'active', 'cancelled'] },
  priority: { $in: ['low', 'medium', 'high'] },
}

// Inject the current user's ID
preset: { created_by: '$user.id' }

// Inject organization and timestamp
preset: {
  organization_id: '$user.current_org_id',
  updated_by: '$user.id',
  updated_at: '$now',
}

// Literal value
preset: { source: 'api' }

// Cap to 100 rows per query
limit: 100

// Allow up to 50,000 rows (for export-type permissions)
limit: 50_000

// Spatial query
customSql: "ST_Distance(location, ST_Point($user.longitude, $user.latitude)) < 10000"

// Date range
customSql: "created_at >= CURRENT_DATE - INTERVAL '30 days'"

middleware: async ({ user, db, operation, input }, next) => {
  // Before: validate
  if (operation === 'insert') {
    const org = await db.query.organizations.findFirst({
      where: eq(organizations.id, user.current_org_id),
    })
    if (org.order_count >= org.order_limit) {
      throw new PermissionError('Order limit reached')
    }
  }

  const rows = await next()

  // After: redact
  if (!user.roles.includes('admin')) {
    return rows.map(({ ssn, ...row }) => row)
  }
  return rows
},

view_own_orders: {
  name: 'View own orders',
  description: 'Read orders belonging to the user\'s organization',
  table: 'main.orders',
  operations: { select: true },
  columns: ['id', 'amount', 'status', 'customer_id', 'created_at'],
  filter: {
    organization_id: { $eq: '$user.current_org_id' },
  },
  limit: 1000,
}

manage_team_tasks: {
  name: 'Manage team tasks',
  description: 'Full CRUD on tasks for the user\'s team',
  table: 'main.tasks',
  operations: { select: true, insert: true, update: true, delete: true },
  columns: ['id', 'title', 'description', 'status', 'priority', 'assigned_to', 'due_date'],
  filter: {
    team_id: { $in: '$user.team_ids' },
  },
  check: {
    status: { $in: ['todo', 'in_progress', 'done', 'cancelled'] },
    priority: { $in: ['low', 'medium', 'high', 'critical'] },
  },
  preset: {
    organization_id: '$user.current_org_id',
    updated_by: '$user.id',
  },
}

submit_feedback: {
  name: 'Submit feedback',
  description: 'Users can submit feedback but cannot read or modify existing entries',
  table: 'main.feedback',
  operations: { insert: true },
  columns: ['message', 'category', 'rating'],
  check: {
    rating: { $gte: 1, $lte: 5 },
    category: { $in: ['bug', 'feature', 'general'] },
  },
  preset: {
    user_id: '$user.id',
    submitted_at: '$now',
    status: 'pending',
  },
}

delete_draft_orders: {
  name: 'Delete draft orders',
  description: 'Users can only delete their own orders that are still in draft status',
  table: 'main.orders',
  operations: { delete: true },
  filter: {
    customer_id: { $eq: '$user.customer_id' },
    status: { $eq: 'draft' },
  },
}