SaaS Data Model Design: Early Decisions That Save Painful Rewrites
Get the core data model decisions right early: entities, identifiers, money, time, history and deletion, so new features do not need schema surgery.
This is for CTOs and founding engineers designing a new SaaS product or untangling an early schema that is starting to hurt. Weak data model choices rarely fail on day one; they show up later as reporting that cannot be trusted, invoices that are off by small amounts and features that need months of migration. This post covers the core decisions to make deliberately, a short data model guide and a review habit that keeps the schema healthy.
A SaaS product stored a customer's address directly on each invoice row, prices as floating point numbers and dates in server time. Two years later, finance asks for revenue by state for GST reporting, and support asks why some invoices are off by a paisa. The data needed is either missing, inconsistent or tied to the wrong time zone for customers abroad.
Early schemas are usually designed around the first screens, under deadline pressure. One person's choices become permanent, and there is no written guidance for the next engineer. Different tables handle money, time, deletion and history differently. Each new feature, report or integration then requires careful archaeology, workarounds and risky migrations that could have been avoided with a few deliberate early decisions.
An early-stage accounting SaaS in Kolkata found that invoice totals occasionally differed from customer ledgers by tiny amounts, and changes to a customer's GSTIN altered old invoices. The tech lead wrote a short data model guide: money in integer minor units with currency, timestamps in UTC with stored time zones, and snapshots of billing details on every invoice. New tables were reviewed against the guide, and existing invoice data was migrated in stages using expand and contract. Rounding complaints stopped, historical invoices stayed fixed after customer edits, and new reports for finance could be built without manual corrections.
Should we use soft deletes or hard deletes?
Use soft deletes or history tables where you need audit trails or recovery, such as invoices and permissions. Use hard deletes, or anonymisation, where data protection rules require erasure. Check DPDP Act obligations with your compliance team.
How should we store money in a SaaS database?
Store amounts as integers in the smallest currency unit, such as paise, or as fixed-precision decimals, always with a currency code. Never use floating point types for money, because rounding errors accumulate.
Should we use UUIDs or sequential IDs?
Many teams use sequential IDs internally for performance and non-guessable IDs such as UUIDs for anything exposed in URLs or APIs. Whatever you choose, apply it consistently and never expose IDs that reveal business volumes.
How do we fix a data model that is already causing problems?
List the specific pain points, agree target conventions in a short guide, apply them to all new tables immediately, and migrate the most painful existing tables in stages using expand and contract rather than one big rewrite.
Want this fixed in your business?
Send the workflow. You get a scope and a fixed price within two working days — or a straight answer that it isn't a fit.