How This Assessment Was Generated
A repeatable pipeline reads both schemas, runs six analyzers, and outputs structured reports. Re-run it whenever schemas change to track drift, formula sprawl, and overlap evolution.
Current Numbers & Data Analysis
Table counts, computed field ratios, complexity tiers, pattern reuse, and cross-system overlap — all auto-generated from the latest schema analysis.
Side-by-Side Comparison
| Dimension | PostgreSQL ERP (Ecosys) | Airtable CRM |
|---|---|---|
| Foreign Keys | Zero By convention (soft-delete compatible) | Full Linked records |
| Referential Integrity | None enforced at DB level | Enforced by Airtable |
| Computed Logic | None in DB In code (GIT) | Thousands of computed fields + Code (GIT) |
| Soft Deletes | Every table (meta_deleted) | Native trash |
| Strengths | Supply chain, suppliers, logistics, pricing | Sales, contracts, inventory ops, forecasting |
| Primary Users | Engineering, sourcing team | Sales, ops, finance |
See current table counts and field totals in the Analysis Report.
Technology Stack
Two fully independent stacks. Ecosys is a custom-built SRM/ERP application with a traditional LEMP architecture. Airtable is a no-code platform where business logic lives in automations and formulas.
Ecosys (SRM/ERP)
| Cloud | DigitalOcean droplets |
| Database | PostgreSQL 16 |
| Backend APIs | REST/JSON · LEMP stack · PHP 8.4 |
| API routing | Internal router with per-app endpoint ACLs and CORS policy |
| Frontend | Svelte JS (static build) · Nginx |
| Schema mgmt | Object Model in PostgreSQL, separated from business logic |
| User mgmt | Custom coded (PHP) |
| Code mgmt | GitHub |
Airtable (CRM)
| Cloud | Airtable (managed) |
| Database | Airtable (automatic backup, encryption) |
| Business logic | Airtable Automations + a few JavaScript scripts |
| API | Airtable REST API (5 req/s limit) |
| Frontend | Airtable Interfaces (no-code) |
| Schema mgmt | Object Model + computed fields (derived, UI, presentation layers) inline |
| User mgmt | Airtable native (granular, no-code) |
| Code mgmt | GitHub (for scripts) · no version control on Airtable config |
Ecosys already has a data access layer. The PHP REST APIs are a working API layer with routing, per-app permissions, and CORS. Each Ecosys application communicates only with its allowed endpoints. Under Path B, Hasura would sit alongside (and eventually replace) these PHP APIs as the unified query layer for both PostgreSQL and synced Airtable data.
Schema Methodology
Airtable: Object Model + Presentation
Because of the no-code approach, the schema mixes two things: (a) the Object Model (core business entities and relationships) and (b) hundreds of additional fields — derived values, UI helpers, presentation layers, report columns. This is why tables have 400+ fields.
A Python script reads Airtable metadata into a Prisma schema file organized by object model, business logic, and presentation layers. Work in progress.
Ecosys: Object Model Only
PostgreSQL stores only the Object Model — the normalized schema of core business entities. All business logic, derived values, and presentation live in the PHP API layer and Svelte frontend. This is a cleaner separation, but means the business logic is scattered across application code.
The ideal: overlapping entities between Airtable and Ecosys should be identical at the Object Model level — same structure, same truth, different presentation layers.
Airtable Computed Field Analysis
Airtable is a write-first database: every value a user sees in an interface must exist as a field on the record. A column in a grid view = a stored field. To build rich CRM interfaces — sales dashboards, availability grids, forecast summaries — you need hundreds of derived fields per table. This is by design, not a mistake.
PostgreSQL is the opposite — a read-first database. Derived values live in views, computed at query time. The same "Total Revenue" that requires a formula field in Airtable is a column in a SQL view in PostgreSQL. It doesn't exist in the table. The underlying schema stays lean.
This is the write-first trade-off. Tables aren't bloated — they're doing double duty as both the Object Model and the presentation layer. The high ratios reflect the richness of the CRM interfaces the sales and ops teams use daily. Under consolidation, the Object Model (the 10–30% that's real data) moves to PostgreSQL. The derived values become SQL views — computed on read, not stored on write. The computed fields don't "migrate" — most simply disappear because PostgreSQL doesn't need them to exist as physical fields.
Presentation Layer Density, Complexity Tiers & Pattern Reuse
Per-table computed ratios, formula complexity breakdown, tier counts, and the full pattern reuse table are in the Analysis Report — auto-generated from current schema data.
Key takeaway: The migration is dramatically smaller than the raw numbers suggest. The vast majority of computed fields are presentation-layer operations that disappear in a read-first database — they become view columns or JOINs, not things you build. Only a small percentage contain real business logic that needs careful analysis, and many of those are packaging variants of the same pattern. The real migration effort is ~100 unique formulas, not thousands.
Ecosys PostgreSQL Structural Analysis
The schema uses zero foreign keys, zero check constraints, and zero enums. Combined with universal soft deletes and _id naming conventions, this points to an architecture deliberately built for flexibility — and the data proves the convention works.
Zero Foreign Keys
All relationships use _id naming conventions. With soft deletes on every table, traditional FKs would fight the architecture — ON DELETE RESTRICT blocks soft-deleted parents.
Zero Check Constraints
No validation that prices are positive, quantities make sense, or status fields contain valid values. These can be added without conflicting with the flexible design.
Zero Enums
Status fields use raw integers or strings. No documentation of valid values at the schema level. Enums would add documentation and safety with no downside.
Universal Soft Deletes
Every table has meta_deleted, meta_edited, and meta_edited_by. This uniform convention explains the FK-free design — the schema treats deletion as a state change, not a removal.
Convention Works
Despite no FK enforcement, the orphan audit found only 9 broken references across 28 tables and 30 relationships. The naming convention is remarkably effective.
PostGIS Installed
The cust_gps field on customers is geospatial. Enables region mapping and delivery optimization.
Improvement areas that don't fight the design: Add FKs only to static reference tables
(ports, countries, units, distro centers) that never get soft-deleted. Add check constraints and enums everywhere —
these validate data without affecting deletion patterns. Add partial indexes on WHERE meta_deleted = false for
performance on "active records" queries.
Referential Integrity Audit — 30 Relationships Checked
Full orphan audit results: 28 tables scanned, 29 clean relationships, 1 low-risk issue (9 orphaned rows). The database is cleaner than expected and ready for FK constraints.
Domain Ownership
Each system is the natural "system of record" for different business domains. This split is organic and reflects who uses what.
Ecosys (PostgreSQL) Owns
Supply chain, sourcing, suppliers, manufacturing plants, landed costs, freight rates, logistics, competitor analytics. ERP-only entities — see current count in the Analysis Report.
Airtable Owns
Customer contracts, sales orders, FIFO costing, inventory allocations, cash flows, forecasting, monthly summaries. Airtable-only entities — see current count in the Analysis Report.
This natural split matters. It means consolidation isn't the only answer. Each system could remain the authority for its domain — but today they're connected only through ad-hoc API scripts with no contracts, no error handling, and no audit trail. The architecture options explore how to formalize this connection.
Airtable-Only Entities
Business-critical entities exist only in Airtable with no PostgreSQL equivalent — contracts, sales orders, FIFO costing, inventory allocations, cash flows, and forecasting tables.
Full Entity Lists & Overlap Analysis
See the complete Airtable-only entity list, PG-only entities, and matched entities in the Analysis Report.
Key consideration: Contracts, sales orders, FIFO costing, and inventory allocations are mission-critical with no backup outside Airtable. Under Path C (consolidation), they move to PostgreSQL. Under Path A or B, they stay in Airtable but gain governed sync, ID mapping, and an audit trail through the proposed sync infrastructure.