L'Oreal Airtable Integration

Automated pipeline that takes JSON job specifications from the OMG system, syncs them into an Airtable Deliverables table, and sends daily HTML email reports.

How It Works

                         SERVER (box-cli-01)
  ┌──────────────────────────────────────────────────────────────────────┐
  │                                                                      │
  │   JSON Workflow Processor (existing service)                         │
  │   ┌──────────────────────────────────────────┐                       │
  │   │  Watches /data/PRODUCTION/JSON/           │                      │
  │   │  Detects new OMG JSON files               │                      │
  │   │  Routes by ClientCode                     │                      │
  │   └──────────────┬───────────────────────────┘                       │
  │                  │                                                    │
  │                  │  ClientCode == "LOREAL"                            │
  │                  ▼                                                    │
  │   /data/PRODUCTION/CLIENT-STORES/LOREAL/IN/                          │
  │   ┌──────────────────────────────────────────┐                       │
  │   │  6741380.json                             │                      │
  │   │  6733601.json                             │                      │
  │   │  6615788.json  ...                        │                      │
  │   └──────────────┬───────────────────────────┘                       │
  │                  │                                                    │
  │                  │  watchdog detects new files                        │
  │                  ▼                                                    │
  │   Deliverables Service (/home/dalim/LOREAL-AIRTABLE/)                │
  │   ┌──────────────────────────────────────────┐                       │
  │   │  1. Parse JSON                            │                      │
  │   │  2. Flatten nested fields                 │                      │
  │   │  3. Upsert to Airtable (Number = key)    │                      │
  │   │  4. Move file to PROCESSED/YYYY-MM-DD/    │                      │
  │   │  5. Track daily stats                     │                      │
  │   │  6. Email report at midnight              │                      │
  │   └──────────┬──────────────┬────────────────┘                       │
  │              │              │                                         │
  │              ▼              ▼                                         │
  │   .../LOREAL/PROCESSED/    .../LOREAL/FAILED/                        │
  │     └─ 2026-02-06/           └─ 2026-02-06/                          │
  │         ├─ 6741380.json          └─ bad_file.json                    │
  │         ├─ 6733601.json                                              │
  │         └─ 6615788.json                                              │
  │                                                                      │
  └──────────────────────────────────────────────────────────────────────┘
                   │
                   │  Airtable API (batch_upsert)
                   ▼
          ┌─────────────────┐
          │    AIRTABLE      │
          │                  │
          │  Deliverables    │
          │  Table           │
          │                  │
          │  56 fields       │
          │  per record      │
          │                  │
          │  Number = key    │
          │  (create/update) │
          └─────────────────┘

Project Structure

LOREAL-INTEGRATIONS/                    (local dev)
├── airtable_connect.py                 # Original Airtable connection test script
├── create_deliverables_fields.py       # One-time: creates 56 fields in Airtable table
├── deliverables_sync.py                # Manual: process a folder/file and upsert to Airtable
├── deliverables_service.py             # Service: file watcher + upserter + daily reports
├── loreal-deliverables.service         # Systemd unit file for the server
├── requirements.txt                    # Pinned deps (local Python 3.14)
├── requirements-server.txt             # Unpinned deps (server Python 3.12)
├── JSON-SAMPLES/                       # Sample JSON files for testing
│   ├── 6615788.json
│   ├── 6733601.json
│   └── 6741380.json
├── Airtable PAT                        # PAT token
├── Loreal Fields for Artable...xlsx    # Field mapping reference
└── venv/                               # Local Python virtual environment

/home/dalim/LOREAL-AIRTABLE/            (server)
├── deliverables_service.py             # The daemon
├── deliverables_sync.py                # Manual sync tool
├── requirements-server.txt             # Dependencies
├── loreal-deliverables.service         # Systemd unit (copied to /etc/systemd/system/)
├── deliverables_service.pid            # Written at runtime by the service
└── venv/                               # Server Python 3.12 virtual environment

Airtable Configuration

Item	Value
Base ID	apptPeKvHf7wZqjc5
Table ID	tbldCaDgXF9ewNjPq
View ID	viw3iybTqEnUYCuuH
Table Name	Deliverables
Upsert Key	Number (OMG job number)

Field Map (56 fields)

Fields are prefixed by section to avoid name collisions:

Prefix	Source	Count	Examples
(none)	Upsert key	1	Number
Job_	JobSpecification.JobDetails	22	Job_Campaign, Job_DueDate, Job_Type
Activation_	JobDetails.activation_data	17	Activation_Status, Activation_Destination
Project_	JobSpecification.ProjectDetails	8	Project_Title, Project_StartDate
Asset_	JobSpecification.AssetDetails	8	Asset_Filename, Asset_Version

Date fields are stored as ISO YYYY-MM-DD. The Outputs array and AdditionalAttributes dict are serialized as JSON strings in multilineText fields.

Excluded fields: MediaTypeID, SubMediaTypeID, MediaData, MediaGroupName, MediaOwnerName, MediaSectionName, MediaSectionData, MediaFormatName, MediaFormatData.

Server Installation Guide

Prerequisites

• Linux server with Python 3.8+ (tested on Python 3.12)
• Network access to Airtable API (https://api.airtable.com)
• Network access to SMTP (smtp.mailgun.org:587)
• Access to /data/PRODUCTION/CLIENT-STORES/LOREAL/IN (where the JSON workflow processor drops L'Oreal files)

Step 1: Create project directory

mkdir -p /home/dalim/LOREAL-AIRTABLE

Step 2: Copy files to server

From local machine:

scp deliverables_service.py deliverables_sync.py requirements-server.txt loreal-deliverables.service \
    dalim@box-cli-01:/home/dalim/LOREAL-AIRTABLE/

Step 3: Create virtual environment

cd /home/dalim/LOREAL-AIRTABLE
python3.12 -m venv venv
./venv/bin/pip install --upgrade pip
./venv/bin/pip install -r requirements-server.txt

Use python3.12 (or whatever 3.8+ is available). Do NOT use the system python3 if it's 3.6 - pyairtable requires 3.8+.

Step 4: Test manually

cd /home/dalim/LOREAL-AIRTABLE
./venv/bin/python deliverables_sync.py /data/PRODUCTION/CLIENT-STORES/LOREAL/IN

This processes all JSON files in the folder and upserts to Airtable. Check the output for CREATED/UPDATED counts.

Step 5: Install systemd service

sudo cp /home/dalim/LOREAL-AIRTABLE/loreal-deliverables.service /etc/systemd/system/
sudo systemctl daemon-reload
sudo systemctl enable loreal-deliverables
sudo systemctl start loreal-deliverables

Step 6: Verify

sudo systemctl status loreal-deliverables
journalctl -u loreal-deliverables -f          # live tail logs

You should see files being processed. Once the IN folder drains, the service watches for new files.

Server Commands Reference

# --- Service Management ---
sudo systemctl start loreal-deliverables
sudo systemctl stop loreal-deliverables
sudo systemctl restart loreal-deliverables
sudo systemctl status loreal-deliverables

# --- Logs ---
journalctl -u loreal-deliverables -f              # live tail
journalctl -u loreal-deliverables --since today    # today's logs
journalctl -u loreal-deliverables -n 50            # last 50 lines

# --- Reports ---
cd /home/dalim/LOREAL-AIRTABLE

./venv/bin/python deliverables_service.py --report  # send report now (signals running service)

# OR via signal directly:
kill -SIGUSR1 $(cat deliverables_service.pid)

# --- Manual Sync (one-off, doesn't need the service running) ---
./venv/bin/python deliverables_sync.py /data/PRODUCTION/CLIENT-STORES/LOREAL/IN
./venv/bin/python deliverables_sync.py /path/to/single/file.json

# --- After code changes ---
# Copy updated file to server, then:
sudo systemctl restart loreal-deliverables

Reports

The service sends a styled HTML email report to daveporter@oliver.agency:

• Automatic: Every day at midnight (00:00 GMT)
• On demand: ./venv/bin/python deliverables_service.py --report
• Via signal: kill -SIGUSR1 $(cat deliverables_service.pid)

The report includes:

• Total files processed, records created/updated, errors
• Success rate and average processing time
• Job category breakdown
• Hourly activity bar chart
• Recent job numbers
• Error details (last 10)

Plain text reports are also saved to /data/PRODUCTION/LOREAL_DELIVERABLES_LOGS/ and retained for 30 days.

File Flow

1. OMG system generates JSON job specs
2. JSON Workflow Processor detects files, routes L'Oreal jobs to /data/PRODUCTION/CLIENT-STORES/LOREAL/IN/
3. Deliverables Service picks up files via watchdog (instant) or periodic scan (60s safety net)
4. Each file is parsed, flattened to 56 Airtable fields, and upserted using Number as the unique key
5. New records are CREATED, existing records (same Number) are UPDATED
6. Processed files move to .../LOREAL/PROCESSED/YYYY-MM-DD/
7. Failed files move to .../LOREAL/FAILED/YYYY-MM-DD/
8. Stats accumulate throughout the day, report emails at midnight

Scripts

airtable_connect.py (original)

The first script built to test Airtable connectivity. Connects to the MASTER table (appWy60RN6TeHDXx6 / tblCXjVtaHnLTCulY), creates test records, and searches. Not used in production.

create_deliverables_fields.py

Run once to create all 56 fields in the Deliverables table. Supports --dry-run to preview without creating.

./venv/bin/python create_deliverables_fields.py           # create fields
./venv/bin/python create_deliverables_fields.py --dry-run  # preview only

deliverables_sync.py

Manual sync tool. Point it at a folder or single file, it flattens the JSON and upserts to Airtable. Files are NOT moved - useful for testing or one-off imports.

./venv/bin/python deliverables_sync.py ./JSON-SAMPLES
./venv/bin/python deliverables_sync.py ./JSON-SAMPLES/6741380.json

deliverables_service.py

The production daemon. Watches a folder for JSON files, processes them, moves to processed/failed, tracks stats, sends email reports.

./venv/bin/python deliverables_service.py                          # default watch folder
./venv/bin/python deliverables_service.py --watch ./JSON-SAMPLES   # custom folder
./venv/bin/python deliverables_service.py --report                 # send report now

Troubleshooting

Service won't start - Python version

from pyairtable import Api
  File ".../python3.6/site-packages/pyairtable/..."

pyairtable requires Python 3.8+. Rebuild the venv with a newer Python:

rm -rf /home/dalim/LOREAL-AIRTABLE/venv
python3.12 -m venv /home/dalim/LOREAL-AIRTABLE/venv
/home/dalim/LOREAL-AIRTABLE/venv/bin/pip install --upgrade pip
/home/dalim/LOREAL-AIRTABLE/venv/bin/pip install -r requirements-server.txt
sudo systemctl restart loreal-deliverables

pip can't find packages

Old pip (9.x) can't resolve modern packages. Upgrade pip first:

./venv/bin/pip install --upgrade pip
./venv/bin/pip install -r requirements-server.txt

Report shows all zeros

The --report command signals the running service to send its accumulated stats. If the service isn't running, it can't send. Check:

sudo systemctl status loreal-deliverables
cat /home/dalim/LOREAL-AIRTABLE/deliverables_service.pid

422 Airtable errors

Field type mismatch. The typecast=True flag handles most cases, but check the logs for specifics:

journalctl -u loreal-deliverables --since today | grep ERROR

Files reprocessing endlessly

The service moves processed files to .../LOREAL/PROCESSED/. If files aren't being moved, check folder permissions:

ls -la /data/PRODUCTION/CLIENT-STORES/LOREAL/

The dalim user needs write access to create PROCESSED/ and FAILED/ subdirectories.

---

Created: February 3, 2026 Last Updated: February 6, 2026 Server: box-cli-01 (/home/dalim/LOREAL-AIRTABLE/)