Designing a PostgreSQL Schema with TypeORM That Survives Growth

TypeORM makes it easy to describe a table as a decorated class and move on. The schema mistakes that hurt later are rarely about TypeORM syntax — they are about constraints you never asked the database to enforce, and migrations that assumed the table would stay small forever.

Let the database enforce what the application cannot guarantee alone

An @Entity() class documents intent; a CHECK constraint, a UNIQUE index, or a FOREIGN KEY enforces it regardless of which code path writes the row — an admin script, a migration backfill, or a bug in a service you have not touched in months. If two values must relate a certain way (a sale price lower than the list price, a status enum that only moves forward), put that rule in the migration, not only in a DTO validator.

@Entity('products')
@Index(['slug'], { unique: true })
@Index(['sku'], { unique: true })
export class ProductEntity {
  @Column({ name: 'base_price', type: 'bigint' })
  basePrice: string; // money as integer minor units, never float

  @Column({ name: 'compare_at_price', type: 'bigint', nullable: true })
  compareAtPrice: string | null;
}

// migration
await queryRunner.query(`
  ALTER TABLE "products"
    ADD CONSTRAINT "CHK_products_compare_at_price_above_base"
    CHECK ("compare_at_price" IS NULL OR "compare_at_price" > "base_price")
`);

Money is an integer, not a float

Storing prices as numeric(10,2) or, worse, a JavaScript number, invites rounding drift the moment you apply a discount or split a payment. Store minor units (cents, or đồng, which has no fractional unit) as bigint, and let the presentation layer decide how to format it. TypeORM maps bigint to a string in JavaScript specifically so you are not tempted to do arithmetic on it with floating point.

Opaque, sortable primary keys beat auto-increment

A serial integer primary key leaks how many rows exist and is trivial to enumerate; a random UUID fixes both but destroys index locality because inserts land in random b-tree pages. A time-prefixed, application-generated string id — prod_01hz... — keeps inserts roughly sequential for the index while staying opaque and unguessable, and it doubles as a self-describing type tag in logs.

• Index every foreign key column explicitly — Postgres does not create one automatically the way some other databases do.
• Prefer varchar(n) with an explicit, generous length over unbounded text for anything used in a WHERE or ORDER BY, so the planner has better statistics.
• Use partial indexes (WHERE deleted_at IS NULL) for soft-deleted tables instead of indexing rows nobody queries.
• Add CHECK constraints for anything a bug could otherwise write silently wrong, like a discount that exceeds the price.
• Never let an ORM "sync" mode touch a database with real data — migrations are the only path that is reviewable and reversible.

Migrations are a one-way door — write them like it

A migration that runs once against a table with three test rows and a migration that runs against a table with ten million rows are different engineering problems. Adding a NOT NULL column to a large table needs a default and a backfill step, not a bare ADD COLUMN ... NOT NULL, which locks the table for the length of the rewrite. Splitting a migration into add-nullable → backfill → add-constraint keeps each step fast and each step independently revertible.

Always write the down() migration, even for a project that never plans to roll back in production. It is the fastest way to catch a migration that silently depends on data it should not.

Relations: know when NOT to use a join table

TypeORM makes @ManyToMany with an auto-generated join table one decorator away, but the moment that relationship needs its own attributes — a sortOrder, a createdAt, a role — you need an explicit join entity like ProductCategoryEntity with its own @Entity(), its own composite unique index, and its own repository. It is more boilerplate up front and considerably less painful than migrating away from an implicit join table later.

Soft deletes: a column, not a separate table

TypeORM's @DeleteDateColumn() turns repository.softDelete() into an UPDATE ... SET deleted_at = now() instead of a DELETE, and every find() call automatically adds WHERE deleted_at IS NULL unless you explicitly ask for trashed rows with withDeleted: true. That single column preserves the audit trail, lets a "restore" feature exist without a backup, and keeps foreign keys pointing at rows that still physically exist.

@Entity('products')
export class ProductEntity extends StringIdBaseEntity {
  @DeleteDateColumn({ name: 'deleted_at', type: 'timestamptz', nullable: true })
  deletedAt: Date | null;
}

// Every unique constraint that should allow re-using a slug after deletion
// must be scoped to live rows only:
await queryRunner.query(`
  CREATE UNIQUE INDEX "UQ_products_slug_live"
    ON "products" ("slug")
    WHERE "deleted_at" IS NULL
`);

A plain UNIQUE index on slug without the WHERE deleted_at IS NULL clause blocks exactly the scenario soft delete is meant to allow: deleting a product and creating a new one with the same slug. The partial index is what makes soft deletion and unique slugs compatible instead of fighting each other.

• Every relation that should not resurrect a soft-deleted parent needs an explicit query — TypeORM does not cascade the "deleted" filter through joins automatically.
• A background job that permanently purges old soft-deleted rows still needs to exist; "soft" delete without a retention policy just delays the storage problem.
• Foreign keys from a soft-deleted row to a live one are still enforced by Postgres — soft delete only changes what queries see, not what the database allows to reference what.

JSONB versus normalized columns: a real decision, not a default

A specification column typed jsonb is the right call for genuinely variable, product-specific attributes — one product needs { stack: [...] }, another needs { pages: 64 } — where a normalized table would need a new column (or a sparse EAV table) for every attribute any product might ever have. It is the wrong call the moment a field needs to be queried, filtered, or joined on regularly; a status or price living inside JSONB loses indexability and type checking that a real column gives for free.

-- A GIN index makes containment queries on jsonb usable at scale
CREATE INDEX idx_products_specification_gin
  ON products USING gin (specification);

-- Now this is index-assisted, not a full scan:
SELECT * FROM products WHERE specification @> '{"framework": "NestJS"}';

The rule of thumb that holds up in practice: if a field appears in a WHERE, an ORDER BY, or a foreign key relationship, it earns a real column. If it only ever gets read back whole and displayed, JSONB is the right amount of structure — no migration required every time the product team wants to add one more optional attribute.

When to reach for a database view instead of application code

A query joined across five tables and repeated, slightly differently, in three different service methods is a sign the join itself deserves to live in the database as a view, not just as a repeated TypeORM query builder chain. A view does not change what data is stored, only how a common shape is exposed — and unlike duplicating the join logic in TypeScript, a materialized view can also be refreshed on a schedule for a report that does not need to be perfectly real-time.

The trade-off is discoverability: a view hides the underlying joins from anyone reading the TypeORM entities alone, so it earns its place only when the query it replaces is genuinely reused, not for a one-off report that a single service method already expresses clearly.

Conclusion

A schema that survives growth is not the one with the cleverest TypeORM decorators — it is the one where the database itself refuses invalid states, where money is an integer, where every foreign key has an index, and where migrations are written for the table size you will have in a year, not the one you have in a test database today.