GovFit Platform Airtable Integration

Overview

This document describes the integration of the GovFit Platform pre-alpha Airtable base into the CivStart application. The system now supports syncing data from two different Airtable bases:

1. CivStart GovIntel (original base) - appqsID9a16IvZZKy
2. GovFit Platform (new base) - appRqBC1cwvzK5DMs

What Was Changed

1. Database Schema Updates

New Migration: 20251117000000_add_govfit_fields

Added fields to support GovFit Platform data structure:

Signals Table

• signal_category - Array field for signal categories (GovFit's version of solution_category)

Startup Contacts Table

Contact Information:

• primary_contact - Primary contact person name
• role_title - Role or title of the primary contact

Business Profile:

• icp_description - Ideal Customer Profile description
• target_region - Array of target regions
• procurement_readiness - Array of procurement readiness indicators
• funding_stage - Current funding stage
• startup_notes - General notes about the startup

Metrics:

• total_assigned_signals - Count of signals assigned to startup
• mqls_this_month - Marketing Qualified Leads this month

Value Propositions:

• key_differentiators - Key differentiators and unique value propositions
• use_cases - Use cases and applications
• proof_and_outcomes - Proof points and measurable outcomes
• tech_compliance - Technical specifications and compliance information
• solution_categories - Array of solution categories
• qualification_questions - Questions to qualify opportunities

2. Environment Variables

Added new environment variables to all .env files:

# GovFit Platform Base (optional)
AIRTABLE_API_KEY_GOVFIT=pat3hVIvENrjOk4p9.0c66f4ea858e51c54b02f602a166e476aaf607292a92a6b983231cfc89e5c0f0
AIRTABLE_BASE_ID_GOVFIT=appRqBC1cwvzK5DMs

3. Seeding Script Updates

Updated scripts/full-reset-and-seed.ts to support multiple Airtable bases:

• Added --airtable-base CLI flag to switch between bases
• Automatically selects correct API key and base ID
• Populates both solutionCategory and signalCategory fields for compatibility
• Updated help documentation

4. New Tools

Created scripts/analyze-airtable-schema.ts - A utility to compare schemas between different Airtable bases and identify field differences.

How to Use

Running Schema Analysis

To compare the two Airtable bases and see differences:

pnpm tsx scripts/analyze-airtable-schema.ts

This will output:

• Tables that exist in only one base
• Fields that exist in only one base
• Field type mismatches
• Recommendations for database migrations

Applying Database Migration

Option 1: Using SQL Script (Recommended for Automation)

# Local environment
cat /Users/pratapsingh/Documents/Personal/CivStart/prisma/migrations/20251117000000_add_govfit_fields/migration.sql | docker exec -i civstart-postgres-dev psql -U civstart -d civstart_dev

# Dev environment (via SSH or ECS task)
psql $DATABASE_URL -f prisma/migrations/20251117000000_add_govfit_fields/migration.sql

Option 2: Using Prisma (For Development)

# After pulling latest code, regenerate Prisma client
pnpm db:generate

# Migration is already applied, but if you need to reapply:
# cat prisma/migrations/20251117000000_add_govfit_fields/migration.sql | docker exec -i civstart-postgres-dev psql -U civstart -d civstart_dev

Seeding with GovFit Platform Data

Local Testing

# Use GovFit Platform base
pnpm tsx scripts/full-reset-and-seed.ts --airtable-base=govfit --startups=10

# Use default CivStart GovIntel base
pnpm tsx scripts/full-reset-and-seed.ts --startups=10

# Dry run to test without making changes
pnpm tsx scripts/full-reset-and-seed.ts --airtable-base=govfit --dry-run

Dev Environment

# Update .env.dev with GovFit credentials first
# Then run:
pnpm reset:dev --airtable-base=govfit

Staging Environment

# Update .env.staging with GovFit credentials first
# Then run:
pnpm reset:staging --airtable-base=govfit

Environment Setup

Local Development (.env.local)

# CivStart GovIntel Base (default)
AIRTABLE_API_KEY=patJhyLVUEPfOYRvU.6103c4672ef7274c19e0091801dc37a3dc5e4cfe5907116f728bd4b9dfe9ca2a
AIRTABLE_BASE_ID=appqsID9a16IvZZKy

# GovFit Platform Base
AIRTABLE_API_KEY_GOVFIT=pat3hVIvENrjOk4p9.0c66f4ea858e51c54b02f602a166e476aaf607292a92a6b983231cfc89e5c0f0
AIRTABLE_BASE_ID_GOVFIT=appRqBC1cwvzK5DMs

Dev Environment (.env.dev)

TODO: Add GovFit credentials to .env.dev after local testing is successful:

# Add these to .env.dev
AIRTABLE_API_KEY_GOVFIT=<your_govfit_api_key>
AIRTABLE_BASE_ID_GOVFIT=appRqBC1cwvzK5DMs

Staging Environment (.env.staging)

TODO: Add GovFit credentials to .env.staging after dev testing is successful:

# Add these to .env.staging
AIRTABLE_API_KEY_GOVFIT=<your_govfit_api_key>
AIRTABLE_BASE_ID_GOVFIT=appRqBC1cwvzK5DMs

Schema Differences Summary

Based on the schema analysis, here are the key differences:

Tables

• ✅ Both bases have the same 6 tables
• ✅ No new tables needed

Fields Changed: 31 Total

Interactions Table (3 new fields)

• Signals Captured (count) - derived field
• Connections (lookup) - derived field
• MQLs per signal (formula) - derived field

Signals Table (1 field difference)

• ❌ "Solution Category" removed in GovFit
• ✅ "Signal Category" added in GovFit (same purpose, new name)

Connections Table (7 new lookup fields)

• Most are derived/lookup fields from related tables
• Optional for basic functionality

Startup Contacts Table (15 new fields) - MOST IMPORTANT

• Primary Contact, Role/Title
• ICP Description, Target Region, Procurement Readiness
• Funding Stage, Notes
• Total Assigned Signals, MQLs This Month
• Key Differentiators, Use Cases, Proof and Outcomes
• Tech/Compliance, Solution Categories, Qualification Questions

Testing Checklist

Phase 1: Local Testing ✅

• Schema analysis completed
• Database migration created
• Migration applied to local database
• Prisma client regenerated
• Seeding script updated
• Full local seed test with GovFit data
• Verify all new fields are populated
• Test application functionality with GovFit data

Phase 2: Dev Environment Testing

• Add GovFit credentials to .env.dev
• Apply migration to dev database
• Run full reset with GovFit data
• Team testing and validation
• Gather feedback on data quality

Phase 3: Staging Deployment

• Add GovFit credentials to .env.staging
• Apply migration to staging database
• Run full reset with GovFit data
• Final validation before production

Phase 4: Production Deployment

• Add GovFit credentials to production secrets
• Apply migration to production database
• Run production seed with appropriate safety checks
• Monitor and validate

Rollback Plan

If issues occur, you can rollback by:

1. Database Rollback:

   # Restore from backup
   psql $DATABASE_URL < backup_schema_YYYYMMDD_HHMMSS.sql

2. Switch back to CivStart GovIntel base:

   # Simply don't use the --airtable-base flag
   pnpm tsx scripts/full-reset-and-seed.ts

3. Code Rollback:

   git revert <commit_hash>
   pnpm db:generate

Backward Compatibility

✅ Fully backward compatible!

• Scripts work with both Airtable bases
• Default behavior uses CivStart GovIntel (no breaking changes)
• New fields are optional (nullable or have defaults)
• Both solutionCategory and signalCategory are populated

Monitoring

After deployment, monitor:

1. Airtable Sync Success Rate

   • Check logs for sync errors
   • Verify record counts match Airtable

2. Data Quality

   • Ensure new fields are populated
   • Validate data types and formats

3. Application Performance

   • Monitor query performance on new indexed fields
   • Check vector embedding generation times

Support & Troubleshooting

Common Issues

Issue: AIRTABLE_API_KEY_GOVFIT not found

• Solution: Ensure you've added the GovFit credentials to your .env file

Issue: Database connection errors

• Solution: Verify DATABASE_URL is correct for your environment

Issue: Migration fails

• Solution: Check PostgreSQL version compatibility, ensure no schema conflicts

Getting Help

• Check logs in /logs/ directory
• Review migration file: prisma/migrations/20251117000000_add_govfit_fields/migration.sql
• Run schema analysis: pnpm tsx scripts/analyze-airtable-schema.ts

Files Modified

• ✅ packages/database/prisma/schema.prisma - Added new fields
• ✅ prisma/migrations/20251117000000_add_govfit_fields/migration.sql - Migration SQL
• ✅ scripts/full-reset-and-seed.ts - Multi-base support
• ✅ scripts/analyze-airtable-schema.ts - New schema comparison tool
• ✅ .env, .env.local - Added GovFit credentials
• ⏳ .env.dev - TODO: Add GovFit credentials
• ⏳ .env.staging - TODO: Add GovFit credentials

Next Steps

1. ✅ Complete local testing with GovFit data
2. Update .env.dev with GovFit credentials
3. Deploy and test in dev environment
4. Gather team feedback
5. Deploy to staging
6. Final validation
7. Production deployment

---

Last Updated: 2025-11-17 Version: 1.0.0 Author: CivStart Development Team