Views - Column Level Security

Named field projections with per-view access control. Views live under the read pipeline (read.views) and implement column-level security.

Views implement Column Level Security (CLS) - controlling which columns users can access. This complements Row Level Security (RLS) provided by the Firewall, which controls which rows users can access.

Migration note: views were previously declared at the resource top level (views: {...}). In DSL v2 they live under read.views so the read pipeline owns its own configuration. The top-level form is rejected at compile time.

Security Layer	Controls	Quickback Feature
Row Level Security	Which records	Firewall
Column Level Security	Which fields	Views

Views provide named field projections with role-based access control. Use views to return different sets of columns to different users without duplicating CRUD endpoints.

When to Use Views

Concept	Purpose	Example
CRUD list	Full records	Returns all columns
Masking	Hide values	Email `j*@c****.com`
Views	Exclude columns	Only `id`, `name`, `source`

CRUD list returns all fields to authorized users
Masking transforms sensitive values but still includes the column
Views completely exclude columns from the response

Basic Usage

// features/candidates/candidates.ts
import { sqliteTable, text } from 'drizzle-orm/sqlite-core';
import { defineTable } from '@quickback/compiler';

export const candidates = sqliteTable('candidates', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
  phone: text('phone'),
  source: text('source'),
  resumeUrl: text('resume_url'),
  internalNotes: text('internal_notes'),
  organizationId: text('organization_id').notNull(),
  // ── quickback:audit (compiler-managed — edits are validated, not merged) ──
  createdAt: text('created_at').notNull().default('1970-01-01T00:00:00.000Z').$defaultFn(() => new Date().toISOString()),
  modifiedAt: text('modified_at').notNull().default('1970-01-01T00:00:00.000Z').$defaultFn(() => new Date().toISOString()).$onUpdate(() => new Date().toISOString()),
  createdBy: text('created_by'),
  modifiedBy: text('modified_by'),
  deletedAt: text('deleted_at'),
  deletedBy: text('deleted_by'),
});

export default defineTable(candidates, {
  masking: {
    email: { type: 'email', show: { roles: ['hiring-manager', 'recruiter'] } },
    phone: { type: 'phone', show: { roles: ['hiring-manager', 'recruiter'] } },
  },

  read: {
    access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] },

    // Named field projections
    views: {
      pipeline: {
        fields: ['id', 'name', 'source'],
        access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] },
      },
      full: {
        fields: ['id', 'name', 'email', 'phone', 'source', 'resumeUrl', 'internalNotes'],
        access: { roles: ['hiring-manager', 'recruiter'] },
      },
      report: {
        fields: ['id', 'name', 'source', 'createdAt'],
        access: { roles: ['owner', 'hiring-manager'] },
      },
    },
  },
});

Generated Endpoints

Each named view generates a dedicated GET endpoint:

GET /api/v1/candidates                            # Full read - read.access fields
GET /api/v1/candidates/views/pipeline             # interviewer/recruiter/etc.
GET /api/v1/candidates/views/full                 # hiring-manager/recruiter only
GET /api/v1/candidates/views/report               # report fields

If read.defaultView is set, bare GET /api/v1/candidates can also resolve to that named view. Without defaultView, bare GET /api/v1/candidates returns VIEW_REQUIRED once views are declared.

Query Parameters

Views support the same query parameters as the list endpoint:

Declare a view's query surface explicitly with query.filterable, query.searchable, and query.sortable. Those allowlists are authoritative for that view: a field listed there does not also need .filterable() or .searchable() on its column definition. Column-level modifiers configure ordinary CRUD reads and provide helpful defaults when the compiler generates a surface; an explicitly configured view does not require the declaration twice. On the ordinary CRUD collection endpoint, the column modifiers remain the declaration: only columns marked .filterable(...) accept filters, and .searchable() determines the default cross-column search set. Pagination is independent of these allowlists and remains available on both CRUD reads and named views.

columns: {
  candidateId: q.text().required(), // no column-level query modifier needed
  stage: q.enum(['applied', 'interview', 'offer']).required(),
},
read: {
  views: {
    pipeline: {
      fields: ['id', 'candidateId', 'stage'],
      query: {
        filterable: ['stage'],
        searchable: ['candidateId'],
      },
    },
  },
}

Unknown columns, encrypted columns, and masked-column query restrictions are still validated normally.

Pagination

Parameter	Description	Default	Max
`limit`	Number of records to return	50	100
`offset`	Number of records to skip	0	-

# Get first 10 records
GET /api/v1/candidates/views/pipeline?limit=10

# Get records 11-20
GET /api/v1/candidates/views/pipeline?limit=10&offset=10

Filtering

# Filter by exact value
GET /api/v1/candidates/views/pipeline?source=linkedin

# Filter with operators
GET /api/v1/candidates/views/pipeline?createdAt.gt=2024-01-01

# Multiple filters (AND logic)
GET /api/v1/candidates/views/pipeline?source=linkedin&name.like=Smith

Sorting

Parameter	Description	Default
`sort`	Field to sort by	`createdAt`
`order`	Sort direction (`asc` or `desc`)	`desc`

GET /api/v1/candidates/views/pipeline?sort=name&order=asc

Security

All four security pillars apply to views:

Pillar	Behavior
Firewall	WHERE clause applied (same as list)
Access	Per-view access control
Guards	N/A (read-only)
Masking	Applied to returned fields

Firewall

Views automatically apply the same firewall conditions as the list endpoint. Users only see records within their organization scope.

Access Control

Each view has its own access configuration:

read: {
  access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] },
  views: {
    // Pipeline view - available to all roles
    pipeline: {
      fields: ['id', 'name', 'source'],
      access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] },
    },
    // Full view - recruiter and above
    full: {
      fields: ['id', 'name', 'source', 'email', 'phone', 'internalNotes'],
      access: { roles: ['hiring-manager', 'recruiter'] },
    },
  },
}

Masking

Masking rules are applied to the returned fields. If a view includes a masked field like email, the masking rules still apply:

masking: {
  email: { type: 'email', show: { roles: ['hiring-manager', 'recruiter'] } },
},

read: {
  access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] },
  views: {
    // Even if an interviewer accesses the 'full' view, email will be masked
    // because masking rules take precedence
    full: {
      fields: ['id', 'name', 'email'],
      access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] },
    },
  },
}

How Views and Masking Work Together

Views and masking are orthogonal concerns:

Views control field selection (which columns appear)
Masking controls field transformation (how values appear based on role)

Example configuration:

masking: {
  email: { type: 'email', show: { roles: ['hiring-manager', 'recruiter'] } },
},

read: {
  access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] },
  views: {
    pipeline: {
      fields: ['id', 'name'],  // email NOT included
      access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] },
    },
    full: {
      fields: ['id', 'name', 'email'],  // email included
      access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] },
    },
  },
}

Endpoint	Role	`email` in response?	`email` value
`/views/pipeline`	interviewer	No	N/A
`/views/pipeline`	recruiter	No	N/A
`/views/full`	interviewer	Yes	`j*@c****.com`
`/views/full`	recruiter	Yes	`jane@company.com`

Aggregations

Views can declare pre-computed aggregations that run alongside the row query and surface as aggregations: { ... } on the response. This avoids a custom standalone action for every "count of X grouped by Y" dashboard tile — the firewall and view filters already in place drive the metric.

read: {
  access: { roles: ['owner', 'admin', 'member'] },
  views: {
    pipeline: {
      fields: ['id', 'jobId', 'candidateId', 'status', 'appliedAt'],
      aggregations: {
        totalApplications: { fn: 'count' },
        byStatus:          { fn: 'groupBy', field: 'status' },
      },
    },
  },
}

Response now carries the aggregations alongside data:

{
  "data": [/* paginated rows */],
  "view": "pipeline",
  "pagination": { "count": 50, "page": 1, "pageSize": 50, "hasMore": true },
  "aggregations": {
    "totalApplications": 173,
    "byStatus": {
      "applied": 102,
      "screening": 41,
      "interview": 18,
      "offer": 7,
      "rejected": 5
    }
  }
}

Supported aggregation functions

`fn`	`field`	Returns	Example
`sum`	required, numeric	scalar number	`{ fn: 'sum', field: 'guestCount' }`
`avg`	required, numeric	scalar number	`{ fn: 'avg', field: 'salary' }`
`min`	required	scalar number	`{ fn: 'min', field: 'price' }`
`max`	required	scalar number	`{ fn: 'max', field: 'price' }`
`count`	optional	`COUNT(*)` if omitted, `COUNT(field)` (non-null) if given	`{ fn: 'count' }`
`count_distinct`	required	scalar number	`{ fn: 'count_distinct', field: 'sourceId' }`
`groupBy`	required	`{ value: count }` map	`{ fn: 'groupBy', field: 'status' }`
`rollup`	— (`groupBy`, `count`, `sum`)	array of bucket rows	`{ fn: 'rollup', groupBy: 'optionId', count: true, sum: ['weight'] }`

groupBy always returns a count map keyed by the distinct values of field. NULL values bucket under the literal string "(none)". To sum other columns per bucket, use a declared rollup on an aggregate-only view.

Filter and firewall inheritance

Aggregations always run over the same filtered, firewalled set as the row query. Pagination is intentionally ignored — you get the value over every matching record, not just the current page.

In practice: a request like GET /applications/views/pipeline?status=applied&jobId=job_123&limit=10 returns the first 10 applied rows for that job, AND byStatus reflects only that filtered set (which will be { applied: <total> } since the filter pinned the status). This is the only consistent default — divorcing the metric from the user's filters would surprise.

Constraints (compile-time)

field must reference a real column on the table.
sum/avg require numeric column types (int, real, decimal, numeric, bigint). Non-numeric columns are rejected at compile time.
The same masking opt-out gate that governs query.{filterable, sortable, searchable} applies to aggregations: the view's roles must satisfy the masking rule, otherwise the aggregation is rejected. (A sum(salary) leaks structure across the masked set even when the row payload is redacted.)
groupBy, min and max return the column's raw values (bucket keys, extremes), so on a masked column they need more than an overlap: the view's access must be a plain roles list and every role in it must be in masking.<col>.query.roles (show.roles when query is omitted). Otherwise the build fails — narrow the view, or use count / count_distinct.
At request time every aggregation over a masked column is also gated per caller: a caller whose roles may not query the column gets "(suppressed)" for that aggregation, whatever the view's roles are.
Aggregation names cannot collide with the reserved response fields data, pagination, view.
No raw SQL expr: form. Column references only — declare a generated column or write a custom action if you need arithmetic.

Aggregate-only views

A normal view returns rows and (optionally) aggregations. An aggregate-only view returns the rollup and nothing else — no data[], no field selection. Set aggregateOnly: true:

read: {
  views: {
    'dietary-rollup': {
      aggregateOnly: true,
      fields: [],                                  // must be empty/absent
      kMin: 5,                                       // k-anonymity bucket size
      access: { roles: ['scope:event:fnb', 'admin'] },
      aggregations: {
        dietaryRollup: { fn: 'groupBy', field: 'dietaryNotes' },
        totalGuests:   { fn: 'sum',     field: 'guestCount' },
        headcount:     { fn: 'count' },
      },
    },
  },
}

The response carries only aggregations:

{
  "view": "dietary-rollup",
  "aggregations": {
    "dietaryRollup": { "vegan": 18, "gluten-free": 12, "(none)": 143 },
    "totalGuests": 184,
    "headcount": 173
  }
}

This lets a vendor see "12 guests need a gluten-free meal" without ever reading the roster. The endpoint is gated independently of the base collection and the per-row views — reading the rollup grants zero row or field access.

Constraints (compile-time):

Requires a non-empty aggregations map.
fields must be empty or absent — field selection is disabled, and ?fields= cannot coax row-level data out.
Only the view's query.filterable allowlist is honored. query.searchable and query.sortable are rejected (no ?search=, no ?sort=, no free-form ?<col>= outside the allowlist).

`kMin` — k-anonymity suppression

kMin (default 5) protects against re-identification through small buckets ("1 guest, allergy = shellfish"):

groupBy buckets with fewer than kMin rows collapse into a single "(other)" bucket carrying their summed count.
A scalar count / sum / avg / min / max / count_distinct computed over a filtered set smaller than kMin rows returns the literal string "(suppressed)" instead of a number.

kMin is only meaningful on aggregateOnly: true views. It's ignored on row-returning views, where the aggregations ride alongside the rows and the row firewall already governs disclosure.

Declared rollups (`fn: 'rollup'`)

A rollup is the grouped aggregate an action would otherwise compute in JavaScript — and actions can't run raw aggregates. It emits one firewalled SELECT <groupBy>, COUNT(*), SUM(<col>)… GROUP BY <groupBy> over the view's filtered set, and each returned row carries the bucket and its totals — never a row id or a subject. A caller who may not read individual ballots can read the tally if the view's access admits them:

import { feature, q } from '@quickback/compiler';

export default feature('votes', {
  columns: {
    id:             q.id(),
    pollId:         q.text().required(),
    optionId:       q.text().required(),
    weight:         q.int().default(1).required(),
    organizationId: q.scope('organization'),
    ...q.audit(),
    ...q.softDelete(),
  },
  guards: { createable: ['pollId', 'optionId', 'weight'], updatable: [] },
  read: {
    access: { roles: ['admin'] },          // individual ballots: admins only
    views: {
      tally: {
        aggregateOnly: true,
        fields: [],
        kMin: 1,                           // a poll tally may show single votes
        access: { roles: ['member', 'admin'] },
        query: { filterable: ['pollId'] },
        aggregations: {
          tally: { fn: 'rollup', groupBy: 'optionId', count: true, sum: ['weight'] },
        },
      },
    },
  },
  create: { access: { roles: ['member', 'admin'] } },
  update: { access: { roles: ['admin'] } },
  delete: { access: { roles: ['admin'] }, mode: 'soft' },
});

GET /api/v1/votes/views/tally?pollId=p1 returns:

{
  "view": "tally",
  "aggregations": {
    "tally": [
      { "optionId": "opt_a", "count": 12, "sum": { "weight": 15 } },
      { "optionId": "opt_b", "count": 7,  "sum": { "weight": 7 } }
    ]
  }
}

groupBy names one column; count: true adds the bucket size; sum lists numeric columns, returned under sum. At least one of count / sum.
Only on aggregateOnly: true views — a rollup is its own surface, gated by its own access, never a side-channel of a row view. kMin applies: buckets smaller than k fold into one "(other)" row, and a non-empty set smaller than k returns "(suppressed)".
The bucket may not identify a subject. Grouping by the primary key, the owner column, a q.stamp() identity column, createdBy / modifiedBy / deletedBy, a reference to the auth user table, or an encrypted column is a compile error — one bucket per subject is a per-subject read.
A masked groupBy or sum column needs every role that can reach the view to be in its masking query roles (the groupBy / min / max rule above), and at request time a caller who may not query one of them gets "(suppressed)".

Response Format

View responses include metadata about the view:

{
  "data": [
    { "id": "cnd_123", "name": "Jane Doe", "source": "linkedin" },
    { "id": "cnd_456", "name": "John Smith", "source": "referral" }
  ],
  "view": "pipeline",
  "pagination": {
    "count": 2,
    "page": 1,
    "pageSize": 50,
    "hasMore": false
  }
}

When the view declares aggregations, the response also includes an aggregations: { ... } field. See Aggregations.

Complete Example

// features/jobs/jobs.ts
import { q, defineTable } from '@quickback/compiler';

export const jobs = q.table('jobs', {
  id:             q.id(),
  title:          q.text().required(),
  department:     q.text().required(),
  status:         q.text().default('draft').required(),
  salaryMin:      q.int().optional(),
  salaryMax:      q.int().optional(),
  organizationId: q.scope('organization'),
  ...q.audit(),
  ...q.softDelete(),
});

export default defineTable(jobs, {
  firewall: [{ field: 'organizationId', equals: 'ctx.activeOrgId' }],

  guards: {
    createable: ['title', 'department', 'status', 'salaryMin', 'salaryMax'],
    updatable: ['title', 'department', 'status'],
  },

  masking: {
    salaryMin: { type: 'redact', show: { roles: ['owner', 'hiring-manager'] } },
    salaryMax: { type: 'redact', show: { roles: ['owner', 'hiring-manager'] } },
  },

  read: {
    access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] },
    views: {
      // Public job board view
      board: {
        fields: ['id', 'title', 'department', 'status'],
        access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] },
      },
      // Internal view with salary info
      internal: {
        fields: ['id', 'title', 'department', 'status', 'salaryMin', 'salaryMax'],
        access: { roles: ['owner', 'hiring-manager'] },
      },
      // Compensation report
      compensation: {
        fields: ['id', 'title', 'department', 'salaryMin', 'salaryMax'],
        access: { roles: ['owner', 'hiring-manager'] },
      },
    },
  },

  create: { access: { roles: ['owner', 'hiring-manager'] } },
  update: { access: { roles: ['owner', 'hiring-manager'] } },
  delete: { access: { roles: ['owner', 'hiring-manager'] } },
});

Views - Column Level Security

On this page