Databases

I never took a database course. I never read a book about SQL. Everything I know about databases I learned by building real projects that needed to store real data. PostgreSQL is what I use everywhere, and I picked it up the same way I picked up everything else — by describing what I needed and letting Claude figure out the schema.

What IS a database?

Think of a database as a super-organized spreadsheet that your app can talk to. Seriously — that is the mental model. You have tables (like sheets in Excel), rows (like individual entries), and columns (like the headers). The difference is: a database can hold millions of rows without slowing down, multiple users can read and write at the same time, and your application talks to it with a language called SQL instead of clicking cells with a mouse.

When your app needs to remember something — a user signed up, a campaign was created, an email was opened — that information goes into the database. When your app needs to show something — the dashboard stats, the list of campaigns, the user's profile — it reads from the database. That is the entire concept. Everything else is details.

Why PostgreSQL?

There are many databases out there — MySQL, SQLite, MongoDB, and dozens more. I use PostgreSQL (often called Postgres) for everything, and here is why:

- It is completely free and open source — no license fees, no limits
- It is incredibly reliable — banks and governments use it, so your side project will be just fine
- It handles everything — simple data, complex queries, JSON data, full-text search, geolocation
- It has amazing documentation and Claude knows it inside out
- It scales — from a tiny app with 100 rows to a production system with millions

I tried MongoDB once early on because someone told me it was "easier for beginners." It was not. Postgres with tables and relationships makes way more sense for the kind of apps I build. Every project I have — SMTPCloud Dashboard, Quality Monitoring, LeadTool, MyVaultKeep.io — all run on PostgreSQL.

Tables, rows, columns — with real examples

Let me make this concrete. Here is what a users table looks like in my SMTPCloud Dashboard:

| id | email | password_hash | role | created_at |
|----|-------|---------------|------|------------|
| 1 | john@company.com | $2b$10$xK... | admin | 2024-01-15 |
| 2 | sarah@client.io | $2b$10$mP... | client | 2024-02-03 |
| 3 | mike@agency.com | $2b$10$qR... | client | 2024-03-20 |

- The table is called users
- Each row is one user
- Each column is a piece of information about that user
- The id column is unique — every row has a different number, and that is how the database tells them apart
- password_hash is not the actual password — it is an encrypted version. Never store real passwords.
- created_at is a timestamp so you know when they signed up

That is it. A table is just organized data. If you have ever used a spreadsheet, you already understand 80% of how databases work.

Relationships: the key concept that makes databases powerful

Here is the thing that makes databases more than just spreadsheets: relationships between tables. And honestly, the concept is simple.

A campaign BELONGS TO a user. A campaign HAS MANY recipients. A recipient HAS MANY tracking events (opens, clicks, bounces). That is it — that is database design.

In practice, this means the campaigns table has a column called user_id that points to a row in the users table. The campaign_recipients table has a column called campaign_id that points to a row in the campaigns table. These connecting columns are called foreign keys — they are just the database's way of saying "this thing belongs to that thing."

When I first learned this, it clicked immediately because it maps to how you naturally think about data. Users own campaigns. Campaigns contain recipients. Recipients generate events. The database just formalizes what you already know about your data.

How I design databases: let Claude do the schema

Here is my actual process. I do not sit down with a database design tool or draw entity-relationship diagrams. I tell Claude what I need in plain English, and Claude designs the schema.

Real example from SMTPCloud Dashboard. I said something like:

"I need to store campaigns that have recipients, and each recipient can have tracking events like opens and clicks. Campaigns belong to users. Users can also have API keys for programmatic access. I need to track which domain each campaign sends from, and domains need rate limits so we do not send too fast."

Claude turned that into proper SQL:
`
CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR UNIQUE, ...);
CREATE TABLE campaigns (id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id), ...);
CREATE TABLE campaign_recipients (id SERIAL PRIMARY KEY, campaign_id INT REFERENCES campaigns(id), ...);
CREATE TABLE tracking_events (id SERIAL PRIMARY KEY, recipient_id INT REFERENCES campaign_recipients(id), event_type VARCHAR, ...);
`

I described the relationships in English, Claude wrote the SQL. That is the workflow.

The real schema from SMTPCloud Dashboard

My biggest database has over 30 tables and 1,600+ lines of raw SQL schema. Here are the main tables and what they do:

- users — accounts that log into the system (id, email, password_hash, role, created_at)
- clients — the businesses that users manage campaigns for
- campaigns — email campaigns with subject, content, settings, status
- campaign_recipients — every person who receives a campaign (email, first_name, status)
- tracking_events — opens, clicks, bounces, unsubscribes for each recipient
- unsubscribes — people who opted out, tracked separately for compliance
- programs — higher-level grouping of campaigns
- leads — lead data with statuses and metadata
- nurture_programs — automated email sequences
- domain_rate_limits — how fast we can send to each email provider (Gmail, Yahoo, etc.)
- email_send_queue — emails waiting to be sent, with priority and retry logic
- api_keys — tokens for programmatic access to the platform
- scheduled_tasks — cron-like jobs managed through the database
- activity_logs — audit trail of who did what and when
- assets — uploaded files, images, templates

This did not start with 30 tables. It started with maybe 5. Every new feature added a table or two. That is normal.

Indexes: making your database fast

Here is a problem I ran into on Quality Monitoring. The issues table had about 100,000 rows. When someone filtered issues by team name, it took 3-4 seconds to load. That is way too slow for a dashboard.

The fix was an index. Think of an index like the index in the back of a textbook — instead of reading every page to find the word "quality," you look it up in the index and it tells you exactly which pages to go to. A database index works the same way: instead of scanning every single row to find team = 'North America', it uses the index to jump straight to the matching rows.

`
CREATE INDEX idx_issues_team ON issues(team_name);
`

That one line took the query from 3-4 seconds down to under 100 milliseconds. Quality Monitoring has 15+ indexes because it needs to search and filter data quickly for real-time dashboards. The rule of thumb: if you frequently search or filter by a column, add an index on it. Claude will usually suggest indexes when it writes your schema, but it is good to know why they exist.

ORM vs raw SQL — both are fine, here is when to use each

An ORM (Object-Relational Mapping) lets you talk to the database using your programming language instead of writing SQL directly. Instead of writing SELECT * FROM users WHERE email = 'john@test.com', you write something like User.findOne({ email: 'john@test.com' }) in JavaScript or db.query(User).filter_by(email='john@test.com').first() in Python.

Here is my experience with both approaches across real projects:

- SMTPCloud Dashboard — raw SQL with node-postgres (the pg library). I wrote direct SQL queries. More control, more work, but for a complex project with lots of custom queries and joins, it was the right call.
- LeadTool — SQLAlchemy ORM with Python. You write Python objects and methods instead of SQL. Great for rapid development and migrations.
- MyVaultKeep.io — Drizzle ORM with TypeScript. Modern, type-safe, catches errors at compile time.
- Quality Monitoring — raw SQL in some places, light ORM patterns in others.

My take: for simple CRUD apps (create, read, update, delete), an ORM saves time. For complex projects with lots of custom queries, raw SQL gives you more control. Both work. Claude handles both equally well.

Migrations: how your database evolves over time

Your database is not static. It changes as your app grows. You add a new column. You create a new table. You rename something. You add an index. Migrations are the history of those changes — like Git commits but for your database structure.

Without migrations, you would have to manually run SQL commands on your production database and hope you remember what you changed. With migrations, every change is a file with a timestamp, and you can apply or roll back changes in order.

In LeadTool (Python), I use Alembic for migrations. In MyVaultKeep.io (TypeScript), I use Drizzle Kit. The concept is the same: you describe the change, the tool generates a migration file, and you run it against the database.

Real example: I needed to add a last_login_at column to the users table in SMTPCloud Dashboard. The migration was:
`
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP;
`

One line. Applied in seconds. No data lost. No downtime. That is the power of migrations.

The four operations you will use 95% of the time

SQL has hundreds of features, but you really only need four operations for most things:

- INSERT — add new data: INSERT INTO users (email, role) VALUES ('john@test.com', 'client');
- SELECT — read data: SELECT * FROM campaigns WHERE user_id = 1;
- UPDATE — change data: UPDATE campaigns SET status = 'sent' WHERE id = 42;
- DELETE — remove data: DELETE FROM tracking_events WHERE created_at < '2023-01-01';

That is it. INSERT, SELECT, UPDATE, DELETE — often called CRUD (Create, Read, Update, Delete). If you understand these four, you understand 95% of what your app does with the database. Claude writes the actual SQL syntax. You just need to know: am I adding data, reading data, changing data, or removing data?

JOINs: getting related data in one query

Remember how campaigns belong to users and have recipients? Sometimes you need data from multiple tables at once. That is what a JOIN does — it combines rows from different tables based on their relationship.

Real example from SMTPCloud Dashboard — I need to show a list of campaigns with the recipient count for each one:

`
SELECT campaigns.name, campaigns.status, COUNT(campaign_recipients.id) as recipient_count
FROM campaigns
JOIN campaign_recipients ON campaign_recipients.campaign_id = campaigns.id
WHERE campaigns.user_id = 1
GROUP BY campaigns.id;
`

This gives me something like:
| name | status | recipient_count |
|------|--------|-----------------|
| Welcome Series | sent | 15,420 |
| March Newsletter | draft | 8,200 |

One query, data from two tables, combined and counted. JOINs are the thing that makes relational databases powerful. And honestly, I never write them by hand — I tell Claude "I need campaigns with their recipient counts" and Claude writes the JOIN.

Real example: designing a schema from scratch

Let me walk you through how I designed the Quality Monitoring database from zero. The problem: "I need to track quality issues reported by different teams. Each issue has a category (like 'broken link' or 'wrong UTM'), a severity level (low, medium, high, critical), and is assigned to a specific campaign. Team leads should be able to see issues for their team, and admins should see everything."

My prompt to Claude was almost exactly that paragraph. Claude came back with:

- issues table: id, title, description, category, severity, status, campaign_name, team_id, reported_by, assigned_to, created_at, resolved_at
- teams table: id, name, lead_user_id
- users table: id, email, name, role, team_id
- categories table: id, name, description
- issue_comments table: id, issue_id, user_id, content, created_at

Five tables. Clear relationships. Issues belong to teams. Users belong to teams. Comments belong to issues and users. I described the business problem, Claude designed the data structure. Then we iterated — I realized I also needed an issue_history table to track status changes over time, so we added that. The schema grew naturally as I discovered what I needed.

Drizzle ORM in MyVaultKeep.io — the modern TypeScript approach

Drizzle is what I used for MyVaultKeep.io, and it represents the newest wave of database tools. It is a TypeScript-first ORM, which means you define your tables in TypeScript and get full type safety — your editor catches database-related errors before you even run the code.

Instead of writing SQL like CREATE TABLE wallets (...), you write:
`
export const wallets = pgTable('wallets', {
id: serial('id').primaryKey(),
userId: integer('user_id').references(() => users.id),
address: varchar('address', { length: 44 }).notNull(),
encryptedKey: text('encrypted_key').notNull(),
createdAt: timestamp('created_at').defaultNow(),
});
`

The advantage: if you try to query a column that does not exist, TypeScript tells you immediately. No more runtime errors from typos in column names. Drizzle also generates migrations automatically — you change the schema definition, run drizzle-kit generate, and it creates the migration SQL for you. For new TypeScript projects, I would pick Drizzle every time.

Backups: the thing you will forget until it is too late

Let me be blunt: if your database dies and you have no backup, you lose everything. All your user data. All your campaigns. All your tracking history. Gone. I learned this lesson early (thankfully on a test server, not production) and now I take backups seriously.

PostgreSQL has a built-in tool called pg_dump that creates a snapshot of your entire database:
`
pg_dump -U postgres mydb > backup_2024_03_15.sql
`

That one command saves everything to a file. I run automated backups on all my production databases — daily at minimum, hourly for the critical ones like SMTPCloud. The backup files get stored on a different server (if the server dies, the backups die with it otherwise).

To restore from a backup:
`
psql -U postgres mydb < backup_2024_03_15.sql
`

Set this up from day one. Do not wait. Future you will thank present you.

Connection pooling: do not open a new connection for every request

This is a performance lesson I learned when SMTPCloud Dashboard started getting real traffic. Every time your app talks to the database, it opens a connection — think of it as a phone line between your app and the database. Opening a new connection takes time (around 50-100ms). If your app handles 100 requests per second and each one opens a new connection, that is 100 phone calls being set up and torn down every second. Your database will choke.

The solution is connection pooling — you keep a pool of, say, 20 open connections ready to go. When your app needs to talk to the database, it grabs an available connection from the pool, uses it, and puts it back. No setup time, no teardown.

In Node.js with pg, you just use Pool instead of Client:
`
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 20 });
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);
`

Claude set this up for me in every project. But it is good to know why — because if you use Client instead of Pool in a busy app, things will break under load.

Environment variables for database credentials — never hardcode passwords

This is a security rule that applies to all credentials but especially database passwords. Never, ever put your database password directly in your code. Not in your JavaScript files, not in your Python files, not anywhere that gets committed to Git.

Instead, use environment variables stored in a .env file:
`
DATABASE_URL=postgresql://myuser:s3cretP@ss@localhost:5432/smtpcloud
`

Your code reads this variable at runtime:
`
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
`

The .env file is listed in .gitignore so it never gets committed to your repository. On the server, you set the environment variable directly or use a .env file that only exists on that server. This way, your code can be public on GitHub without exposing your database password. Every project I have follows this pattern — Claude sets it up automatically, but make sure you double-check.

Schema evolution: how my databases grew over time

My SMTPCloud schema did not start with 30+ tables. It started with maybe 5 — users, campaigns, recipients, tracking_events, and settings. Then I needed API keys, so I added an api_keys table. Then I needed to track unsubscribes separately for compliance, so I added unsubscribes. Then domain rate limits, then the email send queue, then activity logs for the audit trail.

Every new feature meant either a new table or new columns on existing tables. This is completely normal. Do not try to design the perfect schema upfront — you cannot predict every feature you will build. Start with what you need now, and add as you go. The key is not being afraid to change your schema. With proper migrations, every change is tracked, reversible, and safe.

The honest truth about databases

Databases are less scary than they seem. If you can think about your data in terms of things and relationships between things, you can design a database. Users have campaigns. Campaigns have recipients. Recipients have tracking events. That sentence IS a database schema.

Claude handles the SQL syntax, the data types, the constraints, the indexes, and the performance optimization. Your job is knowing what your application actually needs to store and how the pieces relate to each other. Describe it clearly, and Claude will build the schema. Test it with real data, and you will quickly see if something is missing.

After building databases for SMTPCloud (30+ tables), Quality Monitoring (8 tables), LeadTool, MyVaultKeep.io, and several other projects, I can confidently say: the concept is simple. The details are what Claude is for.