<aside>

Goal

Automate the ingestion of POS customer exports from S3 into Supabase (CRM), with validation, rejection logging for audit, and Slack notifications.

Solution

• Listens to S3 events via webhook (from POS bridge).
• Downloads the CSV, parses it to records, and validates identifiers.
• Valid contacts (Email / Phone) are formatted and written to CUSTOMERS.
• Invalid contacts are normalized and written to CUSTOMERS_REJECTS (full raw payload + reason).
• Sends Slack confirmations:
  • Processed summary.
  • Rejections summary (ready for audit).
• Optional: dead-letter CSV to S3, upsert mode, batching, and RLS for production.

Steps

1. Receive the S3 Event

Node: Receive S3 Event (Webhook)

Purpose: Triggers when the POS places a file in S3. Carries bucket, key, etag, presignedUrl, etc.

Configuration:

• Method: POST
• Path: /webhook/s3-event
• Authentication: None (secured by presigned URLs)

2. Download CSV from S3

Node: S3: Download CSV

Purpose: Uses the bucket and key from the webhook to fetch the file.

Configuration:

• Operation: Download File
• Bucket Name: {{ $json.Records[0].s3.bucket.name }}
• File Key: {{ $json.Records[0].s3.object.key }}
• Authentication: AWS credentials configured

3. Parse CSV into Items

Node: Parse CSV to Items (Extract From CSV)

Purpose: Converts the CSV to an array of JSON items.

Configuration:

• Input Data: File content from S3 download
• Include Headers: True
• Delimiter: Comma (,)
• Quote Character: Double quote (")

4. (Test Only) Limit

Node: Limit to 100 Items

Purpose: Keeps the workflow quick and safe while testing.

Configuration:

• Max Items: 100
• Skip: 0
• Keep First: True

5. Validate Contact

Node: Valid Contact? (Email / Phone)

Purpose: Expression checks: Email || Phone.

Configuration:

• Expression: {{ $json.Email || $json.Phone }}
• Routes:
  • TRUE → valid branch
  • FALSE → rejections branch

6. Valid Branch

Loop Over Items

Node: Loop Over items

Purpose: Processes each valid record individually for better error handling.

Configuration:

• Mode: Each Item
• Batch Size: 10

Format for Supabase

Node: Format for Supabase (single Code node)

Purpose: Normalizes fields for database insertion.

Configuration:

• Mode: Run Once for Each Item
• Code:

javascript

return { json: { contact_key: $json.CustomerID || $json.Email || $json.Phone, email: $json.Email, phone: $json.Phone, first_name: $json.FirstName, last_name: $json.LastName, address: $json.Address, postal_code: $json.PostalCode, date_of_birth: $json.DateOfBirth, gender: $json.Gender, s3_key: $('Receive S3 Event').first().json.Records[0].s3.object.key, etag: $('Receive S3 Event').first().json.Records[0].s3.object.eTag } };

Supabase: Create Row

Node: Supabase: Create Row

Purpose: Inserts valid records into the main customers table.

Configuration:

• Operation: Insert
• Table: public.CUSTOMERS
• Data: All fields from Format node
• On Conflict: Do nothing (prevents duplicates)

Send Valid Records Message

Node: Send a message (Slack)

Purpose: Notifies about successfully processed records.

Configuration:

• Channel: #data-imports
• Message: From the done output of the loop with processed counts
• Webhook URL: Slack app webhook

7. Rejections Branch

Loop Over Rejections

Node: Loop over rejections

Purpose: Processes each rejected record for audit logging.

Configuration:

• Mode: Each Item
• Batch Size: 10

Format for Supabase (Rejects)

Node: Format for Supabase (Rejects)

Purpose: Builds rejection records with full audit trail.

Configuration:

• Mode: Run Once for Each Item
• Code:

javascript

return { json: { contact_key: $json.CustomerID || 'unknown', first_name: $json.FirstName, last_name: $json.LastName, address: $json.Address, postal_code: $json.PostalCode, gender: $json.Gender, reason: (!$json.CustomerID && !$json.Email && !$json.Phone) ? 'missing_id_email_phone' : (!$json.CustomerID) ? 'missing_customer_id' : (!$json.Email && !$json.Phone) ? 'missing_email_phone' : 'validation_failed', raw: JSON.stringify($json) } };

Supabase: Create Row – Rejections

Node: Supabase: Create Row – Rejections

Purpose: Inserts rejected records into audit table.

Configuration:

• Operation: Insert
• Table: public.CUSTOMERS_REJECTS
• Data: All fields from Format node
• On Conflict: Do nothing

Send Rejections Message

</aside>

📂 Download & Explore the Workflow:

S3-Supabase Import Automation (JSON_file).json

View GitHub Repository :

S3-Supabase/S3-Supabase Import Automation (JSON_file).json at main · AlvLeoAI/S3-Supabase