When Bad Schema Design Costs You a Rewrite PT 1. of 3

The beginning.

A few days ago, I was forced to rewrite almost half of an application I’ve been building for over a year.

The application is written in Go, backed by PostgreSQL. It started as a personal experiment, I wanted to understand how SMTP servers worked behind the scenes. Over time, it evolved into my playground for learning backend architecture and best practices.

It wasn’t meant to be production-ready.

Until it was.

And that’s when the cracks started to show.

walk with me.

The problem

At some point, I decided to implement a notification system.

Simple enough, right?

Here was my “beautiful” database design:

CREATE TABLE IF NOT EXISTS user_notifications ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4 (), user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, title VARCHAR(255) NOT NULL, read_status BOOLEAN NOT NULL DEFAULT FALSE, additional_field TEXT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

At first glance, it looked clean.

Minimal.

Straightforward.

Functional.

But this application was multi-tenant. It was designed to support multiple companies, each with potentially thousands of users. And that’s where the design quietly failed. If you’ve been building systems long enough, you might already see the problem.

If not, walk with me.

1. Absence of Explicit Tenant Isolation

• Table does not include a company_id (tenant identifier)
• Tenant scoping must be handled entirely at the application layer
• Violates core multi-tenant design principle: enforce isolation at the database level whenever possible
• Increases risk of cross-tenant data leakage
• Prevents tenant-aware indexing and constraint enforcement
• Makes horizontal partitioning or sharding by tenant significantly harder in the future
• Principle: In multi-tenant SaaS, tenant identity should be first-class in schema design, not implicit

2. Ownership Coupled Solely to user_id

• Schema assumes user_id is globally unique and inherently tenant-scoped
• Security and data isolation depend entirely on application correctness
• No composite uniqueness such as (company_id, user_id)
• Creates tight coupling between identity design and notification storage
• Principle: Database design should reduce reliance on application-level guarantees for critical isolation rules

3. Lack of Broadcast-Oriented Architecture

• Each notification must be inserted per user
• A broadcast to 10,000 users produces 10,000 duplicated rows
• Increases write amplification and storage cost
• Prevents content-level reuse and central management
• Principle violated: Separate content from delivery state
• Design does not scale efficiently under high fan-out scenarios

4. No Separation Between Content and Delivery State

• Title and body are stored directly in the user row
• No central notification entity exists
• Updating a broadcast message requires updating N rows
• Analytics (e.g., total reads per notification) becomes expensive and inconsistent
  • Modern messaging systems typically normalize into:notifications (content)
  • recipients (user-specific state)
• Current schema conflates both concerns

5. Missing Notification Classification (type)

• No logical grouping mechanism exists
• Cannot implement user-level preferences per notification type
• Cannot power template-based rendering
• Limits extensibility as the product evolves
  • A type column is foundational for:Preference systems
  • Template resolution
  • Reporting and analytics
  • Feature flags

6. No Multi-Channel Delivery Modeling

• Schema assumes only in-app notifications
• No representation of email, SMS, push, etc.
• No delivery status tracking
• No retry or failure state modeling
• Production systems: Notification design is typically multi-channel by default
• Absence of channel modeling constrains future system evolution

This would have been enough if I was building a basic and simplistic CRUD application but for a production grade notification service. This would have been a disaster.

After much research and tinkering, I was able to design a proper notification system that truly scales.

I added comments to the DB schema to help out with my thought process during the design.

-- ─────────────────────────────────────────────-- NOTIFICATIONS-- Stores the notification content once.-- audience: 'individual' | 'broadcast'-- is_mandatory: skips preference checks if true-- ─────────────────────────────────────────────CREATE TABLE IF NOT EXISTS notifications ( id UUID PRIMARY KEY DEFAULT gen_random_uuid (), company_id UUID NOT NULL REFERENCES companies (id) ON DELETE CASCADE, type VARCHAR(50) NOT NULL, audience VARCHAR(20) NOT NULL DEFAULT 'individual' CHECK ( audience IN ('individual', 'broadcast') ), target_role VARCHAR(20) DEFAULT NULL CHECK ( target_role IN ('admin', 'user', 'all') ), title VARCHAR(255) NOT NULL, body TEXT NOT NULL, metadata JSONB NOT NULL DEFAULT '{}', link VARCHAR(255), priority SMALLINT NOT NULL DEFAULT 2 CHECK (priority BETWEEN 1 AND 3), -- 1=low 2=normal 3=high is_mandatory BOOLEAN NOT NULL DEFAULT FALSE, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW());
-- ─────────────────────────────────────────────-- NOTIFICATION RECIPIENTS-- Tracks per-user delivery + read state.-- Only created for 'individual' notifications-- or when a user interacts with a 'broadcast'.-- ─────────────────────────────────────────────CREATE TABLE IF NOT EXISTS notification_recipients ( id UUID PRIMARY KEY DEFAULT gen_random_uuid (), notification_id UUID NOT NULL REFERENCES notifications (id) ON DELETE CASCADE, user_id UUID NOT NULL REFERENCES users (id) ON DELETE CASCADE, company_id UUID NOT NULL REFERENCES companies (id) ON DELETE CASCADE, read_at TIMESTAMPTZ, dismissed_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CONSTRAINT uq_notification_user UNIQUE (notification_id, user_id));
-- ─────────────────────────────────────────────-- NOTIFICATION CHANNELS-- Tracks delivery status per channel per user.-- channel: 'in_app' | 'email' | 'sms' | 'push'-- status: 'pending' | 'sent' | 'failed'-- ─────────────────────────────────────────────CREATE TABLE IF NOT EXISTS notification_channels ( id UUID PRIMARY KEY DEFAULT gen_random_uuid (), recipient_id UUID NOT NULL REFERENCES notification_recipients (id) ON DELETE CASCADE, channel VARCHAR(20) NOT NULL CHECK ( channel IN ( 'in_app', 'email', 'sms', 'push' ) ), status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK ( status IN ('pending', 'sent', 'failed') ), sent_at TIMESTAMPTZ, error_message TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW());
-- ─────────────────────────────────────────────-- NOTIFICATION PREFERENCES-- Per-user opt-in/out per notification type.-- Ignored entirely when is_mandatory = TRUE.-- ─────────────────────────────────────────────CREATE TABLE IF NOT EXISTS notification_preferences ( id UUID PRIMARY KEY DEFAULT gen_random_uuid (), company_id UUID NOT NULL REFERENCES companies (id) ON DELETE CASCADE, user_id UUID NOT NULL REFERENCES users (id) ON DELETE CASCADE, type VARCHAR(50) NOT NULL, in_app BOOLEAN NOT NULL DEFAULT TRUE, email BOOLEAN NOT NULL DEFAULT TRUE, sms BOOLEAN NOT NULL DEFAULT FALSE, push BOOLEAN NOT NULL DEFAULT FALSE, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CONSTRAINT uq_user_notification_type UNIQUE (company_id, user_id, type));
-- ─────────────────────────────────────────────-- NOTIFICATION TEMPLATES-- Stores reusable copy per notification type.-- Variables use {{variable}} syntax.-- is_active lets you disable a template without-- deleting it.-- ─────────────────────────────────────────────CREATE TABLE IF NOT EXISTS notification_templates ( id UUID PRIMARY KEY DEFAULT gen_random_uuid (), company_id UUID REFERENCES companies (id) ON DELETE CASCADE, -- NULL company_id = global default template (system level) -- set tenant_id to override per tenant type VARCHAR(50) NOT NULL, title TEXT NOT NULL, body TEXT NOT NULL, variables JSONB NOT NULL DEFAULT '[]', -- documents what variables are expected -- e.g. ["count", "filename", "date"] is_active BOOLEAN NOT NULL DEFAULT TRUE, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CONSTRAINT uq_template_tenant_type UNIQUE (company_id, type));

Closing Remarks

The first version of my notification system was written to "make it work." The rewrite was written to make it scale, to make it safe, and to make it survive growth. The difference between the two designs wasn't complexity. It was intentionality.

In the first version, tenant isolation was assumed, broadcast was improvised, analytics was an afterthought, delivery channels were ignored, and scalability was theoretical.

In the rewrite, tenant identity became first-class. Content was separated from state. Broadcast became cheap. Channels became extensible. Preferences became enforceable. Analytics became natural.

The schema stopped being a table. It became an architecture.

Final Thoughts

This rewrite didn’t just change my schema, it changed how I approach system design. I learned that multi-tenant systems demand explicit isolation, that content must be separated from state early, and that fan-out is not a feature you “add later” but something you design for from day one.

Booleans are rarely enough. Assumptions are expensive. And the most dangerous systems aren’t the ones that crash, they’re the ones that work just well enough to hide structural flaws until scale exposes them. The rewrite cost me time, but it bought me clarity. And in software architecture, clarity is always cheaper than a production disaster.