Building a Structured Performance Review Cycle in Airtable

The Business Situation

Clearbrook Technical Services is a fictional 84-person engineering and compliance services firm. Its two-person People Operations team coordinates performance reviews for employees, 11 managers, and a three-person leadership calibration group.

The company completes one annual review for each active employee and approximately 24 probationary reviews during the year. This produces an estimated 108 review cycles annually. Most annual reviews are concentrated into an eight-week period, creating a substantial administrative peak for People Operations.

Employee self-assessments and manager assessments were prepared in Microsoft Word, exchanged through email, and saved into inconsistently named Google Drive folders. People Operations maintained a separate spreadsheet to track submissions, overdue work, calibration, approval, and completion.

Note: This case study is provided as a representative example of the types of AI integration and digital transformation solutions Intelligex designs and delivers. Actual engagements are tailored to each client’s goals, constraints, existing systems, timeline, and available resources, so the approach, tools, and outcomes may vary.

The process did not fail because the review questions were inadequate. It failed operationally because the documents, deadlines, ownership rules, approvals, and reporting were disconnected.

Clearbrook wanted a structured process without immediately purchasing dedicated human resources software. It needed to retain human control over ratings, calibration, approval, and employment decisions while automating routine coordination.

The Existing Process

The original annual review cycle followed this sequence:

1. People Operations copied the previous year’s Word templates and updated the dates.
2. An email was sent to each employee with a self-assessment document attached.
3. Employees edited the attachment and returned it by email.
4. People Operations downloaded each file, renamed it, and saved it to Google Drive.
5. The corresponding manager received the employee document and a separate manager template.
6. The manager completed the assessment and returned both documents by email.
7. People Operations copied ratings and completion dates into a spreadsheet.
8. Department leaders reviewed documents during calibration meetings.
9. Changes were sent back to managers by email, often without a controlled version number.
10. People Operations recorded approval in the spreadsheet and moved files into a completed folder.
11. The manager held the review conversation and emailed People Operations to confirm completion.

The tracker also lacked a dependable audit history. A changed completion date did not explain who made the change, why it was changed, or whether the underlying review had actually progressed.

Probationary reviews created an additional problem. Their deadlines were based on individual start dates rather than one annual calendar, so they were easier to overlook between major review cycles.

What the New System Needed to Do

People Operations defined the business rules before selecting the implementation tools. The system needed to coordinate work, not make judgments about employees.

Implementation Approaches Considered

Improving the current process

Standard templates and naming conventions would reduce some confusion, but People Operations would still reconcile documents, emails, and spreadsheet rows manually. This option did not address status uncertainty or reminder workload.

Using Google Workspace only

Google Forms, Sheets, Drive, Docs, Gmail, and Google Apps Script could support the full process. This would reduce software sprawl, but Clearbrook did not have an internal script owner who could maintain authentication, triggers, error handling, and deployment.

Connecting Airtable and Google Workspace through Zapier

This approach provided a relational system of record, structured views, native forms and interfaces where appropriate, and configurable cross-platform automation. People Operations could maintain fields and views without editing application code.

Purchasing dedicated software

A dedicated performance platform could provide employee portals, competency libraries, review workflows, and vendor support. Clearbrook decided the current volume and process complexity did not yet justify replacing the existing productivity stack.

Building a custom application

A custom application offered the most control but also required identity management, security testing, hosting, backups, monitoring, user support, and ongoing engineering capacity. It was not proportionate to 108 reviews per year.

The Selected Solution

Clearbrook selected Airtable as the system of record and Zapier as the automation layer. Google Forms collected authenticated employee and manager input. Google Drive and Google Docs stored controlled review documents. Gmail delivered invitations, reminders, escalations, and confirmations.

The company retained Google Workspace because employees already used company-managed Google identities. Google Forms could therefore collect verified email addresses and restrict access to authorized users, subject to the organization’s Workspace configuration.

Routine copying, folder creation, reminder preparation, status reconciliation, and document naming were removed. Ratings, calibration changes, procedural approval, review conversations, and employment decisions remained human-controlled.

System Architecture and Data Flow

Airtable acts as the workflow authority. A status is considered valid only when it is stored in Airtable with the required supporting fields. Email delivery or the presence of a file alone does not complete a review stage.

1. Review creation: People Operations creates or imports a review in Airtable. Required employee, manager, cycle, and due-date fields are validated. Incomplete records remain in a preparation view.
2. Initialization: A Zapier workflow detects a review that has entered the Ready to Initialize view. It finds or creates the correct Google Drive folder, stores the returned folder identifier, assigns the People Operations owner, and changes the review to Self Review Open.
3. Invitation: Zapier creates a unique outbound-message record, marks it as Sending, and sends the employee a Gmail message containing a prefilled self-assessment form link. The message subject includes the Review ID.
4. Employee submission: Google Forms collects the verified email address, Review ID, competency scores, and narrative responses. Zapier validates the Review ID and confirms that the respondent matches the employee email stored in Airtable.
5. Response recording: Zapier searches Airtable for the source submission key and the canonical self-response key. A valid new response is created or an allowed pre-deadline resubmission updates the canonical response. The review advances to Manager Review.
6. Manager invitation: Gmail sends the manager a link to the manager assessment. The message is generated only after the accepted employee response has been recorded.
7. Manager submission: The same validation pattern confirms the manager’s identity. Zapier records the response, calculates score averages in Airtable, creates a calibration document from a Google Docs template, and moves the review to Calibration Pending.
8. Calibration: Authorized leaders review the material. People Operations records Calibrated, Changes Requested, or On Hold in a restricted Airtable interface. Changes Requested returns the review to the manager.
9. Approval: Required approval records are created. Approvers use a restricted interface rather than email buttons. Approval captures the approver, date, decision, and comments.
10. Finalization: When all required approvals are recorded, Zapier generates a final document, stores its Google Drive identifier, and notifies the manager to conduct the review conversation.
11. Acknowledgment and closure: The employee acknowledgment form records the conversation date and receipt. People Operations confirms required documents and changes the review to Completed.
12. Reminders and failure paths: A scheduled Zap searches Airtable views for due reminders, overdue stages, uncertain email states, and automation failures. Failed records remain in a recovery queue instead of advancing.

• Intake: Company-restricted Google Forms with prefilled Review IDs and verified respondent email addresses.
• System of record: Airtable base containing related workflow tables.
• Automation layer: Zapier multi-step workflows, filters, paths, scheduled checks, and local JavaScript validation for the optional AI output.
• Document storage: Restricted Google Drive folders and Google Docs templates.
• Notifications: Gmail connected through a dedicated People Operations automation user.
• Reporting: Airtable views and restricted Interfaces using live base data.
• AI layer: Optional narrative summarization after deterministic validation and explicit human authorization.

Data Structure

The Airtable base contains seven related tables: People, Review Cycles, Reviews, Responses, Approvals, Messages, and Automation Events.

• One person can have many reviews.
• One review cycle contains many reviews.
• One review can have multiple response types, approval records, messages, and automation events.
• One accepted response is retained for each review and response type, while superseded submissions remain available for audit purposes.

Airtable does not enforce database-style unique constraints on ordinary text fields. Every creation workflow therefore performs a Find Record step using Review ID, Response Key, Message Key, or Source Event Key before creating a record.

Workflow Statuses and Ownership

A record moves backward only through an explicit Changes Requested or authorized reopen action. A low score never changes status automatically. Rejection means that the review package needs correction or does not meet the process requirement; it is not an employment decision.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Confirm Google Workspace capabilities. Use a company-managed Workspace environment that supports authenticated Forms and the organization’s chosen shared-drive arrangement. If Shared drives are unavailable, use a restricted folder owned by a dedicated automation user and document the ownership risk.
2. Create a dedicated automation user. Clearbrook used an account such as peopleops-automation@YOUR_COMPANY_DOMAIN. The account owns Forms and templates, can create files in the review Shared drive, and is authorized to send from the approved People Operations mailbox or alias.
3. Create the People Operations mailbox. Use a monitored address such as peopleops@YOUR_COMPANY_DOMAIN. At least two People Operations staff members should be able to review replies and delivery failures.
4. Create the Airtable workspace and base. Select an Airtable subscription that supports the required automation integrations, interface access model, record history, and permission controls. Verify current product capabilities before procurement.
5. Create the Zapier workspace. The selected Zapier subscription must support the required number of multi-step workflows, filters, scheduled runs, paths or equivalent branching, task volume, and shared administration.
6. Assign administrators. One People Operations lead owns business configuration. One technical administrator owns connections, failed runs, and credential rotation. Each role has a named backup.
7. Restrict Airtable base access. Only People Operations administrators receive full base access. Managers and employees do not receive unrestricted base access. Approvers use a restricted Interface if the chosen Airtable access model can enforce the required visibility.
8. Create test identities. Prepare one test employee, one test manager, one test approver, one alternate approver, and one unauthorized account. Use synthetic names and narratives.
9. Create a separate test base. Clone the schema without production records. Connect test Forms, a test Drive location, and a test Gmail sender before enabling production workflows.
10. Record connection ownership. Document which administrator owns each Airtable, Zapier, Google Drive, Google Docs, Google Forms, Gmail, and optional AI connection.

The Zapier connection receives only the permissions needed to read and update the selected Airtable base, create files in the review storage location, read form submissions, and send approved messages. Real credentials are entered through the platforms’ connection interfaces and are never stored in Airtable fields or Zap descriptions.

Step 2: Build the Intake

Create three Google Forms owned by the dedicated automation user: Employee Self-Assessment, Manager Assessment, and Review Acknowledgment.

Configure Forms to collect verified company email addresses and restrict access to authorized organizational users where the Workspace configuration permits it. A prefilled Review ID is a convenience, not a security control. Zapier must still compare the verified respondent email with the employee or manager email stored in Airtable.

Do not collect medical details, protected-characteristic information, passwords, investigation records, or unrelated personal information. Place a concise privacy notice above the narrative fields explaining the purpose, authorized readers, retention policy, and prohibited content.

Clearbrook chose not to enable file uploads in the forms. Managers place approved supporting documents in the restricted review folder. This avoids files being stored in a general form-upload location with different inherited permissions.

The confirmation message should state that submission does not itself confirm acceptance. If validation fails, People Operations will contact the respondent. This is important because Google Forms cannot verify the Airtable workflow state before accepting a response.

Step 3: Create the System of Record

1. Create the People, Review Cycles, Reviews, Responses, Approvals, Messages, and Automation Events tables.
2. Add the field types and controlled values defined in the data structure section.
3. Make employee and manager relationships linked records rather than free-text names.
4. Add Created Time and Last Modified Time fields to each operational table.
5. Add an Autonumber field named Sequence to Reviews.
6. Create the Review ID formula shown in the configuration section.
7. Create a formula field named Initialization Ready. It should return READY only when the employee, manager, cycle, all required dates, and People Operations owner are present.
8. Create formula fields for days until each deadline and days in the current status.
9. Create filtered views that act as work queues rather than relying on users to filter the complete grid.
10. Configure Interfaces for People Operations, approvers, calibration leaders, and aggregate leadership reporting.

Recommended filtered views include:

• Preparation Incomplete
• Ready to Initialize
• Self Reminder Due
• Manager Reminder Due
• Calibration Due
• Approval Due
• Conversation Due
• Automation Retry Eligible
• Manual Recovery
• Recently Completed
• Cancelled and Archived

The Review ID is the human-readable identifier. The Airtable record ID is also retained inside Zapier mappings because it is the safest identifier for updating an exact Airtable record.

For the annual cycle, People Operations imports one review row for each active employee after validating reporting lines. A preparation view exposes duplicate employee-cycle combinations. The launch does not proceed until those duplicates are resolved.

Step 4: Connect the Tools

Create and test each Zapier connection separately. Interface labels can change between product versions, so configure the underlying trigger, filter, mapping, action, and update behavior rather than relying solely on a copied label.

For folder operations, store the manager’s parent folder ID in the People table. Zapier should search that known parent for the exact review folder name before creating anything. This is more reliable than searching the entire Drive by file name.

For form responses, construct a Source Event Key from the form identifier, source timestamp, verified respondent email, response type, and Review ID. Search Automation Events for this key before processing the submission.

For every destination action, update the source Airtable record with the returned identifier or a reliable completion timestamp. If an action succeeds but the update fails, the stored identifiers and deterministic names support reconciliation.

Step 5: Build the Core Automation

Automation A: Initialize a review

• Trigger: A review enters the Ready to Initialize view.
• Conditions: Initialization Ready equals READY, Automation Status equals Pending, and Status equals Planned.
• Actions: Set Automation Status to Running; find the parent manager folder; find an existing review folder by exact name; create it only if absent; store the folder ID and link; create the self-invitation message record; set Status to Self Review Open; set Automation Status to Ready.
• Fields updated: Folder ID, Folder Link, Status, Last Automation Run, Automation Status, Retry Count, and Error Message.
• Notification: A separate message workflow sends the invitation.
• Exception: Missing parent folder, duplicate review, permission error, or incomplete employee data moves the record to Manual Review.

The exact folder name is:

PR-2026-00047_CTS-042_Maya-Patel

Zapier first searches the expected parent folder. If it finds one exact match, it uses that folder. If it finds none, it creates one. If it finds multiple exact matches, it stops and creates a duplicate-folder exception.

Automation B: Send a queued message

• Trigger: A Messages record enters the Ready to Send view.
• Conditions: Message Key is present, State equals Queued, recipient is valid, and an identical Message Key does not already have Sent status.
• Actions: Change State to Sending; send through Gmail; store the returned provider identifier when available; record Sent At; change State to Sent.
• Fields updated: State, Sent At, Provider ID, Attempt Count, and Error.
• Notification: The Gmail message itself.
• Exception: If the send result is uncertain, leave the message in Sending and route it to reconciliation rather than sending it again automatically.

The Message Key combines the Review ID, message type, recipient, and planned sequence date. For example:

PR-2026-00047|SELF_INVITE|maya.patel@YOUR_COMPANY_DOMAIN|2026-09-01

Automation C: Process an employee response

• Trigger: New response from the Employee Self-Assessment form.
• Conditions: Review ID exists; workflow status permits self input; verified email matches the linked employee; all scores are integers from 1 to 5; attestation is checked; Source Event Key has not been processed.
• Actions: Create an Automation Event; find the review; find the canonical self-response; create or update the accepted response; store the source timestamp; update Self Submitted At; change Status to Manager Review; create the manager-invitation message.
• Fields updated: Self Submitted At, Status, Last Automation Run, accepted response fields, and source identifiers.
• Notification: Confirmation to the employee and assessment invitation to the manager.
• Exception: Identity mismatch, closed review, duplicate source event, missing score, or late resubmission goes to Manual Review.

Automation D: Process a manager response

• Trigger: New response from the Manager Assessment form.
• Conditions: Verified email matches the current manager or approved delegate; self-response is accepted; status is Manager Review or Changes Requested; required fields are complete.
• Actions: Record the manager response; supersede an earlier version when permitted; create a calibration document from the approved Google Docs template; save it to the review folder; store the document ID and link; set Calibration Status to Scheduled or Not Started; change Status to Calibration Pending.
• Fields updated: Manager Submitted At, Document Link, Calibration Status, Status, and automation fields.
• Notification: People Operations receives a calibration-ready notice.
• Exception: Missing folder, template failure, unauthorized respondent, or unsupported resubmission stops the transition.

Automation E: Finalize an approved review

• Trigger: Review Approval Status changes to Approved and Calibration Status equals Calibrated.
• Conditions: All required approval records have Approved decisions; no unresolved exception exists; manager response is accepted.
• Actions: Find or create the final document; store its identifier; create the manager conversation message; update Status to Finalization Pending.
• Fields updated: Final Document ID, Document Link, Finalization Started At, Status, and automation fields.
• Notification: Manager receives the conversation deadline and restricted document link.
• Exception: Missing approval evidence, document failure, or permission mismatch keeps the record in Approval Pending or Exception.

Automation F: Close the review

• Trigger: An acknowledgment response is accepted.
• Conditions: Respondent email matches the employee; status is Finalization Pending; final document and approved records exist.
• Actions: Store the acknowledgment; set Conversation Date; create a completion event; update Status to Completed; send completion confirmation to People Operations and the manager.
• Fields updated: Acknowledged At, Conversation Date, Completed At, Status, and Automation Status.
• Notification: Completion confirmation.
• Exception: An acknowledgment received before approval is retained but not accepted until People Operations reviews it.

Step 6: Add Approvals, Reminders, and Escalations

Approvals are sequential in relation to calibration. Standard employee reviews require calibration completion followed by one People Operations procedural approval. Reviews for designated senior leadership roles require two approval records after calibration: People Operations and the assigned executive sponsor.

When two approvals are required, the requests can be issued in parallel, but both must be approved before finalization. One Changes Requested decision returns the review to Changes Requested and closes the remaining pending approval requests as Superseded.

Approval requests contain no one-click approval links in email. The email directs the authorized approver to a restricted Airtable Interface. The approver records:

• Approved, Changes Requested, or On Hold
• Decision date
• Procedural comments
• Confirmation that required material was reviewed
• Delegate identity, if applicable

Scores do not determine approval routing. Threshold rules apply only to deadlines, missing fields, unresolved exceptions, and required approval counts.

A scheduled Zap runs each weekday morning in the company’s operating time zone:

1. Search Self Reminder Due and create messages for due-soon employees.
2. Search Manager Reminder Due and create messages for due-soon managers.
3. Search overdue views and calculate the correct sequence number.
4. Check Messages for the corresponding Message Key.
5. Create a message only if that key does not exist.
6. Escalate employee lateness to the manager after five calendar days.
7. Escalate manager lateness to People Operations after three calendar days.
8. Escalate approval lateness to the delegate after four calendar days.
9. Record the Last Reminder At and Reminder Count after confirmed delivery.

If an approver is unavailable, People Operations records an approved delegate in the Approval record. Reassignment requires the original approver, delegate, reason, actor, and timestamp to be retained. The approval itself must be entered by the delegate, not backdated by automation.

Step 7: Add Documents and File Management

Create a restricted Shared drive named People Operations, subject to the organization’s Google Workspace configuration. Inside it, use this structure:

People Operations
  Performance Reviews
    2026 Annual
      Manager Folder
        PR-2026-00047_CTS-042_Maya-Patel
          01_Calibration
          02_Final
          03_Supporting
    2026 Probationary
      Manager Folder
        Review Folder

The manager parent folder is provisioned and permissioned before the cycle. Only People Operations, the assigned manager, and authorized administrators receive access. Because Google Drive permissions are inherited, do not place manager folders under a parent that is broadly shared.

Zapier creates the employee review folder under the manager folder whose ID is stored in Airtable. If subfolders are needed, it creates or finds them using the same exact-name pattern.

Google Docs templates use stable placeholder labels for Review ID, employee, period, employee narratives, manager narratives, scores, calibration status, and approval details. The template is stored in a restricted Templates folder and edited only by People Operations administrators.

Use versioned names rather than replacing files silently:

PR-2026-00047_Calibration_v01
PR-2026-00047_Calibration_v02
PR-2026-00047_Final_Approved

Google Docs version history provides additional evidence, but the Airtable record should still point to the approved final file. Superseded document links remain in Automation Events or Notes.

Supporting documents must use the Review ID prefix. Managers should not upload unrelated personnel information. Large files, duplicate documents, and unsupported formats are reviewed manually. Retention and archival periods must follow the organization’s documented personnel-record policy and applicable legal requirements.

Step 8: Add Reporting and Operational Views

Airtable Interfaces provides operational dashboards using live data from the Reviews, Responses, Approvals, Messages, and Automation Events tables.

Calculated reporting fields include days in current status, days overdue, total cycle duration, employee submission timeliness, manager submission timeliness, calibration duration, approval duration, and whether manual intervention occurred.

Leadership receives aggregate counts and completion rates by department and status. Narrative text and employee-level score details are excluded unless a leader has a documented business need and appropriate permission.

The dashboard owner is the People Operations lead. Airtable Interfaces reflects saved base data without a separate warehouse refresh. If data is exported to another reporting tool later, the refresh schedule and field-level exclusions must be documented.

Step 9: Add Security and Governance Controls

• Least privilege: Full Airtable base access is limited to People Operations administrators and the approved automation connection.
• Manager access: Managers receive only their restricted Drive folders and required input links. They do not browse the complete Airtable base.
• Employee access: Employees use authenticated Forms and receive only material approved for their review conversation.
• Approver access: Approvers use a filtered Interface that shows only assigned approval records.
• Sensitive fields: Narrative, calibration comments, exception notes, and approval comments are excluded from general leadership dashboards.
• Shared links: Public or organization-wide links are prohibited for review folders and documents.
• Credentials: OAuth connections and AI credentials remain in Zapier connection storage, not in Airtable.
• Activity logs: Airtable record history, Google Workspace audit logs, Zapier task history, and Automation Events are reviewed according to role.
• Former employees: Account suspension and access removal are part of offboarding. Manager folder access is also reviewed when reporting lines change.
• Retention: Forms, Airtable records, Gmail messages, and Drive files follow a documented retention schedule.
• Backups: Perform periodic controlled exports or use approved backup capabilities. Test that records and document identifiers can be restored.
• Privacy: Collect only information necessary for the review process. Do not repurpose review narratives for unrelated analytics without review.
• AI restrictions: AI input is de-identified where practical, excludes prohibited personal data, and is submitted only after explicit eligibility review.
• Human control: Automation cannot approve a review, alter a score, make a legal conclusion, or recommend termination, promotion, compensation, or discipline.

Applicable employment, privacy, retention, works council, and records-management requirements vary by jurisdiction. Clearbrook’s representative design requires legal and security review before production use.

Step 10: Deploy and Test

1. Build the Airtable schema, Forms, Drive structure, templates, and Zaps in the test environment.
2. Use synthetic employee records and narratives for initial testing.
3. Run each workflow one step at a time with destination actions disabled or directed to test locations.
4. Confirm field mappings using Zapier test data, especially email, Review ID, score fields, dates, and folder IDs.
5. Complete the full testing checklist in this article.
6. Conduct user acceptance testing with a People Operations coordinator, manager, employee, approver, and technical administrator.
7. Run a six-person pilot across two departments before launching the annual cycle.
8. Review message clarity, folder permissions, exception handling, and dashboard totals after the pilot.
9. Freeze field names and template placeholders before production activation.
10. Import the production review records in a paused state.
11. Activate initialization workflows for a small batch and reconcile every result.
12. Expand to the remaining population after the first batch succeeds.
13. Publish a short user guide covering forms, deadlines, resubmissions, support, and privacy.
14. Assign launch-week monitoring to the primary and backup administrators.

The rollback procedure pauses all Zaps, changes active reviews to On Hold, disables scheduled reminder workflows, and directs participants to People Operations. Existing Airtable records and Drive files remain intact for diagnosis.

Code and Configuration

The core workflow does not require custom application code. Airtable formulas, Google Forms settings, and native Zapier actions provide the required record creation, validation, branching, document generation, email delivery, and status updates.

Use configuration placeholders rather than real production identifiers:

AIRTABLE_BASE_ID=YOUR_AIRTABLE_BASE_ID
REVIEWS_TABLE=Reviews
RESPONSES_TABLE=Responses
APPROVALS_TABLE=Approvals
MESSAGES_TABLE=Messages
EVENTS_TABLE=Automation Events
GOOGLE_SELF_FORM_ID=YOUR_SELF_FORM_ID
GOOGLE_MANAGER_FORM_ID=YOUR_MANAGER_FORM_ID
GOOGLE_ACK_FORM_ID=YOUR_ACK_FORM_ID
GOOGLE_TEMPLATE_ID=YOUR_TEMPLATE_ID
GOOGLE_REVIEW_ROOT_FOLDER_ID=YOUR_FOLDER_ID
PEOPLE_OPS_EMAIL=YOUR_EMAIL_ADDRESS

Airtable formulas

Place the following formula in the Review ID field of the Reviews table. It combines the creation year and Autonumber sequence:

"PR-" & DATETIME_FORMAT({Created Date}, "YYYY") & "-" & RIGHT("00000" & {Sequence}, 5)

Use the following formula for Initialization Ready:

IF(
  AND(
    {Employee},
    {Manager},
    {Cycle},
    {People Ops Owner},
    {Self Due},
    {Manager Due},
    {Calibration Due},
    {Final Due},
    {Status} = "Planned"
  ),
  "READY",
  "INCOMPLETE"
)

Calculate the number of days until the self-assessment deadline:

IF(
  {Self Due},
  DATETIME_DIFF({Self Due}, TODAY(), "days")
)

Calculate the competency average in the Responses table only when all scores are present:

IF(
  AND(
    {Results Focus},
    {Quality},
    {Collaboration},
    {Communication},
    {Client Service},
    {Development}
  ),
  ROUND(
    AVERAGE(
      {Results Focus},
      {Quality},
      {Collaboration},
      {Communication},
      {Client Service},
      {Development}
    ),
    2
  )
)

Flag a self-reminder candidate:

IF(
  AND(
    {Status} = "Self Review Open",
    {Self Due},
    DATETIME_DIFF({Self Due}, TODAY(), "days") <= 3,
    OR(
      {Last Reminder At} = BLANK(),
      DATETIME_DIFF(NOW(), {Last Reminder At}, "hours") >= 24
    )
  ),
  "REMINDER DUE"
)

Google Forms prefilled links

Use the Google Forms function that generates a prefilled link. Do not guess the form’s field entry identifier. Generate it from the completed form, then store the resulting template in Zapier.

https://docs.google.com/forms/d/e/YOUR_FORM_ID/viewform?usp=pp_url&entry.YOUR_REVIEW_FIELD_ID={{Review ID}}

The Review ID can remain visible so the participant can confirm it. Hiding a prefilled value does not make it secure. The verified email and Airtable relationship provide the identity check.

Core Zap configuration

Gmail invitation template

Subject: Action required: self-assessment for {{Review ID}}

Hello {{Employee First Name}},

Your {{Cycle Name}} self-assessment is ready.

Review ID: {{Review ID}}
Due date: {{Self Due}}
Form: {{Prefilled Self Form URL}}

Use your company account to open the form. Do not forward the link. If the employee name or review period is incorrect, contact People Operations before submitting.

People Operations
{{People Ops Email}}

Optional AI output validator

The optional AI workflow uses a Code by Zapier JavaScript step after the AI action. The code validates the response structure before any text is written to the manager-visible summary field.

/*
Input fields configured in Code by Zapier:
  raw_output: Raw JSON returned by the AI action
  review_id: Expected Airtable Review ID
  max_items: Optional maximum items per array, default 8

This step makes no external requests and requires no credentials.
*/

const raw = String(inputData.raw_output || "").trim();
const expectedReviewId = String(inputData.review_id || "").trim();
const configuredMax = Number(inputData.max_items || 8);
const maxItems = Number.isFinite(configuredMax) && configuredMax > 0
  ? Math.min(configuredMax, 20)
  : 8;

const errors = [];
const allowedSourceRefs = new Set([
  "employee.accomplishments",
  "employee.challenges",
  "employee.development_goals",
  "employee.evidence",
  "manager.performance_evidence",
  "manager.achievements",
  "manager.development_priorities",
  "manager.support_commitments",
  "manager.discussion_points"
]);

function validateStringArray(value, fieldName) {
  if (!Array.isArray(value)) {
    errors.push(`${fieldName} must be an array.`);
    return [];
  }

  if (value.length > maxItems) {
    errors.push(`${fieldName} contains more than ${maxItems} items.`);
  }

  return value.slice(0, maxItems).map((item, index) => {
    if (typeof item !== "string" || !item.trim()) {
      errors.push(`${fieldName}[${index}] must be a non-empty string.`);
      return "";
    }

    if (item.length > 600) {
      errors.push(`${fieldName}[${index}] exceeds 600 characters.`);
    }

    return item.trim();
  }).filter(Boolean);
}

function validateSummaryPoints(value) {
  if (!Array.isArray(value)) {
    errors.push("summary_points must be an array.");
    return [];
  }

  return value.slice(0, maxItems).map((item, index) => {
    if (!item || typeof item !== "object" || Array.isArray(item)) {
      errors.push(`summary_points[${index}] must be an object.`);
      return null;
    }

    const theme = String(item.theme || "").trim();
    const statement = String(item.statement || "").trim();
    const sourceRefs = Array.isArray(item.source_refs)
      ? item.source_refs.map(value => String(value).trim())
      : [];

    if (!theme) {
      errors.push(`summary_points[${index}].theme is required.`);
    }

    if (!statement) {
      errors.push(`summary_points[${index}].statement is required.`);
    }

    if (statement.length > 800) {
      errors.push(`summary_points[${index}].statement exceeds 800 characters.`);
    }

    if (sourceRefs.length === 0) {
      errors.push(`summary_points[${index}] requires at least one source reference.`);
    }

    sourceRefs.forEach(sourceRef => {
      if (!allowedSourceRefs.has(sourceRef)) {
        errors.push(
          `summary_points[${index}] contains an invalid source reference: ${sourceRef}`
        );
      }
    });

    return {
      theme,
      statement,
      source_refs: sourceRefs
    };
  }).filter(Boolean);
}

let result;

try {
  if (!raw) {
    throw new Error("AI output is empty.");
  }

  if (!expectedReviewId) {
    throw new Error("Expected review_id is missing.");
  }

  const parsed = JSON.parse(raw);

  if (!parsed || typeof parsed !== "object" || Array.isArray(parsed)) {
    throw new Error("AI output must be one JSON object.");
  }

  if (String(parsed.review_id || "").trim() !== expectedReviewId) {
    errors.push("The returned review_id does not match the source review.");
  }

  const normalized = {
    review_id: expectedReviewId,
    summary_points: validateSummaryPoints(parsed.summary_points),
    agreements: validateStringArray(parsed.agreements, "agreements"),
    differences: validateStringArray(parsed.differences, "differences"),
    development_themes: validateStringArray(
      parsed.development_themes,
      "development_themes"
    ),
    missing_information: validateStringArray(
      parsed.missing_information,
      "missing_information"
    ),
    needs_human_review: parsed.needs_human_review === true,
    safety_flags: validateStringArray(parsed.safety_flags, "safety_flags")
  };

  const decisionPattern =
    /\b(terminate|termination|fire|dismiss|promote|promotion|demote|discipline|salary|bonus|compensation decision)\b/i;

  if (decisionPattern.test(JSON.stringify(normalized))) {
    errors.push("Output contains a prohibited employment-decision term.");
  }

  if (normalized.needs_human_review !== true) {
    errors.push("needs_human_review must be true.");
  }

  result = {
    valid: errors.length === 0,
    review_id: expectedReviewId,
    normalized_json: JSON.stringify(normalized),
    error_count: errors.length,
    errors: errors.join(" | ")
  };
} catch (error) {
  result = {
    valid: false,
    review_id: expectedReviewId,
    normalized_json: "",
    error_count: errors.length + 1,
    errors: [error.message, ...errors].join(" | ")
  };
}

console.log(
  JSON.stringify({
    review_id: result.review_id,
    valid: result.valid,
    error_count: result.error_count
  })
);

output = result;

The code has no dependencies, credentials, or network calls. Configure a Zapier Path after the code step. Continue to the AI Staging field only when valid is true. Otherwise, set AI Status to Manual Review and store the validation errors.

Test the code with valid JSON, malformed JSON, a mismatched Review ID, missing arrays, invalid source references, excessive output, and prohibited decision language. Review the Code step output and Zapier task log when troubleshooting.

Failure Handling and Operational Reliability

The automation uses status gates and queue records so that one failed external action does not falsely advance a review. Technical status is separate from business status.

The Airtable Manual Recovery view acts as a dead-letter queue. It contains records with Automation Status equal to Error or Manual Review, Retry Count of three or more, or a message left in Sending beyond the agreed reconciliation interval.

Automatic retries are limited to actions that are safely repeatable. Folder and document creation use find-before-create logic. Form events use a Source Event Key. Messages do not use blind retries after an uncertain Gmail response.

Every weekday, the administrator compares active review counts, accepted response counts, pending message counts, and current Zapier errors. At least weekly during the annual cycle, People Operations reconciles Completed reviews against required approvals, final documents, and acknowledgments.

A Complete Example

The following representative record follows Maya Patel, employee ID CTS-042, through Clearbrook’s 2026 annual cycle. Her manager is Elias Grant, and the People Operations owner is Rina Cole.

1. People Operations imports a planned review linked to Maya, Elias, and the 2026 Annual cycle.
2. Airtable assigns sequence 47 and calculates Review ID PR-2026-00047.
3. The initialization formula returns READY because all required people and dates are present.
4. Zapier finds Elias’s restricted parent folder and creates PR-2026-00047_CTS-042_Maya-Patel. Google Drive returns the folder ID, which is written to Airtable.
5. A Messages record is created with a unique self-invitation key. Gmail sends Maya the self-assessment link and records the send time.
6. Maya signs in with her company account. Google Forms records her verified email, Review ID, six competency scores, accomplishments, challenges, development goals, evidence, and attestation.
7. Zapier creates a Source Event Key from the form, submission timestamp, verified email, response type, and Review ID.
8. Airtable confirms that the Review ID exists, the status is Self Review Open, and Maya’s verified email matches the linked employee.
9. The self-response is accepted. The review changes to Manager Review, and Elias receives the manager assessment link.
10. Elias submits scores of 4, 4, 3, 4, 4, and 3 with supporting examples and development priorities. The manager email matches the assigned manager.
11. Airtable calculates a manager competency average of 3.67. The calculation supports comparison but does not determine a rating or workflow decision.
12. Zapier creates the calibration document from the approved Google Docs template and saves it in the review folder. Its document ID and link are stored in Airtable.
13. The review moves to Calibration Pending. The calibration group compares evidence, discusses consistency, and requests clarification on one development statement.
14. People Operations selects Changes Requested and records the reason. Elias receives a new request with a two-business-day deadline.
15. Elias submits a revised manager assessment. The prior response remains linked as superseded, and a version-two calibration document is created.
16. The calibration group records Calibrated. Zapier creates the required People Operations approval record.
17. Rina reviews completeness, confirms the calibration evidence, and selects Approved in the restricted Interface. No employment decision is delegated to the workflow.
18. Zapier creates the final document, stores its external identifier, moves the review to Finalization Pending, and notifies Elias of the conversation deadline.
19. Elias conducts the review conversation. Maya submits the acknowledgment form using her company account.
20. Zapier validates the acknowledgment, confirms the final document and approval, records the conversation date, and changes the review to Completed.
21. The review appears in Recently Completed and is removed from all overdue and action queues.

If the final document action had failed, the review would have remained Approval Pending or entered Exception. The manager would not have received the conversation message until the document identifier was successfully recorded.

Implementation Cost

All amounts below are representative budgeting assumptions, not quoted vendor prices or verified client costs. Actual software costs depend on current vendor pricing, user counts, task volume, storage, security requirements, taxes, and contract terms.

The internal labour estimate is an opportunity-cost assumption, even if no external invoice is issued. The optional professional implementation amount should not be added automatically if internal staff perform the same configuration work.

Estimated Time and Cost Savings

The calculation uses the following representative assumptions:

• 108 review cycles per year, averaging 9 per month
• 115 minutes of current People Operations administration per review
• 32 minutes of administration per review after automation
• 12 percent exception rate
• 25 minutes of manual handling per exception
• 2 hours of monthly system maintenance
• $42 loaded hourly labour cost for administrative work
• $125 recurring monthly core software allowance
• $6,600 optional professional implementation cost

Current monthly labour hours: Monthly volume × current minutes per record ÷ 60

Current calculation: 9 × 115 ÷ 60 = 17.25 hours

New monthly labour hours: Monthly volume × new minutes per record ÷ 60, plus exception handling and maintenance

New calculation: 9 × 32 ÷ 60 + 9 × 12% × 25 ÷ 60 + 2 = 4.80 + 0.45 + 2.00 = 7.25 hours

Monthly hours recovered: Current monthly labour hours minus new monthly labour hours

Hours calculation: 17.25 – 7.25 = 10.00 hours

Estimated monthly labour value: Monthly hours recovered × loaded hourly labour cost

Labour value calculation: 10.00 × $42 = $420

Net estimated monthly value: Monthly labour value minus recurring tool costs

Net value calculation: $420 – $125 = $295

Estimated payback period: One-time implementation cost ÷ net estimated monthly value

Professional implementation payback calculation: $6,600 ÷ $295 = approximately 22.4 months

Internal labour-value payback calculation: $2,700 ÷ $295 = approximately 9.2 months

Recovered time does not automatically reduce payroll. It can create additional People Operations capacity, reduce overtime during the annual cycle, improve turnaround, support higher review volume, and reduce dependency on one coordinator.

The average monthly calculation also understates the operational benefit during the concentrated annual cycle. The primary benefit may be avoiding a severe eight-week administrative peak rather than producing equal savings every month.

Non-financial benefits include clearer ownership, fewer follow-up emails, fewer incomplete submissions, more consistent scoring fields, better document control, improved auditability, faster leadership reporting, and a more predictable experience for employees and managers.

Readers should replace review volume, handling time, exception rate, labour cost, software allowance, implementation cost, maintenance effort, and cycle concentration with their own measured figures.

Adding AI to the Automation

AI should be added only after the deterministic workflow, permissions, validation, reminders, approvals, and failure handling operate reliably.

Potential AI uses include summarizing approved narratives, grouping development themes, identifying missing examples, comparing employee and manager narratives, and producing a source-referenced draft for manager review.

AI is not needed for required fields, date calculations, score validation, email matching, duplicate prevention, approval counts, permissions, reminders, or deadline escalation. Those tasks are more reliable as formulas, lookup rules, and workflow conditions.

The core automation provides status visibility, document control, reminders, reporting, and audit evidence. AI provides optional assistance with reading and organizing unstructured text. It does not replace the core process.

The Recommended AI Enhancement

The recommended enhancement creates a de-identified, source-referenced draft summary after the employee and manager responses are accepted but before final calibration approval.

• Trigger: Manager response accepted, AI Eligible equals Yes, AI Status equals Not Started, and no prohibited-data flag exists.
• AI input: Review ID, competency labels and scores, selected employee narratives, selected manager narratives, and field-level source names.
• Excluded input: Names, email addresses, dates of birth, health information, disability information, leave details, family information, protected characteristics, salary, investigation details, and unrelated personnel records.
• Expected output: JSON containing themes, agreements, differences, development topics, missing information, source references, and safety flags.
• Validation: The JavaScript validator checks JSON structure, Review ID, lengths, source references, and prohibited decision language.
• Record update: Valid output is written to an AI Staging field, not directly into the approved review document.
• Human review: The manager edits, accepts, or discards the draft before it can be included in calibration material.
• Low-confidence handling: Missing source references, incomplete output, safety flags, or malformed JSON routes the record to manual preparation.
• Failure behavior: The review continues without AI. No workflow deadline depends on an AI response.

Use the following system instruction:

You assist a manager by organizing performance review narratives.

You must not make or recommend employment decisions. Do not recommend promotion, termination, discipline, compensation changes, legal conclusions, or final ratings.

Use only the supplied source text. Do not infer protected characteristics, health status, intent, personality, or facts not stated in the input.

Every summary point must cite one or more allowed source field names. Distinguish employee statements from manager statements. Identify differences without deciding which person is correct.

Return only valid JSON matching the requested schema. Set needs_human_review to true in every response. If the input contains potentially sensitive personal information or insufficient evidence, add a safety flag and describe the gap without repeating unnecessary sensitive details.

Use this reusable user prompt:

Prepare a draft performance review summary for human manager review.

Review ID:
{{Review ID}}

Competency scores:
{{Competency Score JSON}}

Employee sources:
employee.accomplishments:
{{Employee Accomplishments}}

employee.challenges:
{{Employee Challenges}}

employee.development_goals:
{{Employee Development Goals}}

employee.evidence:
{{Employee Evidence}}

Manager sources:
manager.performance_evidence:
{{Manager Performance Evidence}}

manager.achievements:
{{Manager Achievements}}

manager.development_priorities:
{{Manager Development Priorities}}

manager.support_commitments:
{{Manager Support Commitments}}

manager.discussion_points:
{{Manager Discussion Points}}

Requirements:
1. Summarize recurring work themes using neutral language.
2. Identify explicit areas of agreement.
3. Identify differences in emphasis without resolving them.
4. List development themes supported by source text.
5. List missing or unclear information.
6. Cite allowed source field names for every summary point.
7. Do not calculate a final rating.
8. Do not recommend any employment action.
9. Return only JSON matching the schema.

The required JSON structure is:

{
  "review_id": "PR-2026-00047",
  "summary_points": [
    {
      "theme": "Delivery",
      "statement": "Both sources describe consistent completion of scheduled client work.",
      "source_refs": [
        "employee.accomplishments",
        "manager.performance_evidence"
      ]
    }
  ],
  "agreements": [
    "Both sources identify client communication as a strength."
  ],
  "differences": [
    "The employee emphasizes workload planning while the manager emphasizes prioritization."
  ],
  "development_themes": [
    "Use clearer prioritization checkpoints for competing assignments."
  ],
  "missing_information": [
    "The submitted evidence does not include a measurable example for the development goal."
  ],
  "needs_human_review": true,
  "safety_flags": []
}

Store the raw model response, validation outcome, model or connector identifier when available, prompt version, run time, and human reviewer. Do not store secret credentials or unnecessary personal information in the log.

Benefits of the AI Enhancement

• Reduces the time managers spend reorganizing long narrative responses.
• Presents employee and manager viewpoints in a consistent draft structure.
• Highlights missing examples before calibration.
• Uses source references so a manager can verify each summary point.
• Supports theme reporting after human review and appropriate aggregation.
• Allows the manager to focus on evidence quality and the review conversation rather than initial formatting.

These benefits are specifically related to unstructured text. Deadline control, ownership, restricted storage, approval evidence, completion reporting, and reminder savings come from the core automation, not from AI.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The following assumptions are separate from the core administrative savings:

• 108 reviews per year
• 14 minutes of manager synthesis without AI
• 6 minutes to review and edit an AI-assisted draft
• 15 percent of drafts need an additional 4 minutes of correction
• 3 percent of AI runs fail and require the full 14-minute manual process
• $65 loaded manager labour cost
• $120 annual AI usage allowance

Initial synthesis time recovered: 108 × 8 minutes ÷ 60 = 14.40 hours per year

Expected correction time: 108 × 15% × 4 minutes ÷ 60 = 1.08 hours per year

Expected failure fallback time: 108 × 3% × 14 minutes ÷ 60 = 0.76 hours per year

Net additional capacity: 14.40 – 1.08 – 0.76 = 12.56 hours per year

Estimated labour value: 12.56 × $65 = approximately $816 per year

Estimated value after AI usage allowance: $816 – $120 = approximately $696 per year

This estimate assumes every AI draft receives human review. It does not assume that AI eliminates errors, replaces managers, or improves the quality of an employment decision.

Testing Checklist

Use synthetic sample data before processing real employee information.

Ongoing Maintenance

The People Operations lead is the primary business owner. The technical administrator is the primary integration owner. Each has a named backup with sufficient access and documentation.

Changes to Airtable field names, form questions, template placeholders, Drive structure, Gmail sender configuration, or Zapier mappings require regression testing. Documentation should include a field dictionary, workflow inventory, connection owner list, recovery procedure, and change log.

When to Move to Dedicated Software

The Airtable and Zapier implementation can remain appropriate while the process is understandable, transaction volume is moderate, permissions can be enforced, and maintenance remains controlled.

Clearbrook should reassess dedicated performance management or human resources software if it encounters several of the following conditions:

• Review volume increases substantially or multiple review programs run concurrently.
• Complex matrix-management or multi-rater reviews become standard.
• Employees require a comprehensive self-service portal.
• Advanced role, field, location, or legal-entity permissions are required.
• Formal regulatory, audit, works council, or data-residency requirements exceed the selected tools’ governance model.
• Single sign-on, automated user provisioning, or identity lifecycle requirements become mandatory.
• Calibration requires advanced comparison, normalization, or succession-planning features.
• Exception rates or automation maintenance become excessive.
• Task volume and record growth produce unacceptable software cost or performance.
• Mobile or offline review access becomes a requirement.
• Formal employee acknowledgment, electronic signature, or immutable records are required.
• Leadership requires advanced workforce analytics across performance, compensation, learning, and succession data.
• The business requires contractual vendor support with defined response and recovery commitments.
• Security risk can no longer be addressed through restricted forms, Interfaces, folders, and documented procedures.

Migration is not automatically necessary when the employee count grows. The decision should consider workflow complexity, governance, support burden, integration requirements, and total operating cost together.

Implementation Checklist

• Confirm review types, participants, deadlines, competencies, calibration rules, and completion criteria.
• Select Airtable, Zapier, Google Forms, Google Drive, Google Docs, and Gmail roles.
• Verify required subscription features and task capacity.
• Create production and test accounts.
• Assign primary and backup owners.
• Configure least-privilege permissions.
• Create the People, Review Cycles, Reviews, Responses, Approvals, Messages, and Automation Events tables.
• Define Review IDs, relationship fields, statuses, dates, errors, and external identifiers.
• Build authenticated employee, manager, and acknowledgment intake forms.
• Configure validation, privacy notices, conditional questions, and confirmation messages.
• Create restricted Drive folders and manager parent-folder identifiers.
• Create controlled Google Docs templates and naming conventions.
• Connect Airtable, Zapier, Drive, Docs, Forms, and Gmail.
• Document every field mapping and returned identifier.
• Build review initialization and owner-assignment workflows.
• Build employee and manager response workflows.
• Implement duplicate-event and duplicate-record checks.
• Build approval records and restricted approval interfaces.
• Configure reminders, escalation timing, delegation, and reassignment.
• Implement message keys and uncertain-send reconciliation.
• Create calibration and final document workflows.
• Create operational, exception, overdue, completion, and leadership reporting views.
• Configure automation errors, retry limits, manual recovery, and reconciliation.
• Review retention, privacy, access, backup, and audit requirements.
• Test normal, invalid, duplicate, failed, overdue, rejected, and reassigned scenarios.
• Run user acceptance testing with synthetic data.
• Complete a limited pilot before the annual launch.
• Document deployment, monitoring, support, rollback, and change control.
• Replace representative software, labour, maintenance, and savings assumptions with measured figures.
• Add AI only after the core workflow is reliable.
• Validate AI input restrictions, output schema, human review, failure fallback, and cost monitoring.
• Assign an ongoing maintenance owner and backup owner.
• Define the volume, governance, security, complexity, and support criteria that would trigger a dedicated-software review.