-- Entity mapping cache (deduplication)
CREATE TABLE airtable_entity_mapping (
  airtable_unique_id text PRIMARY KEY,
  entity_type text NOT NULL, -- 'event' | 'venue' | 'production'
  entity_id uuid NOT NULL,
  airtable_data jsonb,
  first_synced_at timestamptz,
  last_synced_at timestamptz
);

-- Fever client (external organization)
CREATE TABLE clients (
  id uuid PRIMARY KEY,
  name text NOT NULL,
  slug text UNIQUE NOT NULL,
  type text -- 'agency' | 'venue' | 'company'
);

-- Productions owned by clients
CREATE TABLE productions (
  id uuid PRIMARY KEY,
  client_id uuid REFERENCES clients(id),
  name text NOT NULL,
  -- ...
);

-- Events inherit client through productions
CREATE TABLE events (
  id uuid PRIMARY KEY,
  production_id uuid REFERENCES productions(id) NOT NULL,
  -- Note: account_id removed in October 2025 migration
  -- Events inherit client relationship through production
);

async findExistingEntity(
  airtableUniqueId: string,
  entityType: 'event' | 'venue' | 'production',
): Promise<string | null> {
  // Step 1: Check cache
  const { data } = await this.client
    .from('airtable_entity_mapping')
    .select('entity_id')
    .eq('airtable_unique_id', airtableUniqueId)
    .eq('entity_type', entityType)
    .single();

  if (!data?.entity_id) return null;

  const cachedId = data.entity_id;

  // Step 2: CRITICAL - Validate entity still exists in target table
  const tableName = entityType === 'production'
    ? 'productions'
    : entityType === 'venue'
      ? 'venues'
      : 'events';

  const { data: entity } = await this.client
    .from(tableName)
    .select('id')
    .eq('id', cachedId)
    .maybeSingle();

  if (!entity) {
    // Stale cache detected - auto-cleanup
    console.warn(`🧹 Stale cache detected for ${entityType} ${cachedId}`);
    await this.deleteEntityMapping(airtableUniqueId);
    return null; // Force entity recreation
  }

  return cachedId; // Valid cached entity
}

-- Check for duplicate mappings
SELECT airtable_unique_id, COUNT(*)
FROM airtable_entity_mapping
WHERE entity_type = 'event'
GROUP BY airtable_unique_id
HAVING COUNT(*) > 1;

-- Find events without cache entries
SELECT e.id, e.name, e.event_date
FROM events e
LEFT JOIN airtable_entity_mapping aem
  ON aem.entity_id = e.id AND aem.entity_type = 'event'
WHERE e.created_at > NOW() - INTERVAL '1 day'
  AND aem.airtable_unique_id IS NULL;

# Run diagnostic script
pnpm tsx .claude/skills/airtable-sync-specialist/scripts/diagnose-sync-cache.ts

# Check for stale cache entries manually
pnpm sql:local "
  SELECT aem.*,
         CASE
           WHEN e.id IS NULL THEN 'STALE'
           ELSE 'VALID'
         END as status
  FROM airtable_entity_mapping aem
  LEFT JOIN events e ON e.id = aem.entity_id
  WHERE aem.entity_type = 'event'
    AND e.id IS NULL;
"

# Check RLS policies
pnpm sql:local --inspect events
pnpm sql:local --inspect airtable_entity_mapping

# Test as super admin
pnpm tsx .claude/skills/rls-policy-generator/scripts/diagnose-fever-client-rls.ts

-- Add super admin bypass to airtable_entity_mapping
CREATE POLICY airtable_mapping_insert ON airtable_entity_mapping
FOR INSERT TO authenticated
WITH CHECK (
  public.is_super_admin() OR
  auth.uid() = created_by -- if you track creator
);

-- Events should allow super admin to create via sync
CREATE POLICY events_insert ON events
FOR INSERT TO authenticated
WITH CHECK (
  public.is_super_admin() OR
  -- other conditions
);

-- Check if Fever client exists
SELECT * FROM clients WHERE slug = 'fever';

-- Check default client config
SELECT * FROM clients WHERE slug = 'default-airtable-client';

// Ensure client via ClientManager
import { ensureClient } from '~/app/admin/sync/_lib/server/client-manager';

const client = await ensureClient(supabaseClient);
// Creates Fever client if not exists

// Check change detection logic
import { ChangeDetectorService } from '~/app/admin/sync/_lib/server/change-detector.service';

const detector = new ChangeDetectorService();
const hasChanges = detector.hasChanges(
  newAirtableData,
  cachedAirtableData,
  entityType
);

console.log('Changes detected:', hasChanges);
console.log('New data:', newAirtableData);
console.log('Cached data:', cachedAirtableData);

1. Fetch from Airtable
   ↓
2. Ensure Fever client exists (ClientManager)
   ↓
3. For each Airtable record:
   ↓
   a. Generate UniqueID
   ↓
   b. Check cache (DuplicatePreventionService.findExistingEntity)
      ↓
      EXISTS? → c. Check for changes (ChangeDetectorService)
                ↓
                CHANGES? → d. Update entity
                         → e. Update cache
                NO? → Skip (already synced)
      ↓
      NOT EXISTS? → f. Create entity
                   → g. Register in cache

import { AirtableSyncService } from '~/app/admin/sync/_lib/server/airtable-sync.service';
import { ensureClient } from '~/app/admin/sync/_lib/server/client-manager';

// 1. Ensure Fever client exists
const client = await ensureClient(supabaseClient);

// 2. Initialize sync service
const syncService = new AirtableSyncService(
  supabaseClient,
  client.id // Fever client ID
);

// 3. Sync productions from Airtable
const result = await syncService.syncProductions(airtableRecords);

if (result.success) {
  console.log('Synced:', result.created, 'created,', result.updated, 'updated');
} else {
  console.error('Sync failed:', result.error);
}

// ✅ CORRECT - Validates entity exists
const existingId = await duplicatePrevention.findExistingEntity(
  airtableUniqueId,
  'event'
);

if (existingId) {
  // Entity exists and is valid
  const hasChanges = changeDetector.hasChanges(newData, cachedData, 'event');

  if (hasChanges) {
    // Update entity
  }
} else {
  // Create new entity
}

// ❌ WRONG - Trusts cache without validation
const { data } = await supabase
  .from('airtable_entity_mapping')
  .select('entity_id')
  .eq('airtable_unique_id', airtableUniqueId)
  .single();

if (data) {
  // Assumes entity exists - may be stale!
}

// ✅ CORRECT - Can run multiple times safely
async function syncProduction(airtableRecord: AirtableProduction) {
  const uniqueId = generateUniqueId(airtableRecord);

  // Check if exists (validates entity still exists)
  const existingId = await duplicatePrevention.findExistingEntity(
    uniqueId,
    'production'
  );

  if (existingId) {
    // Update if changed
    if (changeDetector.hasChanges(newData, cachedData, 'production')) {
      await updateProduction(existingId, newData);
      await duplicatePrevention.updateCache(uniqueId, newData);
    }
  } else {
    // Create new
    const newId = await createProduction(newData);
    await duplicatePrevention.registerEntity(uniqueId, 'production', newId);
  }
}

// ✅ CORRECT - Handles failures gracefully
async function syncBatch(records: AirtableRecord[]) {
  const results = {
    created: 0,
    updated: 0,
    failed: 0,
    errors: [] as Array<{ record: string; error: string }>
  };

  for (const record of records) {
    try {
      const result = await syncSingleRecord(record);
      if (result.created) results.created++;
      if (result.updated) results.updated++;
    } catch (error) {
      results.failed++;
      results.errors.push({
        record: record.id,
        error: error.message
      });

      // Log but continue processing
      console.error(`Failed to sync record ${record.id}:`, error);
    }
  }

  return results;
}

describe('DuplicatePreventionService', () => {
  test('validates cached entity exists', async () => {
    const service = new DuplicatePreventionService(adminClient);

    // Create cache entry for non-existent entity
    await adminClient.from('airtable_entity_mapping').insert({
      airtable_unique_id: 'test-unique-id',
      entity_type: 'event',
      entity_id: 'non-existent-uuid'
    });

    // Should return null and cleanup cache
    const result = await service.findExistingEntity('test-unique-id', 'event');

    expect(result).toBeNull();

    // Verify cache was cleaned up
    const { data } = await adminClient
      .from('airtable_entity_mapping')
      .select()
      .eq('airtable_unique_id', 'test-unique-id')
      .maybeSingle();

    expect(data).toBeNull();
  });
});

describe('Airtable Sync Integration', () => {
  test('syncs production from Airtable', async () => {
    const syncService = new AirtableSyncService(adminClient, feverClientId);

    const airtableData = {
      id: 'airtable-prod-123',
      fields: {
        Name: 'Swan Lake',
        Type: 'Ballet',
        // ...
      }
    };

    const result = await syncService.syncProductions([airtableData]);

    expect(result.success).toBe(true);
    expect(result.created).toBe(1);

    // Verify production created
    const { data: production } = await adminClient
      .from('productions')
      .select()
      .eq('name', 'Swan Lake')
      .single();

    expect(production).toBeDefined();

    // Verify cache entry
    const { data: mapping } = await adminClient
      .from('airtable_entity_mapping')
      .select()
      .eq('airtable_unique_id', generateUniqueId(airtableData))
      .single();

    expect(mapping.entity_id).toBe(production.id);
  });
});

# Airtable API Authentication
AIRTABLE_API_KEY=key_xxxxx              # Personal access token from Airtable

# Base ID (workspace + base identifier)
AIRTABLE_BASE_ID=appxxxxx              # Application/Base ID from URL

# Optional: Explicitly configure primary table
AIRTABLE_TABLE_NAME=Ballee Dates       # Table name in Airtable (default)

# Client configuration for Fever sync
AIRTABLE_CLIENT_SLUG=fever              # Client slug in database
AIRTABLE_CLIENT_NAME=Fever              # Display name
AIRTABLE_CLIENT_EMAIL=contact@fever.co  # Contact email
AIRTABLE_CLIENT_TYPE=agency             # Client type (agency, venue, company)
AIRTABLE_CLIENT_CONTACT_NAME=Fever Team # Contact person name

// Current: Hard-coded to use AIRTABLE_TABLE_NAME or default "Ballee Dates"
const tableName = process.env.AIRTABLE_TABLE_NAME || 'Ballee Dates';
const records = await base(tableName).select().all();

// Future: Make table name configurable per sync operation
async function fetchTableData(tableName: string) {
  const airtable = new Airtable({ apiKey: process.env.AIRTABLE_API_KEY });
  const base = airtable.base(process.env.AIRTABLE_BASE_ID);
  return base(tableName).select().all();
}

Airtable Base (appXXXXXX)
│
├─ Table: "Ballee Dates" (Primary)
│  ├─ Fields: Date, City_linked, Venue, Sub-Programm, Starttime: 1. Show, Starttime: 2. Show, Status
│  ├─ Client: Fever (AIRTABLE_CLIENT_SLUG=fever)
│  └─ Mapped to: events, productions, venues
│
└─ Table: "Ratings" (Secondary)
   ├─ Fields: Name, Date, Show Time, Rating, Comment
   ├─ Client: Fever (same)
   └─ Mapped to: event_ratings

# From project root
cd apps/web
npx tsx scripts/discover-all-tables.ts

🔍 Discovering all tables in Airtable base...

Base ID: appXXXXXXXXXXXXXX

✅ Found 5 table(s) in the base:

1. Ballee Dates
   ID: tblXXXXXXXXXXXXXX
   Description: Performance schedule for all shows
   Fields: 8
   Field names:
     - Date (date)
     - City_linked (singleCollaborator)
     - Venue (singleLineText)
     - Sub-Programm (singleLineText)
     - Starttime: 1. Show (duration)
     - Starttime: 2. Show (duration)
     - Status (singleSelect)
     - UniqueID (singleLineText)

2. Ratings
   ID: tblYYYYYYYYYYYYYY
   Description: Performance ratings and reviews
   Fields: 5
   Field names:
     - Name (singleLineText)
     - Date (date)
     - Show Time (duration)
     - Rating (number)
     - Comment (multilineText)

3. Cities
   ID: tblZZZZZZZZZZZZZZ
   Description: City reference data
   Fields: 3
   Field names:
     - Name (singleLineText)
     - Code (singleLineText)
     - Country (singleLineText)

4. Venues
   ID: tblVVVVVVVVVVVVVV
   Description: Venue reference data
   Fields: 4
   Field names:
     - Name (singleLineText)
     - City (singleLineText)
     - Address (singleLineText)
     - Capacity (number)

5. Productions
   ID: tblPPPPPPPPPPPPPP
   Description: Production/program reference data
   Fields: 3
   Field names:
     - Name (singleLineText)
     - Type (singleSelect)
     - Description (multilineText)

⭐ Found 1 ratings-related table(s):
   - Ratings (tblYYYYYYYYYYYYYY)

// Example: Sync from Venues table instead of Ballee Dates
const venuesTableId = 'tblVVVVVVVVVVVVVV';
const venuesTableName = 'Venues';

const records = await base(venuesTableName).select().all();

// Process venue records
for (const record of records) {
  const venueName = record.fields['Name'];
  const city = record.fields['City'];
  const capacity = record.fields['Capacity'];

  // Sync to Ballee venues table
  await syncVenue({
    name: venueName,
    city,
    capacity
  });
}

cd apps/web && npx tsx scripts/discover-all-tables.ts

// Example: Mapping Invoices table to Ballee invoices
const airtableInvoices = await fetchAirtableTable('Invoices');

const mappedInvoices = airtableInvoices.map(record => ({
  // Airtable field → Ballee field
  externalId: record.fields['Invoice#'],
  date: new Date(record.fields['Date']),
  amount: record.fields['Amount'],
  clientName: record.fields['Client'],
  status: record.fields['Status']
}));

// Generic table sync function
async function syncGenericTable(
  tableName: string,
  mapFunction: (record: any) => object
) {
  const records = await base(tableName).select().all();

  for (const record of records) {
    const mapped = mapFunction(record);
    // Upsert to Ballee database
    await syncToBallee(tableName, mapped);
  }
}

// Type-safe field extraction
interface AirtableRow {
  fields: Record<string, unknown>;
}

function extractAirtableFields(record: AirtableRow) {
  return {
    // Text fields
    name: String(record.fields['Name']),
    description: String(record.fields['Description'] || ''),

    // Numeric fields
    capacity: Number(record.fields['Capacity']),
    rating: Number(record.fields['Rating']),

    // Date fields
    performanceDate: new Date(String(record.fields['Date'])),

    // Time/Duration
    startTime: String(record.fields['Starttime']), // e.g., "19:30"

    // Selections
    status: String(record.fields['Status']), // Single select enum
    tags: Array.isArray(record.fields['Tags'])
      ? record.fields['Tags']
      : [], // Multiple select array

    // Boolean
    isActive: record.fields['Active'] === true,

    // Linked records
    cityId: record.fields['City_linked']?.[0] || null, // First linked record

    // Collaborators
    createdBy: record.fields['Created by']?.email || null,

    // Metadata (auto fields)
    createdAt: new Date(String(record.fields['Created time'])),
    updatedAt: new Date(String(record.fields['Last modified time']))
  };
}

# Create new Personal Access Token with correct scopes:
1. Go to https://airtable.com/account/tokens
2. Create new token
3. Add scope: "schema.bases:read"
4. Also add: "data.records:read"
5. Copy and use new token

# Check your token
echo $AIRTABLE_API_KEY  # Should start with "pat_" or "key_"

# If empty, set it
export AIRTABLE_API_KEY="your_token_here"

# Verify base ID from Airtable URL
# https://airtable.com/appXXXXXXXXXXXXXX/...
# Extract: appXXXXXXXXXXXXXX

echo $AIRTABLE_BASE_ID  # Should match the app... from URL

// Unique ID created from composite data
const uniqueId = `${date}_${venue}_${city}_${program}_${startTime1}`;

// Used for deduplication in airtable_entity_mapping

import { fetchAirtableData } from '~/app/admin/sync/_lib/server/airtable-api.service';

// Fetch all Fever dates from Ballee Dates table
const { shows, venues, productions } = await fetchAirtableData();

// Shows format: ParsedShow[]
shows.forEach(show => {
  console.log(`${show.date} @ ${show.venue} (${show.city})`);
  console.log(`  Program: ${show.program}`);
  console.log(`  Show 1: ${show.startTime1}`);
  if (show.startTime2) console.log(`  Show 2: ${show.startTime2}`);
  if (show.canceled) console.log(`  Status: CANCELED`);
});

// Venues format: Map<string, VenueWithCity>
// Key: "venueName_city"
venues.forEach((venue, key) => {
  console.log(`${venue.name} (${venue.city})`);
});

// Productions format: Set<string>
productions.forEach(prod => {
  console.log(`Production: ${prod}`);
});

import { AirtableSyncService } from '~/app/admin/sync/_lib/server/airtable-sync.service';
import { ensureClient } from '~/app/admin/sync/_lib/server/client-manager';

// 1. Ensure Fever client exists in database
const feverClient = await ensureClient(supabaseClient, {
  slug: 'fever',
  name: 'Fever',
  email: 'contact@fever.co',
  type: 'agency',
  contactName: 'Fever Team'
});

// 2. Initialize sync service for Fever
const syncService = new AirtableSyncService(
  supabaseClient,
  feverClient.id
);

// 3. Execute full sync with change tracking
const result = await syncService.syncWithChangeTracking({
  clientId: feverClient.id,
  triggeredBy: currentUserId,        // Admin user ID
  triggerType: 'manual',             // 'manual' | 'cron' | 'webhook'
  dryRun: false,                     // Preview without applying
  notifyOnChanges: true              // Send Slack notifications
});

// 4. Check results
console.log(`
  ✅ Venues synced: ${result.venues.created} created, ${result.venues.updated} updated
  ✅ Productions synced: ${result.productions.created} created, ${result.productions.updated} updated
  ✅ Events synced: ${result.events.created} created, ${result.events.updated} updated
  ✅ Ratings synced: ${result.ratings.created} created, ${result.ratings.updated} updated
`);

if (result.errors.length > 0) {
  console.error('⚠️  Errors during sync:', result.errors);
  // Handle errors...
}

# List all tables in base to verify access
pnpm tsx .claude/skills/production-database-query/scripts/discover-all-tables.ts

# Output:
# Tables in base appXXXXXX:
# - Ballee Dates (primary sync table)
# - Ratings (secondary table)
# - Cities (reference data)

# Inspect Ballee Dates table structure
pnpm tsx .claude/skills/airtable-sync-specialist/scripts/test-ratings-table-id.ts

# Validates:
# - Table exists and is accessible
# - All required fields present
# - Field names match constants.ts definitions

// Use dryRun mode to preview changes
const result = await syncService.syncWithChangeTracking({
  clientId: feverClient.id,
  dryRun: true,  // Preview only, no database changes
  notifyOnChanges: false
});

// Review what would be synced
console.log('Would create events:', result.events.created);
console.log('Would update events:', result.events.updated);
console.log('Changes:', result.changeLog);

✅ data.records:read     - Can read records from all tables
✅ schema.bases:read     - Can read table schema and metadata
❌ data.records:write    - Cannot create/update records
❌ data.records:delete   - Cannot delete records

# REQUIRED - API Authentication
AIRTABLE_API_KEY=patXXXXXXXXXXXXXX              # Personal access token

# REQUIRED - Base identification
AIRTABLE_BASE_ID=appwh9Xy2DZzbjg4J             # Fever base ID

# OPTIONAL - Table name (default: "Ballee Dates")
AIRTABLE_TABLE_NAME=Ballee Dates                # Primary sync table

# OPTIONAL - Fever client configuration
AIRTABLE_CLIENT_SLUG=fever                      # DB client slug
AIRTABLE_CLIENT_NAME=Fever                      # Display name
AIRTABLE_CLIENT_EMAIL=contact@fever.co          # Contact email
AIRTABLE_CLIENT_TYPE=agency                     # Client type
AIRTABLE_CLIENT_CONTACT_NAME=Fever Team         # Contact person

# OPTIONAL - Ratings table
RATING_TABLE_NAME=Ratings                       # Ratings sync table