🗂️ Airtable Database Architect

Airtable Base Architecture: B2B SaaS Sales CRM

Suitability Check

Yes, Airtable is the right tool for your scale and team. Specifically:

• 8-person team, ~3K companies, ~10K contacts, ~800 deals at 18-month projection — well within Airtable's gracefully-scalable range.
• HubSpot Starter at your stage is over-priced + over-configured. The CRM features you actually use are 30% of HubSpot's surface.
• You have RevOps capacity to maintain the base. Without an internal owner, Airtable bases drift.

The honest caveat: at the 3-year scale (10K companies, 40K contacts, 3K deals), you'll feel performance taxes. Specifically: views with rollups across 40K contacts will be sluggish. Plan: at the 18-month mark, evaluate moving to a Postgres+Retool architecture or HubSpot Pro. The migration plan we'll architect now keeps that door open.

Tables (the entity model)

1. Companies

• Purpose: the account-level entity
• Primary field: Company Name (formula: {Name} & ' (' & {Domain} & ')') — human-readable and unique-ish
• Records: ~500 now → ~3K (18mo) → ~10K (3y)
• Links: to Contacts (one-to-many), to Deals (one-to-many)

2. Contacts

• Purpose: individual people inside companies
• Primary field: Full Name (formula: {First Name} & ' ' & {Last Name} & ' (' & {Email} & ')')
• Records: ~1.5K now → ~10K (18mo) → ~40K (3y)
• Links: to Companies (many-to-one), to Activities (one-to-many), to Deals (many-to-many — multiple contacts can be on one deal)

3. Deals

• Purpose: sales opportunities
• Primary field: Deal Name (formula: {Company Name (lookup)} & ' - ' & {Stage} & ' - $' & {Amount})
• Records: ~200 now → ~800 (18mo) → ~3K (3y)
• Links: to Companies (many-to-one), to Contacts (many-to-many), to Activities (one-to-many), to Owner-Users (many-to-one)

4. Activities

• Purpose: every interaction (email, call, meeting, note)
• Primary field: formula: {Type} & ' - ' & {Date} & ' - ' & {Subject}
• Records: ~5K now → ~30K (18mo) → ~100K (3y) — biggest table by record count
• Links: to Contacts (many-to-one), to Deals (many-to-one optional), to Owner-Users (many-to-one)

5. Users

• Purpose: sellers + ops + founders, for ownership and reporting
• Primary field: Full Name
• Records: ~10
• Links: to Deals (one-to-many as Owner), to Activities (one-to-many)

6. Pipeline Stages (lookup table)

• Purpose: define stages once, reference in Deals. Allows reordering/renaming without bulk updates.
• Primary field: Stage Name
• Records: ~6 (Inbound Lead, Discovery, Demo, Proposal, Negotiation, Closed-Won, Closed-Lost)
• Note: also could be a single-select on Deals; lookup table is more flexible if stages change

7. (Optional Phase 2) Email Threads

• For richer email logging beyond Activities
• Skip for v1; add at 18-month mark if needed

Fields per Table

Companies

Contacts

Deals

Activities

Users

Standard: Name, Email, Role (single-select: AE / SDR / Sales Lead / Founder / RevOps), Active (checkbox)

Pipeline Stages

Name, Order (number for sorting), Probability (number 0-1), Is Closed (checkbox)

Link Structure

Users ─owns─→ Companies
Users ─owns─→ Deals
Users ─did─→ Activities

Companies ─has─→ Contacts (one-to-many)
Companies ─has─→ Deals (one-to-many)

Contacts ─on─→ Deals (many-to-many)
Contacts ─had─→ Activities (one-to-many)

Deals ─uses─→ Pipeline Stages (many-to-one)
Deals ─has─→ Activities (one-to-many)

Critical normalization point: company-level data (industry, size, country) lives ONLY on Companies. Contacts inherit via lookup. Deals inherit via lookup-through-Company. Don't duplicate — when a company changes industry, you only update one record.

Views per Role

For AEs (their personal pipeline)

• My Active Deals: Deals filtered by Owner = current user, Stage != Closed-Won/Lost. Sorted by Expected Close Date asc.
• My Stalled Deals: Deals filtered by Owner = current user, Is Stalled = '🚨 Stalled'. Most urgent.
• My Today's Activities: Activities filtered by Owner = current user, Date = today.
• My Won This Quarter: Deals filtered by Owner = current, Stage = Closed-Won, Created Date >= start-of-quarter.

For SDRs (lead-focused)

• My Inbound Queue: Contacts filtered by Lifecycle Stage = MQL, Owner = current. Sorted by activity recency.
• My Outbound Sequenced: Contacts I've enrolled in outbound — separate field or Activities-linked.

For Sales Lead (team-wide)

• Team Pipeline by Stage: Deals grouped by Stage. Quick visual of pipeline distribution.
• Stalled Deals (All): Is Stalled = '🚨 Stalled'. For weekly forecast call.
• Pipeline by Owner: Deals grouped by Owner. For 1:1 prep.
• Inbound vs Outbound Trend: Deals grouped by Source.

For Founders (read-mostly)

• This Quarter Summary: dashboard view (use Interface Designer) showing total open pipe, won this quarter, deal-volume by stage chart.
• Top 20 Companies by ARR: sorted by Total ARR desc.

For RevOps

• Data Quality: Contacts where Email is blank. Companies where Industry is blank. (Find data gaps to fill.)
• Sync Status: Companies where Apollo enrichment is stale (>90 days). Activities where Owner is blank.

For all (shared)

• Master Companies List: simple list with key fields
• Master Contacts List: simple list with key fields

Automations Worth Building

v1 (build immediately):

1. Slack Alert: Deal Stage Change

• Trigger: Record updated in Deals where Stage changes
• Action: Send Slack message to #sales channel: '{Owner} moved {Deal Name} from {Old Stage} → {New Stage}'
• Why: keeps team aware, signals momentum, social-proofs activity

2. Daily Stalled Deal Digest

• Trigger: Daily at 9am Pacific
• Action: Send Slack DM to each AE listing their stalled deals (Is Stalled = '🚨 Stalled')
• Why: stalled deals are the #1 silent killer; daily prompt forces action

3. Auto-update Last Stage Change

• Trigger: Record updated in Deals where Stage changes
• Action: Set 'Last Stage Change' field = today
• Why: lets you compute time-in-stage for forecast accuracy

v2 (build after 4 weeks of v1 use):

4. Sync Is-Paying-Customer from app DB

• Trigger: scheduled hourly via webhook from your app's DB or a sync source
• Action: update Companies.Is Paying Customer + Total ARR
• Why: keeps the 'paid customer' signal accurate without manual updates

5. Auto-create Activity from Gmail

• Trigger: Email sent to/from a Contact's email address
• Action: create Activity record linked to Contact, Type = Email
• Why: activity tracking without manual logging burden
• Caveat: Airtable's native Gmail sync is mediocre. Use Zapier or Make for reliable Gmail-to-Airtable bridge.

Sync Sources

Slack

• One-way: Airtable → Slack (deal-stage alerts, stalled-deal digest)
• No two-way needed for v1

Gmail

• One-way: Gmail → Airtable Activities (logs emails to/from contacts)
• Use Zapier (~$19/month for the volume) — Airtable's native is unreliable

App DB

• One-way: app DB → Airtable Companies (paying status, ARR)
• Use Airtable's native Sync source if your app DB is Postgres + accessible, or webhook-based via your backend

HubSpot (during migration only)

• One-way: HubSpot → Airtable for the 1500 contacts + 200 deals migration
• Airtable's HubSpot Sync extension handles initial pull
• After migration: disconnect; HubSpot is no longer source-of-truth

Apollo (enrichment)

• On-demand or scheduled: Apollo → Airtable Contacts (job title, seniority, company size)
• Use Airtable's Apollo extension or a custom Zap

Permission Design

Admin (RevOps + founders): Full base access

Editor (AEs + SDRs + Sales Lead):

• Edit access to all tables
• Cannot delete records
• Cannot modify base structure (table fields)

Read-only (board members or external advisors):

• Shared via Interface Designer dashboard
• See aggregated views only — total pipe, won this Q, top companies
• NO direct base access

Per-table considerations:

• Activities are write-heavy and noisy. Don't restrict — let everyone log.
• Pipeline Stages table: read-only for everyone except RevOps (only RevOps can add/reorder stages)
• Users table: only RevOps can edit

Scaling Boundaries

Comfortable range (your design works fluidly):

• Companies < 5K
• Contacts < 20K
• Deals < 1.5K
• Activities < 50K
• Total: ~75K records

Performance signs to watch for as you grow:

• Views with rollups (Total Open Pipeline) take >2s to load → first sign of strain
• Search across Activities slows noticeably (typical at >50K activities)
• Automations occasionally fail or delay (Pro tier hits limits at 25K runs/month)
• Interface Designer dashboards refresh slowly

At 18-month projection (10K contacts, 800 deals, ~30K activities): comfortable.

At 3-year projection (40K contacts, 3K deals, 100K activities): expect performance issues. Specifically:

• Activities table search will be slow
• Cross-table rollups will be visibly slow
• View loading on Companies (with the rollups) will hit 3-5s

Migration trigger: when (a) view loads exceed 5s AND (b) Activities table exceeds 75K records AND (c) you're on Business tier already. At that point, evaluate Postgres + Retool or moving back to HubSpot Pro.

Implementation Order

Week 1: Schema + Tables

• Create the 7 tables
• Add all fields per spec
• Set primary fields
• Build link structure
• Configure 1-2 critical views per table (no role-specific views yet)

Week 2: Migration from HubSpot

• Export HubSpot Companies → import to Airtable Companies
• Export Contacts → import (verify email-based dedup)
• Export Deals → import (preserve Stage history if possible)
• Spot-check 50 random records for accuracy
• Disconnect HubSpot as source-of-truth

Week 3: Views per role

• Build all role-specific views (AE, SDR, Sales Lead, Founder, RevOps)
• Test with each role's primary user
• Iterate based on feedback

Week 4: Automations v1

• Slack deal-stage-change alert
• Daily stalled-deal digest
• Auto-update Last Stage Change
• Test for 1 week

Week 5-6: Integrations

• Gmail → Activities sync (Zapier)
• App DB → Companies sync (paying status)
• Apollo enrichment

Week 7: Interface Designer dashboards

• Founder summary view
• Sales Lead pipeline overview
• Read-only board dashboard

Week 8: Polish + handoff

• Document the base in a 'README' base or Notion page
• Train each role on their views
• Establish RevOps weekly maintenance cadence

What This Base Won't Solve

• Email send infrastructure: Activities tracks emails but doesn't send them. Use Gmail / Apollo / Outreach for outbound sending.
• Deal-room collaboration: Airtable is for internal CRM, not customer-facing deal rooms.
• Quote/contract generation: out of scope. Use a separate tool (PandaDoc, etc.).
• Forecasting algorithms: rollups give you raw numbers; complex weighted forecasting requires custom formulas or external tooling.
• Compliance / SOC2-grade audit trail: Airtable has limited audit logging. If you need full record-of-changes, use a real CRM or layer on top.
• Marketing automation: sequences, drip campaigns, lead scoring — Airtable is the wrong tool. Use HubSpot Marketing Hub or similar.

Maintenance Cadence

Daily (RevOps, ~5 min):

• Skim daily stalled-deal digest
• Spot-check any anomalies in Slack alerts

Weekly (RevOps, ~30 min):

• Review Data Quality views (blank emails, blank industries)
• Verify automations didn't fail silently (Airtable shows failed automation runs)
• Add new Pipeline Stages if needed

Monthly (RevOps + Sales Lead, ~1 hour):

• Audit views — are any unused? Retire.
• Review automations — any noise? Tune triggers.
• Check sync source freshness — Apollo enrichment, app DB push

Quarterly (RevOps + Founders, ~half day):

• Performance review — view load times, automation queue
• Schema review — new fields needed? Old fields unused?
• Permission audit
• Plan tier evaluation (do you need Business tier yet?)

Annually (full audit):

• Migration evaluation: still scaling well? Or approaching the wall?
• Re-architect if outgrowing

Key Takeaways

• Airtable is right for your scale and team. Plan migration trigger around 18-month mark to keep options open.
• Normalize ruthlessly. Company-level data lives ONLY on Companies; Contacts and Deals lookup to it.
• The 5 critical views: AE personal pipeline, stalled deals, team pipeline by stage, founder summary, data-quality. Build these first.
• Automate the 3 things that pay off immediately: Slack stage alerts, daily stalled-deal digest, auto-Last-Stage-Change. Skip the others until you've used the base for 4 weeks.
• At 3-year projection scale (40K contacts, 100K activities), expect performance taxes. Plan migration to Postgres+Retool or HubSpot Pro at the 18-month evaluation.
• RevOps owns the base. Without an internal owner, Airtable bases drift within 6 months.