# Views - 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](/define/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`](/define/read#views-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

```typescript
// 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:

### Pagination

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

```bash
# 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

```bash
# 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` |

```bash
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:

```typescript
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:

```typescript
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:

```typescript
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.

```typescript
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`:

```json
{
  "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' }` |

`groupBy` always returns a count map keyed by the distinct values of `field`.
`NULL` values bucket under the literal string `"(none)"`. Other rollup
functions (`groupBy` + `sum` of another column) are deferred to a future
release.

### 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.)
- 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`:

```typescript
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`:

```json
{
  "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.

## Response Format

View responses include metadata about the view:

```json
{
  "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](#aggregations).

## Complete Example

```typescript
// 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'] } },
});
```