Zoho CRM Implementation
Zoho One Consulting
Zoho Books Setup
Zoho Analytics & BI
Data Migration Services
Zoho Desk Automation
Zoho Creator Development
Zoho Automation Services
Zoho Widget Development
Zoho Extension Development
Deluge Development
Zoho Integration Services
Zoho API IntegrationPostgreSQL Database Design & Development
Schema design, Prisma, migrations planned so the app keeps running, and queries that hold up as the tables grow - from developers who design the database before the screens.
PostgreSQL databases designed for data that has to stay right.
Most business software is a database with screens on top. The screens get the attention, but the database decides whether an invoice total can drift from its line items, whether two customers can share an email address, and whether next year's report means what this year's did. Those decisions are cheap to get right early and expensive to correct once real data depends on them.
We build on PostgreSQL by default, with Prisma as the TypeScript data layer: one schema describing your models, a typed client generated from it, and every change recorded as a reviewable SQL migration. Where Prisma is the wrong tool, we write the SQL by hand and version it with everything else.
The work comes in three shapes: a new application, where the schema is designed before the interface; an existing one that has slowed down or grown untidy, where we measure before changing anything; and data in spreadsheets, MySQL or Zoho that has to move into PostgreSQL intact.
What's included
- PostgreSQL schema design & data modeling
- Prisma schema, client & migrations
- Expand/contract migrations & backfills
- Query tuning, indexing & plan analysis
- Connection pooling for serverless & scaled apps
- Spreadsheet, MySQL & Zoho data migration
- Multi-tenant design & row-level security
- Backups, restore tests & environment setup
PostgreSQL database design for how your data behaves
A schema is a set of business decisions the machine will enforce: what exists, how it relates and what must always be true. We design it from how the data behaves - who creates a record, who edits it, which numbers have to add up - rather than from the screens, which change far more often than the facts underneath them.
Constraints in the database, not only in the code
Not-null, unique, foreign-key and check constraints mean no import script or forgotten code path can save an order without a customer. Validation in the app gives users friendly messages; the database is the guarantee.
Types that match the data
Money in exact numeric types or integer cents, never floating point. Timestamps as exact moments, not local clock times. JSONB for genuinely free-form data, and real columns for anything you filter or report on.
History you can answer questions from
Where it matters - prices, statuses, approvals - history is kept rather than overwritten, so 'what was the price when this order was placed?' has an answer.
Prisma in practice, and when to drop to raw SQL
Prisma is our default data layer for TypeScript projects on PostgreSQL. Its generated client turns a query for a field that doesn't exist into a compile error rather than a customer-facing one, and Prisma Migrate writes each change as a plain SQL file that we read - and edit where needed - before it reaches production. Because an ORM makes it easy to write a tidy line that quietly runs a query per row, we also read the SQL it sends on the paths that matter.
Where Prisma earns its place
Everyday reads and writes, relations and transactions. Typed results flow into NestJS services and Next.js pages, so a renamed field becomes a compile error everywhere it is used.
Where we write SQL instead
Reports built on window functions or heavy aggregation, large bulk updates, and anything needing exact control of the statement - as parameterized raw queries, kept in one place and tested.
What the schema file can't express
Row-level security policies, triggers and some kinds of constraint and index sit outside Prisma's schema language, so they go into the migrations as hand-written SQL - versioned and reviewed, not applied by hand and forgotten.
Changing a live schema safely
During a deploy, old and new versions of an application can run side by side, and a migration that renames a column breaks whichever one wasn't expecting it. So any change that would break running code is split into steps that each leave the system working.
Expand, then contract
Add the new column alongside the old one, write to both, backfill, move reads across, and drop the old column in a later release. More releases than a single migration - but the running code and the schema never disagree.
Backfills in batches
Existing rows are filled in small, resumable batches outside the deploy, not by one long statement that holds locks and can't be stopped halfway.
Locks checked before they bite
A statement waiting for a table lock can hold up every query queued behind it, so indexes on large tables are built concurrently, constraints on them are validated after they are added, and migrations run with a lock timeout.
A written way back
Dropping a column takes its data with it, so not every migration can be reversed. Each change has its rollback plan written down before it runs, and the destructive step comes last, after the new path has worked in production.
Indexes, query plans and connection pooling
A slow database is usually a handful of slow queries. We find them by measuring - request timings, then the statements taking the most total time (from pg_stat_statements, where your host provides it), then their plans with EXPLAIN ANALYZE - rather than adding indexes on a hunch.
Indexes that match the queries
Composite and partial indexes shaped to the filters you actually run, and an index on each foreign key you join through - PostgreSQL doesn't create those on its own. Every index slows writes a little, so unused ones go.
Reading the plan
EXPLAIN ANALYZE shows a full-table scan, a badly misjudged row count or a sort spilling to disk. Stale statistics and functions wrapped around indexed columns are common causes, and cheap to fix once you can see them.
Pagination that holds up
OFFSET pagination slows down as people page deeper, because skipped rows are still read. Cursor-based pagination, which Prisma supports, costs about the same on page 500 as on page 1.
Connection pooling
Each PostgreSQL connection is a separate server process, and the number allowed is limited. Serverless functions and multiple app instances can exhaust them, so a pooler - PgBouncer or your host's own - sits in front, with Prisma's pool sized to match.
Migrating data into PostgreSQL from spreadsheets, MySQL or Zoho
Whatever the source, data lands in staging tables first, where it is cleaned, mapped and validated before the real tables are filled in dependency order - customers before orders. Rows that fail are reported, not quietly dropped. Then we prove it worked: counts per table, totals on money columns, sample records compared field by field, and every relationship confirmed to resolve.
Spreadsheets
The structure lives in how people have used the columns - dates in three formats, one customer spelled five ways. We turn that into typed tables with agreed rules for duplicates, and list every assumption for you to check.
MySQL to PostgreSQL
Mostly straightforward, with known traps. MySQL compares text case-insensitively by default and PostgreSQL doesn't, which changes what a unique email means. Zero dates and unsigned columns have no direct equivalent, and carried-over IDs need their sequences reset or the next insert collides.
Zoho exports
Zoho is the other half of our work, and its data model maps cleanly when you know it: lookups become foreign keys, subforms child tables and multi-select fields proper relations. Zoho record IDs are kept, so every row traces back - or stays in sync, if both systems remain in use.
Multi-tenant PostgreSQL and row-level security
If one application serves many client organizations, its worst possible bug is showing one customer another customer's data. How tenants are kept apart is decided in the schema, early, because changing it later touches every table and every query.
Shared tables with a tenant key
Every tenant-owned row carries a tenant ID, and uniqueness is scoped per tenant, so two customers can each have an invoice 1001. It is the cheapest design to run, but it depends on every query filtering correctly - which is why it gets a backstop.
Row-level security as the backstop
PostgreSQL policies limit every query to the current tenant's rows, so a forgotten filter returns nothing rather than someone else's records. It needs care with connection pooling and with the role the app connects as, and we test it like any other feature.
A schema or database per tenant
Stronger separation, at the cost of running every migration once per tenant. Reasonable for a handful of large clients; painful for thousands of small ones.
Backups, environments and access
A backup that has never been restored is a hope, not a backup. We set up automated backups with point-in-time recovery where your host offers it, then restore one into a separate environment before calling the job done - so you know recovery works, and roughly how long it takes.
Staging is a separate database with realistic data, anonymized where it is personal, so a migration rehearsed there behaves much as it will in production. The app connects with a role that can change data but not the schema, and production access stays with the people who need it, in accounts you own.
What you get out of it
Outcomes that matter to your business - not just features.
Rules enforced at the source
Constraints in the database stop bad data that application code lets through.
Schema changes in safe steps
Migrations staged so a deploy doesn't lock the tables your users are working in.
Queries that stay fast
Indexes and query plans checked against realistic volumes, not ten test rows.
Imports you can verify
Migrated data reconciled against its source before anyone relies on it.
How we deliver this service
Map the data
What the records are, how they relate, who changes them, and which rules must never be broken.
Design the schema
Tables, keys, constraints and indexes agreed and walked through with you before any code depends on them.
Build the data layer
Prisma models and migrations reviewed as SQL, with hand-written SQL where the ORM is the wrong tool.
Load & reconcile
Existing data moved through staging tables, validated, and checked against the source with counts and totals.
Measure & hand over
Slow queries found against realistic volumes, a backup restored to prove it works, and the decisions written down.
You might also need
PostgreSQL & Prisma - FAQs
The questions we are asked most about PostgreSQL & Prisma.
Ready to start? Let's scope it.
Book a free discovery call and we'll map out exactly how this can work for your business.