Complete Example

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

// Schema
export const candidates = sqliteTable('candidates', {
  id: text('id').primaryKey(),
  organizationId: text('organization_id').notNull(),
  name: text('name').notNull(),
  email: text('email').notNull(),
  phone: text('phone'),
  resumeUrl: text('resume_url'),
  source: text('source'),               // e.g. "linkedin", "referral", "careers-page"
  internalNotes: text('internal_notes'),
  // Note: createdAt, createdBy, modifiedAt, modifiedBy are auto-injected
});

// Security Configuration
export default defineTable(candidates, {
  // FIREWALL: Data isolation
  firewall: {
    organization: {}
  },

  // ACCESS: Role-based permissions
  crud: {
    list:   { access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] } },
    get:    { access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] } },
    create: { access: { roles: ['hiring-manager', 'recruiter'] } },
    update: { access: { roles: ['hiring-manager', 'recruiter'] } },
    delete: { access: { roles: ['hiring-manager'] }, mode: 'hard' },
  },

  // GUARDS: Field-level protection
  guards: {
    createable: ['name', 'email', 'phone', 'resumeUrl', 'source'],
    updatable: ['name', 'phone'],
  },

  // MASKING: PII redaction
  masking: {
    email: { type: 'email', show: { roles: ['hiring-manager', 'recruiter'] } },
    phone: { type: 'phone', show: { roles: ['hiring-manager', 'recruiter'] } },
  },

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

export type Candidate = typeof candidates.$inferSelect;

import { eq, and } from 'drizzle-orm';
import { candidates } from './schema';
import type { AppContext } from '../../lib/types';
import { masks } from '../../lib/masks';

/**
 * Firewall conditions for candidates
 * Pattern: Organization only
 */
export function buildFirewallConditions(ctx: AppContext) {
  const conditions = [];

  // Organization isolation
  conditions.push(eq(candidates.organizationId, ctx.activeOrgId!));

  return and(...conditions);
}

/**
 * Guards configuration for field modification rules
 */
export const GUARDS_CONFIG = {
  createable: new Set(['name', 'email', 'phone', 'resumeUrl', 'source']),
  updatable: new Set(['name', 'phone']),
  immutable: new Set([]),
  protected: {},
  systemManaged: new Set([
    'createdAt', 'createdBy',
    'modifiedAt', 'modifiedBy',
    'deletedAt', 'deletedBy'
  ]),
};

/**
 * Validates field input for CREATE operations.
 */
export function validateCreate(input: Record<string, any>) {
  const systemManaged: string[] = [];
  const protectedFields: Array<{ field: string; actions: string[] }> = [];
  const notCreateable: string[] = [];

  for (const field of Object.keys(input)) {
    if (GUARDS_CONFIG.systemManaged.has(field)) {
      systemManaged.push(field);
      continue;
    }
    if (field in GUARDS_CONFIG.protected) {
      const actions = [...GUARDS_CONFIG.protected[field]];
      protectedFields.push({ field, actions });
      continue;
    }
    if (!GUARDS_CONFIG.createable.has(field) && !GUARDS_CONFIG.immutable.has(field)) {
      notCreateable.push(field);
    }
  }

  const valid = systemManaged.length === 0 &&
                protectedFields.length === 0 &&
                notCreateable.length === 0;

  return { valid, systemManaged, protected: protectedFields, notCreateable };
}

/**
 * Validates field input for UPDATE operations.
 */
export function validateUpdate(input: Record<string, any>) {
  const systemManaged: string[] = [];
  const immutable: string[] = [];
  const protectedFields: Array<{ field: string; actions: string[] }> = [];
  const notUpdatable: string[] = [];

  for (const field of Object.keys(input)) {
    if (GUARDS_CONFIG.systemManaged.has(field)) {
      systemManaged.push(field);
      continue;
    }
    if (GUARDS_CONFIG.immutable.has(field)) {
      immutable.push(field);
      continue;
    }
    if (field in GUARDS_CONFIG.protected) {
      const actions = [...GUARDS_CONFIG.protected[field]];
      protectedFields.push({ field, actions });
      continue;
    }
    if (!GUARDS_CONFIG.updatable.has(field)) {
      notUpdatable.push(field);
    }
  }

  const valid = systemManaged.length === 0 &&
                immutable.length === 0 &&
                protectedFields.length === 0 &&
                notUpdatable.length === 0;

  return { valid, systemManaged, immutable, protected: protectedFields, notUpdatable };
}

/**
 * Masks sensitive fields based on user role
 */
export function maskCandidate<T extends Record<string, any>>(
  record: T,
  ctx: AppContext
): T {
  const masked: any = { ...record };

  // email: show to hiring-manager, recruiter
  if (!ctx.roles?.some(r => ['hiring-manager', 'recruiter'].includes(r))) {
    if (masked['email'] != null) {
      masked['email'] = masks.email(masked['email']);
    }
  }

  // phone: show to hiring-manager, recruiter
  if (!ctx.roles?.some(r => ['hiring-manager', 'recruiter'].includes(r))) {
    if (masked['phone'] != null) {
      masked['phone'] = masks.phone(masked['phone']);
    }
  }

  return masked as T;
}

export function maskCandidates<T extends Record<string, any>>(
  records: T[],
  ctx: AppContext
): T[] {
  return records.map(r => maskCandidate(r, ctx));
}

// CRUD Access configuration
export const CRUD_ACCESS = {
  list:   { access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] } },
  get:    { access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] } },
  create: { access: { roles: ['hiring-manager', 'recruiter'] } },
  update: { access: { roles: ['hiring-manager', 'recruiter'] } },
  delete: { access: { roles: ['hiring-manager'] }, mode: 'hard' },
};

// Views configuration
export const VIEWS_CONFIG = {
  pipeline: {
    fields: ['id', 'name', 'source'],
    access: { roles: ['owner', 'hiring-manager', 'recruiter', 'interviewer'] },
  },
  full: {
    fields: ['id', 'name', 'email', 'phone', 'resumeUrl', 'source', 'internalNotes'],
    access: { roles: ['hiring-manager', 'recruiter'] },
  },
};

import { Hono } from 'hono';
import { eq, and } from 'drizzle-orm';
import { candidates } from './schema';
import {
  buildFirewallConditions,
  validateCreate,
  validateUpdate,
  maskCandidate,
  maskCandidates,
  CRUD_ACCESS
} from './candidates.resource';
import { evaluateAccess } from '../../lib/access';
import { AuthErrors, AccessErrors, GuardErrors } from '../../lib/errors';

const app = new Hono();

// GET /candidates - List with firewall + masking
app.get('/', async (c) => {
  const ctx = c.get('ctx');
  const db = c.get('db');

  // Access check
  if (!await evaluateAccess(CRUD_ACCESS.list.access, ctx)) {
    return c.json(AccessErrors.roleRequired(
      CRUD_ACCESS.list.access.roles,
      ctx.roles
    ), 403);
  }

  // Query with firewall conditions
  const results = await db.select().from(candidates)
    .where(buildFirewallConditions(ctx));

  // Apply masking before returning
  return c.json({
    data: maskCandidates(results, ctx),
  });
});

// POST /candidates - Create with guards
app.post('/', async (c) => {
  const ctx = c.get('ctx');
  const db = c.get('db');

  // Access check
  if (!await evaluateAccess(CRUD_ACCESS.create.access, ctx)) {
    return c.json(AccessErrors.roleRequired(
      CRUD_ACCESS.create.access.roles,
      ctx.roles
    ), 403);
  }

  const body = await c.req.json();

  // Guards validation
  const validation = validateCreate(body);
  if (!validation.valid) {
    if (validation.systemManaged.length > 0) {
      return c.json(GuardErrors.systemManaged(validation.systemManaged), 400);
    }
    if (validation.notCreateable.length > 0) {
      return c.json(GuardErrors.fieldNotCreateable(validation.notCreateable), 400);
    }
  }

  // Apply ownership and audit fields
  const data = {
    id: 'cnd_' + crypto.randomUUID().replace(/-/g, ''),
    ...body,
    organizationId: ctx.activeOrgId,
    createdAt: new Date().toISOString(),
    createdBy: ctx.userId,
    modifiedAt: new Date().toISOString(),
    modifiedBy: ctx.userId,
  };

  const result = await db.insert(candidates).values(data).returning();
  return c.json(maskCandidate(result[0], ctx), 201);
});

// PATCH /candidates/:id - Update with guards
app.patch('/:id', async (c) => {
  const ctx = c.get('ctx');
  const db = c.get('db');
  const id = c.req.param('id');

  // Fetch with firewall
  const [record] = await db.select().from(candidates)
    .where(and(buildFirewallConditions(ctx), eq(candidates.id, id)));

  if (!record) {
    return c.json({ error: 'Not found', code: 'NOT_FOUND' }, 404);
  }

  // Access check
  if (!await evaluateAccess(CRUD_ACCESS.update.access, ctx)) {
    return c.json(AccessErrors.roleRequired(
      CRUD_ACCESS.update.access.roles,
      ctx.roles
    ), 403);
  }

  const body = await c.req.json();

  // Guards validation
  const validation = validateUpdate(body);
  if (!validation.valid) {
    if (validation.notUpdatable.length > 0) {
      return c.json(GuardErrors.fieldNotUpdatable(validation.notUpdatable), 400);
    }
  }

  // Apply audit fields
  const data = {
    ...body,
    modifiedAt: new Date().toISOString(),
    modifiedBy: ctx.userId,
  };

  const result = await db.update(candidates).set(data)
    .where(eq(candidates.id, id)).returning();

  return c.json(maskCandidate(result[0], ctx));
});

export default app;

# Allowed: Create with createable fields
POST /candidates
{ "name": "Jane Smith", "email": "jane@example.com", "source": "linkedin" }

# Rejected: Update non-updatable field
PATCH /candidates/cnd_123
{ "email": "new@example.com" }
# -> 400: "Field 'email' is not updatable"

# Rejected: Set system-managed field
POST /candidates
{ "name": "Jane Smith", "createdBy": "hacker" }
# -> 400: "System-managed fields cannot be set: createdBy"