tosane932

Posted on Sep 5 Originally published at qiita.com

🏗️ Before Letting Guests In, I Protected Existing Data: Building Dataset Boundaries with DB Migrations and 114 pytest Tests

#python #flask #postgres #pytest

Hello from Japan 🇯🇵

I wanted to turn my Flask portfolio from something people could only look at into something they could actually try for themselves.

That was why I started building a Guest Demo mode.

But before creating a single Guest screen, I ran into a much more important question:

If I allow Guests to write data, how do I make sure they can never touch the existing Admin data?

That question turned what looked like a small feature into a database migration project.

This article was originally published in Japanese on Qiita and has been translated and adapted for DEV Community.

In three lines

🛡️ Before building the Guest Demo, I introduced a Dataset boundary to protect existing Admin data.

🏗️ To migrate existing Products without breaking them, I used an Expand → Migrate → Contract flow: add the column, backfill existing data, then enforce NOT NULL.

🧪 I verified the foundation with 114 pytest tests, a production backup, post-migration database checks, and production smoke tests before letting Guests in.

🧪 Stage 1: Turning a Read-Only Portfolio into Something People Can Actually Try

The application in this article is a sales-management and analysis system I have been building as a personal project.

At the time of Stage 1, it already included features such as:

Product registration
Daily sales entry
Sales rankings
Charts
AI-assisted sales analysis

Until then, however, the application assumed an Admin login.

Recruiters or engineers could look at the project, but they could not freely interact with it.

So I wanted to make this possible:

Let someone try product registration and sales entry as a Guest without giving them my Admin credentials.

Then I noticed the danger.

⚠️ If I allow Guests to write data, couldn't they also modify the existing Admin data?

Before building a Guest login screen, I needed a foundation that could separate:

Admin data from Guest data.

As a result, I did not create a single Guest screen in Stage 1.

Instead, I worked on:

Dataset design and database migrations for protecting the existing production data.

The Goal of Stage 1

The goal was:

Move the existing Admin data into a Dataset boundary so that future Guest Datasets could be added without mixing their data together.

The Guest functionality itself would come later.

First, I wanted the existing data to become:

PostgreSQL
│
└─ Admin Dataset
    │
    └─ Product
        │
        └─ DailySales

The intended future structure was:

PostgreSQL
│
├─ Admin Dataset
│   └─ Product
│       └─ DailySales
│
├─ Guest Dataset A
│   └─ Product
│       └─ DailySales
│
└─ Guest Dataset B
    └─ Product
        └─ DailySales

The idea was to isolate data by Dataset.

Why I Couldn't Build the Guest Feature First

Before this change, Product had no information answering:

"Whose data is this Product?"

For example:

id = 1
name = White Bread

The database could not tell whether that Product belonged to:

Admin
Guest A
Guest B

If I added Guest functionality in that state, I could end up with:

Admin Products
Guest A Products
Guest B Products

all sitting on the same shelf.

So I decided to give every Product an ownership boundary.

Thinking of a Dataset as a Room

This was the mental model I used:

PostgreSQL = the building

Dataset = a room

Product = an item stored inside that room

dataset_id = a name tag showing which room the Product belongs to

The Product structure becomes something like:

Product
├─ id
├─ name
├─ price
└─ dataset_id

If dataset_id points to the Admin Dataset:

This Product belongs to the Admin room.

Future Guest Products could be separated in the same way:

Admin Product
→ dataset_id points to Admin Dataset

Guest A Product
→ dataset_id points to Guest Dataset A

Guest B Product
→ dataset_id points to Guest Dataset B

Adding the Dataset Model

First, I introduced a Dataset model.

The general structure looked like this:

class Dataset(db.Model):
    __tablename__ = "datasets"

    id = db.Column(UUID(as_uuid=True), primary_key=True)

    kind = db.Column(
        db.String(16),
        nullable=False,
    )

    system_key = db.Column(
        db.String(100),
        unique=True,
    )

    created_at = db.Column(...)
    last_activity_at = db.Column(...)
    absolute_expires_at = db.Column(...)

kind can be:

admin
guest

The Admin Dataset follows this rule:

kind = admin
system_key = admin

A Guest Dataset follows:

kind = guest
system_key = NULL

Guests were not actually being used yet in Stage 1.

I was only preparing the structure for adding them later.

I Wanted the Database Itself to Enforce the Rules

One thing I deliberately avoided was relying only on:

"The Python code will probably use the values correctly."

For example, Dataset.kind should only allow:

admin
guest

So I also added a database-level CHECK constraint:

sa.CheckConstraint(
    "kind IN ('admin', 'guest')",
    name="ck_datasets_kind",
)

I also added database rules for:

Admin → system_key = admin

Guest → system_key = NULL

My mental model was:

Validation in Python = the reception desk at the building

Database CHECK constraint = a rule built into the building itself

Even if the reception desk accidentally allows an invalid value through, the database still has one final chance to reject it.

Giving Every Product an Ownership Tag

Next, I added dataset_id to Product.

The final model contained:

dataset_id = db.Column(
    UUID(as_uuid=True),
    db.ForeignKey(
        "datasets.id",
        ondelete="CASCADE",
    ),
    nullable=False,
    index=True,
)

This means every Product must belong to a Dataset.

My mental model was:

"No Product without a name tag is allowed into the warehouse."

But there was a problem.

The production database already contained Products.

I Couldn't Just Add `NOT NULL` Immediately

Before the change, Product did not even have a:

dataset_id

column.

What would happen if I immediately added:

dataset_id NOT NULL

to a table that already contained Products?

The existing rows would have no value.

Existing Product
↓
Add dataset_id
↓
Existing Product has no Dataset
↓
NOT NULL violation

⚠️ Wanting the final column to be NOT NULL does not mean it is safe to make it NOT NULL from the beginning.

I also had to think about how the existing data would move into the new structure.

So I split the migration into stages.

Expand → Migrate → Contract

The migration ended up following this flow:

1. Expand
   Add dataset_id while allowing NULL

2. Migrate
   Assign the Admin Dataset to all existing Products

3. Contract
   Make dataset_id NOT NULL

This is close to the:

Expand → Migrate → Contract

pattern.

I understood it like this:

Don't close the old gate immediately.

First, build the new route.

Move the existing data onto that route.

Confirm that everyone has moved.

Only then make the old state invalid.

Migration 1: Create Dataset and Backfill Existing Products

The first migration revision was:

c7a1d9e4f2b6

It performed these steps:

Create the datasets table
Add products.dataset_id while allowing NULL
Create the Admin Dataset
Backfill all existing Products into the Admin Dataset
Add the foreign key
Add the index

What Is a Backfill?

In this migration, backfilling meant:

adding an Admin Dataset ownership tag to Products that already existed.

Before

Product 1  White Bread
Product 2  Croissant
Product 3  Melon Bread

        ↓

Create Admin Dataset

        ↓

Product 1  White Bread
└─ dataset_id → Admin Dataset

Product 2  Croissant
└─ dataset_id → Admin Dataset

Product 3  Melon Bread
└─ dataset_id → Admin Dataset

Now the existing Products could participate in the new Dataset structure.

I Put Validation Inside the Migration Itself

I did not want the migration logic to be:

UPDATE completed
↓
Migration successful

Before modifying the data, I recorded the row counts:

product_count_before = _count_rows(bind, products)
sales_count_before = _count_rows(bind, daily_sales)

After the backfill, the migration checked things such as:

Did the Product count remain unchanged?
Did the DailySales count remain unchanged?
Were any Products still using dataset_id = NULL?
Did all Products belong to the Admin Dataset?
Were there any orphan Products pointing to no valid Dataset?

If something was wrong:

raise RuntimeError(...)

stopped the migration.

One idea became especially important to me:

"The migration reached the end" does not mean "the data was migrated correctly."

I wanted row counts and ownership relationships to be part of the definition of success.

Migration 2: Enforce `NOT NULL` at the End

The second migration revision was:

f2b6c8d4e1a9

This was where:

products.dataset_id

finally became NOT NULL.

But before strengthening the constraint, the migration checked again:

Does exactly one Admin Dataset exist?
Are any Products still using dataset_id = NULL?
Does any Product reference a Dataset that does not exist?

Only after those checks passed did it run:

batch_op.alter_column(
    "dataset_id",
    existing_type=sa.Uuid(as_uuid=True),
    nullable=False,
)

At that point, the database itself guaranteed that a Product could not exist without a Dataset.

Changing Only the Database Would Still Be Dangerous

There was another compatibility problem.

Suppose I changed the database to:

dataset_id NOT NULL

but the old application still created Products like this:

Product(
    name="New Product",
    price=300,
)

The new database would reject the write.

That creates a dangerous combination:

New database
dataset_id NOT NULL

×

Old application
creates Product without dataset_id

⚠️ A database migration has to consider not only the database itself, but also the application code that may be running against it during the transition.

So I updated:

Product creation
Seed logic
Product creation inside pytest

to understand Datasets as well.

New Products Also Receive the Admin Dataset

When registering a new Product, the application retrieves the Admin Dataset:

admin_dataset = get_admin_dataset()

and creates the Product with:

Product(
    dataset=admin_dataset,
    ...
)

Every newly registered Admin Product therefore has a dataset_id that points to the Admin Dataset.

And if the Admin Dataset is unexpectedly missing:

the application does not silently create a new one.

Product creation fails instead.

I wanted the abnormal state to fail safely rather than pretending everything was normal.

Updating `seed_demo.py` for Dataset Support

The production startup process also runs seed_demo.py.

If the seed logic remained on the old structure, it could try to create a Product without a Dataset after:

dataset_id NOT NULL

was enforced.

So the seed logic was also updated to:

Find Admin Dataset
↓
Create Product

If the Admin Dataset does not exist, the seed process fails instead of guessing.

I Paused for Understanding Checks During the Implementation

This time, I did not ask Codex to implement everything at once and inspect the result only at the end.

I used a loop like this:

Investigate
↓
Understand the mechanism
↓
Implement a small part
↓
Verify with pytest
↓
Check my own understanding
↓
Continue

At important points, I stopped and asked myself whether I could explain:

Why Dataset was necessary
What dataset_id represented
Why it could not be NOT NULL immediately
What the backfill was doing
Why the database needed CHECK constraints in addition to Python validation
Why the migration was split into two revisions
Why I disabled Auto-Deploy

My rule became:

Not "the code works, so continue," but "I can explain why we're doing this, so continue."

Because I was using AI during development, I wanted to avoid increasing implementation speed while leaving my own understanding behind.

pytest Was GREEN — and I Still Stopped

During the Dataset migration work, the existing pytest suite was already GREEN.

But when I searched through the test code, I found multiple places still creating:

Product(...)

without a Dataset.

There were about 20 such cases in the normal pytest suite alone.

So the situation was:

The application itself understood Datasets, but test Products were still being created with the old structure.

Why did the tests still pass?

Because dataset_id was temporarily nullable during the migration process.

That meant the old test fixtures could survive.

So I reviewed all Product creation in the test suite as well.

Adding Dataset-Specific pytest Coverage

I also added tests specifically for Dataset behavior and migration safety.

For example:

Admin Dataset can be created
Guest Dataset can be created
Invalid kind is rejected
Invalid system_key for an Admin Dataset is rejected
A Guest Dataset with a system_key is rejected
A Product can belong to a Dataset
A nonexistent Dataset ID is rejected
dataset_id = NULL is rejected after the migration
Existing Product counts are preserved during migration

The final result was:

114 passed

More important than simply seeing GREEN was asking:

Did this GREEN actually pass through the new Dataset structure?

I Added `ON DELETE CASCADE`, but This Was Not the Final Cleanup Design

The foreign key for Product.dataset_id includes:

ondelete="CASCADE"

This creates a foundation where deleting a Dataset can also delete the Products belonging to it.

The intended conceptual cleanup flow for a later stage was:

Delete Dataset
↓
Delete Products
↓
Delete DailySales

However:

Stage 1 did not yet complete the deletion chain all the way through DailySales.

DailySales is connected through Product.

This part was intentionally left for a later stage, where Guest Dataset expiration, cleanup behavior, and referential consistency would be addressed together.

⚠️ Stage 1 was specifically about:

adding a Dataset boundary to existing Admin data.

Guest Dataset lifetime management and the complete automatic deletion chain were not finished yet.

pytest Passed. I Still Didn't Deploy Yet.

All 114 pytest tests passed.

Normally, that creates a strong temptation:

Great. Deploy!

But this was a database migration.

The production PostgreSQL database already contained Admin data I had been using.

🚨 The highest priority was not shipping the new feature quickly.

It was protecting the existing data.

From this point onward, I treated the work like a safety-critical change.

Safety first. ⛑️

Turning Off Render Auto-Deploy

The Docker startup process for this application ran:

flask db upgrade
↓
python seed_demo.py
↓
gunicorn

That meant once a Render deployment began:

the production database migration would begin automatically as part of startup.

So the first thing I did was disable Auto-Deploy.

Now:

Merge to GitHub

did not immediately become:

Production DB migration

The database remained unchanged until I manually started the deployment.

⚠️ I deliberately separated:

merging the code

from:

starting the production database work.

I did not want "merge" to mean "construction begins immediately."

Backing Up the Production Database

Next, I created a backup of the production PostgreSQL database.

There was one more problem.

My local pg_dump was from PostgreSQL 16.

The production PostgreSQL server was version 18.

So I used the PostgreSQL 18 Docker image and ran a version-compatible pg_dump.

After creating the backup, I also ran:

pg_restore --list backup.dump

to confirm that the archive could be read.

⚠️ This was not a complete restore test.

What I verified was:

pg_restore could successfully read the backup archive I had created.

Recording the Production Baseline Before Migration

A backup alone was not enough.

I also recorded:

what the production database looked like before the migration.

The baseline was:

Alembic revision
9d3c1b7e5a42

Product
8 rows
IDs 1–8
Active products: 8

DailySales
56 rows
IDs 1–56

Orphan DailySales
0

These numbers became my post-migration comparison point.

Instead of saying:

"The data is probably still there."

I wanted to be able to say:

Before: 8 Products
After:  8 Products

with actual numbers.

Merging the PR Still Did Not Change Production

I merged the changes into main.

The diff was:

18 files changed
1301 insertions
15 deletions

But Auto-Deploy remained disabled.

So at that point:

GitHub
→ New code

Render production
→ Still old code and old DB

I also updated my local main and confirmed:

nothing to commit, working tree clean

Starting the Production Migration

Next, I stopped write operations against the production application and manually started:

Manual Deploy
↓
Deploy latest commit

The Render deployment log showed:

Running upgrade 9d3c1b7e5a42 -> c7a1d9e4f2b6
Running upgrade c7a1d9e4f2b6 -> f2b6c8d4e1a9

Then:

Demo seed skipped: database already contains data.
Starting gunicorn
Your service is live

At that point, the startup sequence had completed:

Migration
↓
Seed decision
↓
Gunicorn startup

I Didn't Stop at "Deploy Succeeded"

Render said the deployment succeeded.

That still did not prove that the existing data was intact.

So I inspected the production database directly.

The result was:

Alembic
f2b6c8d4e1a9

Dataset
Total: 1
Admin: 1
Guest: 0

Product
8 rows
IDs 1–8
dataset_id NULL: 0
Owned by Admin: 8
Orphan Product: 0

DailySales
56 rows
IDs 1–56
Orphan DailySales: 0

Before migration:

Product:    8
DailySales: 56

After migration:

Product:    8
DailySales: 56

✅ I preserved all 8 existing Products and all 56 DailySales records while giving every existing Product an Admin Dataset ownership tag.

The Final Step Was a Production Smoke Test

Correct row counts were not enough.

The application still had to work.

So I performed a production smoke test on Render.

I checked:

Admin login
Product list
Daily sales entry
Dashboard
Sales rankings
Charts
AI advice

Everything worked normally.

What I Mean by a Smoke Test

A smoke test is not:

checking every detailed condition.

It is:

checking whether the application's major functions are still alive after a change.

Using my truck-driving work as an analogy:

pytest = a detailed inspection using an inspection checklist

Smoke test = after maintenance, actually starting the engine and checking that the vehicle can move, steer, and stop

That was the mental model I used.

I Also Tested Production Writes

I did not stop with read-only checks.

I registered a new Product:

明太フランス

and entered daily sales for it.

I then confirmed that the new sales appeared correctly in:

Sales rankings
Charts

One Final Check: Did the New Product Receive Its Ownership Tag?

The UI worked.

Still, I wanted one final check.

Did the newly registered Product actually belong to the Admin Dataset?

I queried PostgreSQL directly:

SELECT
  p.id,
  p.name,
  d.kind,
  d.system_key
FROM products p
JOIN datasets d ON d.id = p.dataset_id
WHERE p.name = '明太フランス'
ORDER BY p.id DESC
LIMIT 1;

The result was:

id         9
name       明太フランス
kind       admin
system_key admin

✅ The newly registered Product had the correct Admin Dataset ownership tag.

That verified the complete production path:

Register Product
↓
Assign Admin Dataset
↓
Enter sales
↓
Save to DB
↓
Aggregate
↓
Ranking
↓
Chart

All I Wanted Was a Guest Demo

This entire project started with a simple idea:

I want people to actually try my portfolio.

But the moment I said:

Allow Guests to write data

the questions multiplied:

How do I protect existing Admin data?

How do I isolate one Guest from another?

Who owns the existing Products?

What if the old application writes during migration?

What if NULL remains?

What if migration deletes data?

And the work expanded into:

Dataset design
↓
DB constraints
↓
Migration
↓
Backfill
↓
NOT NULL
↓
Application changes
↓
pytest
↓
Backup
↓
Production migration
↓
DB comparison
↓
Smoke test

Before creating even one Guest login button, the project had already become a fairly large construction job.

"It Works" Is No Longer Enough for Me

Earlier in my learning, I probably would have thought:

It works in the browser
↓
Done

This time, I kept asking:

What happens if it breaks?

What happens in a partially failed state?

Did the existing data really survive?

Can the database itself reject invalid values?

Does a newly created production Product actually receive a Dataset?

Are the row counts still identical after deployment?

Continuing to strengthen pytest seems to have changed how I approach other parts of development too.

I gradually started paying less attention to:

"Is it GREEN?"

and more attention to:

"What does this GREEN actually guarantee?"

Stage 1 Complete

Stage 1 was now complete:

Protect the existing Admin data with Dataset boundaries.

At the end of Stage 1, the structure was:

PostgreSQL
│
└─ Admin Dataset
    │
    └─ Product
        │
        └─ DailySales

There were still no Guests.

The next step at that point was:

PostgreSQL
│
├─ Admin Dataset
│
└─ Guest Dataset

Guests would need mechanisms that Admin did not, including:

Temporary sessions
Per-Guest data isolation
Protection against accessing Admin data
Inactivity expiration
Automatic Guest Dataset cleanup
Limits on AI usage

Only then could I finally begin working on the Guest side of:

a portfolio people could actually interact with.

Conclusion

In Stage 1:

I did not create a single Guest screen.

At first glance, it might look as though the Guest Demo barely progressed.

But what I actually built was the foundation for:

protecting the data that must remain safe before allowing anyone else to interact with the application.

The most important decision in this stage was:

designing protection for the existing data before adding the new feature.

The next stage would create:

a real Guest-only "room" next to the Admin Dataset. 🧪

👤 Guest Demo Implementation Series

Stage 1 — This Article

Migrated the existing Admin data into a Dataset boundary and prepared the database structure before adding Guest areas.

Original Japanese version:

https://qiita.com/tosane932/items/2ccaab5c1b7e29619345

Stage 2 — Part 1

Protected Admin before allowing Guests in, then built the foundation for Guest identity and Dataset authorization.

https://qiita.com/tosane932/items/77cc200ab78761174b91

Stage 2 — Part 2

Safely created Guest Datasets on the server, connected them to Guest identities, and re-audited the safety conditions even after reaching 181 GREEN tests.

https://qiita.com/tosane932/items/aa3b8a06029e8d5e3f25

Stage 3

Strengthened Dataset boundaries across Product, DailySales, Dashboard, API, AI, and seed logic before allowing Guests into business routes, growing pytest from 181 to 195 tests.

https://qiita.com/tosane932/items/f825aff19bff0d3d122c

Stage 4

Allowed valid Guests into the real application routes and verified through real request paths that Dataset boundaries held across Products, sales, Dashboard, and AI.

https://qiita.com/tosane932/items/166113162b6a4d1a437e