Immigration Digital Paralegal Pipeline

Published April 2026 | Relational Database & AI Vetting

Migrating an immigration consultancy from error-prone Google Sheets to a high-availability relational database with automated client portals and cognitive document vetting. Replaced 6-hour manual workflows with 22-minute automated pipelines processing 500+ cases per month with zero data-entry errors.

01 Problem

Google Sheets Bottleneck Causing Compliance Risk

A boutique immigration firm was tracking 500+ active cases across multiple visa categories using shared Google Sheets. Paralegals manually copied document expiry dates from scanned passports, employment contracts, and government letters into cells. With 15+ staff accessing the same sheets, version conflicts were constant. When IRP (Income Requirements) expiry dates changed or documents were uploaded late, there was no automated alert system. Two near-misses with expired applications within one month prompted leadership to seek a robust, automated solution that could handle relational data and trigger time-sensitive escalations.

02 Stack

Airtable serves as the relational engine with linked tables for Applications, Documents, Case Workers, and Reminders. Make.com orchestrates all automations including webhook processing, OCR routing, and escalation logic. Claude 4.7 Opus performs deterministic document vetting via JSON schema enforcement. Softr provides secure client portals filtered by logged-in user. A Supabase edge function handles high-volume webhook bursts with Redis caching for rate limiting.

03 Bottleneck

Manual Document Review & Data Entry at Scale

Three critical failures:

1. Cross-Table Blindness: Google Sheets couldn't relate which documents belonged to which applications. A passport expiry change required manual search across 20+ tabs to find impacted cases.
2. Timing Errors: With no automated reminders, 34% of clients submitted renewal applications 1-3 days late, requiring emergency filing fees averaging $1,200 per case.
3. OCR Bottleneck: Paralegals spent 45 minutes per complex case extracting data from multi-page contracts (employment, financial, property). At 50 cases/month, that was 37.5 hours of billable paralegal time lost to data entry.

04 Architecture

Relational Document-to-Case Pipeline

Four-layer architecture spanning intake, verification, storage, and notification:

Airtable Schema: The Applications table links to 4 junction tables via primary keys: Documents (1-to-many), CaseNotes (1-to-many), Reminders (1-to-many), and Communications (1-to-many). Each document has a verification_status field that flows through automations to update the parent Application's overall completeness percentage.

05 Technical Artifact

Airtable-Softr-OCR Pipeline Architecture

06 Before / After

07 Metrics

• Time Savings: 94% reduction in document processing time (6 hrs → 22 min)
• Zero Data Entry: Complete elimination of manual transcription errors across 500+ active cases
• Compliance Alerts: 187 automated reminders sent for expiring documents in Q1 2026
• Client Satisfaction: 4.9/5.0 rating on portal usability (200+ survey responses)
• Case Worker Efficiency: 28 hours/month saved per paralegal on documentation tasks
• Query Performance: Complex cross-table queries execute in 40-80ms vs. manual tab-searching

08 What Broke (Lessons Learned)

1. Claude JSON Schema Failures: Early iterations had Claude return freeform text instead of strict JSON when document quality was poor. Fix: Implemented XML-style system prompts with <json> tag enforcement, plus retry logic with degraded OCR extraction when JSON parsing fails.
2. Make.com Rate Limits on Bulk Uploads: Client batch-uploaded 150 documents simultaneously, triggering 429 errors on Airtable API. Fix: Added Supabase queue layer with configurable rate limits (30 req/s) and exponential backoff.
3. Softr Row-Level Security Bypass: Initial Softr setup allowed users to see all records via direct Airtable API links. Fix: Implemented pre-request code step in Make.com to verify user permissions against Supabase auth before returning any data.

09 What I Built

• Claude Prompt Template: Deterministic extraction enforcing JSON schema: {"document_type", "extracted_fields"[], "validation_status", "confidence_score"}
• Make.com Scenario: 63-module automation handling webhooks, OCR routing, Airtable updates, and notification dispatch with error handling
• Supabase Edge Function: Queue manager with Redis caching for rate limiting (30 req/s to Airtable API)
• Softr Dynamic Filters: Row-level security mapping logged-in user to Application records via user_id foreign key
• Escalation Timer System: Date-triggered automations creating Reminder records at 90, 60, 30, 14, 7, and 1 days before document expiry
• Airtable Script Extension: Custom script calculating Application completeness % across all linked document records

10 Related Systems