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 problemAt 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 IsolationTable does not include a company_id (tenant identifier)Tenant scoping must be handled entirely at the application layerViolates core multi-tenant design principle: enforce isolation at the database level whenever possibleIncreases risk of cross-tenant data leakagePrevents tenant-aware indexing and constraint enforcementMakes horizontal partitioning or sharding by tenant significantly harder in the futurePrinciple: In multi-tenant SaaS, tenant identity should be first-class in schema design, not implicit2. Ownership Coupled Solely to user_idSchema assumes user_id is globally unique and inherently tenant-scopedSecurity and data isolation depend entirely on application correctnessNo composite uniqueness such as (company_id, user_id)Creates tight coupling between identity design and notification storagePrinciple: Database design should reduce reliance on application-level guarantees for critical isolation rules3. Lack of Broadcast-Oriented ArchitectureEach notification must be inserted per userA broadcast to 10,000 users produces 10,000 duplicated rowsIncreases write amplification and storage costPrevents content-level reuse and central managementPrinciple violated: Separate content from delivery stateDesign does not scale efficiently under high fan-out scenarios4. No Separation Between Content and Delivery StateTitle and body are stored directly in the user rowNo central notification entity existsUpdating a broadcast message requires updating N rowsAnalytics (e.g., total reads per notification) becomes expensive and inconsistentModern messaging systems typically normalize into:notifications (content)recipients (user-specific state)Current schema conflates both concerns5. Missing Notification Classification (type)No logical grouping mechanism existsCannot implement user-level preferences per notification typeCannot power template-based renderingLimits extensibility as the product evolvesA type column is foundational for:Preference systemsTemplate resolutionReporting and analyticsFeature flags6. No Multi-Channel Delivery ModelingSchema assumes only in-app notificationsNo representation of email, SMS, push, etc.No delivery status trackingNo retry or failure state modelingProduction systems: Notification design is typically multi-channel by defaultAbsence of channel modeling constrains future system evolutionThis 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 RemarksThe 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 ThoughtsThis 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.