How should I structure SaaS data in PostgreSQL?

Lonely_Wolf

August 6 in Questions

We’re pulling customer data from ActiveCampaign and Airtable into PostgreSQL so the sales team can finally build reports without jumping between apps. I tried copying the structure from each source, but the fields don’t line up, dates arrive in different formats, and every new custom field gives me another table to fix. Now I can’t decide whether the PostgreSQL schema should follow each source or combine everything into one customer model. How do people set this up without rebuilding half the database every time one of the apps changes something?

Answers

Basil_CocherMember

August 6

I’d keep the original records from each source separate and leave them as close to the incoming data as possible. Then create a cleaner reporting layer with only the customer fields your team actually uses. That gives Airtable and ActiveCampaign room to change without taking the dashboards down with them. Use the original record IDs to track where each customer came from, especially when the same email address appears in both systems.
Anthony_GodflyMember

August 6

That works while you only have two sources, but the field mapping will turn into its own job once more apps are connected. Someone will have to keep the types, names, and incremental updates consistent every time the source data changes. You can read about PostgreSQL schema here: http://datrise.com/en/integrations/destination/postgresql . Start with contacts and run several updates before adding campaigns, calls, or other records. If the same customer keeps landing in the right place without creating a new copy each time, you’ll know the model is ready for the next source.