Database Integration Patterns

Google Sheets and Airtable are excellent for simple automation storage — they're accessible, easy to share, and require no infrastructure. But they have hard limits. Google Sheets starts to crawl above 50,000 rows. Airtable has record limits on its free and low-tier plans. Neither supports proper transactions, concurrent writes, or the complex queries you need for a real data model. You should move to a real database when: you have more than 10,000 rows of automation data, you need to JOIN data across multiple tables, you need concurrent writes (multiple workflow executions writing simultaneously), or you need complex aggregations that Sheets/Airtable can't handle. For automation use cases, Postgres on Supabase is the best starting point. It's free up to generous limits, has a great web UI, supports real-time subscriptions, provides a REST API alongside direct database connections, and has excellent n8n integration. Supabase is also a legitimate product database — so your automation storage and your application database can be the same. N8n connects to Postgres (and MySQL, SQLite, MongoDB) via its database nodes. You can run any SQL query in a workflow: SELECT to fetch data, INSERT to write data, UPDATE to modify existing records, and DELETE to clean up. Parameterized queries are essential — never build SQL strings by concatenating user data, or you're vulnerable to SQL injection even in internal workflows.

The schema you design for your automation data affects how easily you can query it later. Here are the patterns that work well for automation use cases. Always include these columns in automation tables: id (UUID, primary key, auto-generated), created_at (timestamp, auto-set to now()), updated_at (timestamp, auto-updated on change), status (enum: pending/processing/complete/failed), and source (which workflow or system created this record). For lead capture and CRM data: leads table with columns for email (unique), name, source, status, metadata (JSONB — stores arbitrary additional fields without schema changes), and created_at. For workflow state and deduplication: processed_events table with event_id (the unique ID from the external system), event_type, processed_at, and result. Before processing any webhook event, check this table — if the event_id already exists, skip it. This is idempotent processing. For job queues: jobs table with status (queued/running/done/failed), payload (JSONB), attempts, last_error, run_after (timestamp for delayed execution). Your n8n polling workflow selects 'WHERE status = queued AND run_after <= now() ORDER BY created_at LIMIT 10', marks them as running, processes them, and updates status. Use JSONB columns liberally for fields that vary between records. JSONB in Postgres is queryable — you can filter, sort, and index on JSONB fields — so you don't sacrifice query power by using it.