Designing a 185-Table Database Schema: Lessons from Building Nexural

When people hear "185 database tables," they assume complexity for complexity's sake. But every table exists because a business requirement demanded it.

Here's how I designed the Nexural schema — the decisions that worked, the ones I'd change, and the patterns that scale.

Phase-Based Schema Design

I didn't design 185 tables on day one. The schema grew across 7 phases, each adding a domain:

Each phase had its own migration batch. I never modified tables from a previous phase during a new phase's development. This kept deployments safe.

The Three Rules I Followed

Rule 1: Normalize Everything Except Hot Paths

The canonical data is always normalized. users → subscriptions → plans is fully normalized with foreign keys. No shortcuts that could create billing bugs.

But dashboard queries hit denormalized views:

CREATE MATERIALIZED VIEW dashboard_summary AS
SELECT
  u.id,
  COUNT(DISTINCT s.id) as strategy_count,
  COUNT(DISTINCT a.id) as active_alerts,
  MAX(t.executed_at) as last_trade,
  SUM(p.unrealized_pnl) as total_pnl
FROM users u
LEFT JOIN strategies s ON s.user_id = u.id
LEFT JOIN alerts a ON a.user_id = u.id AND a.status = 'active'
LEFT JOIN trades t ON t.user_id = u.id
LEFT JOIN positions p ON p.user_id = u.id AND p.status = 'open'
GROUP BY u.id;

This view refreshes every 60 seconds. Dashboard loads in <50ms.

Rule 2: Row-Level Security on Every Table

Supabase RLS means the database enforces access control, not just the API. Even if my API has a bug, a user can never see another user's data:

-- Every table gets this pattern
ALTER TABLE strategies ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can only see their own strategies"
  ON strategies FOR ALL
  USING (auth.uid() = user_id);

This saved me from 3 access control bugs during development that would have been security incidents in production.

Rule 3: Soft Delete Everything Financial

Nothing in the trading or subscription domains ever gets hard-deleted:

ALTER TABLE trades ADD COLUMN deleted_at TIMESTAMPTZ DEFAULT NULL;
ALTER TABLE subscriptions ADD COLUMN deleted_at TIMESTAMPTZ DEFAULT NULL;

-- All queries filter on deleted_at IS NULL by default
CREATE VIEW active_trades AS
SELECT * FROM trades WHERE deleted_at IS NULL;

Audit trails matter in fintech. If a customer disputes a trade, I need the full history.

Migration Strategy

Every migration follows this pattern:

1. Write the migration SQL — always reversible (UP and DOWN)
2. Test against a copy of production data — catch constraint violations
3. Run in a transaction — all or nothing
4. Verify with a smoke test — automated query that checks the schema matches expectations

I use numbered migration files: 001_create_users.sql, 002_add_subscriptions.sql, etc. No ORM migrations — raw SQL gives me full control.

What I'd Do Differently

Use database schemas (Postgres namespaces) per domain. Instead of 185 tables in the public schema, I'd have trading.positions, auth.profiles, analytics.events. This makes it clearer which domain owns which table.

Add created_by and updated_by columns from the start. I added these retroactively to 40 tables. Should have been in the base table template.

Implement change data capture earlier. For analytics, I needed "what changed and when." CDC from day one would have saved me from building a custom audit log table.

The Bottom Line

185 tables isn't complex — it's organized. Each table has one job, one owner, and clear relationships. The schema document was 40 pages before I wrote the first migration.

If you're building something ambitious, invest in schema design first. Refactoring a database is 10x harder than refactoring code.