Introduction

In this lesson, I implemented a proper database migration system for the Flipnzee Auctions plugin. Instead of recreating tables or manually modifying the database whenever a new release introduces schema changes, the plugin now supports version-based migrations. This makes future updates much safer and easier to maintain.

The major objective of this lesson was to introduce the Watchlist table while ensuring that existing installations can upgrade without affecting current auction, bid, or transaction data.

Objectives

• Implement a version-based database migration manager.
• Support automatic schema upgrades during plugin activation.
• Add database version tracking.
• Create a dedicated Watchlist table.
• Keep existing auction data intact during upgrades.
• Prepare the plugin for future schema changes.

Files Modified

flipnzee-auctions.php
includes/class-database.php
includes/class-database-migration.php

Step 1: Added Database Version Constant

A dedicated database schema version constant was introduced.

define( 'FLIPNZEE_DB_VERSION', '1.3.0' );

This version is independent from the plugin version and is used exclusively for database migrations.

Step 2: Added Database Version Tracking

A helper method was implemented for updating the stored schema version.

public static function update_db_version() {

    update_option(
        'flipnzee_db_version',
        FLIPNZEE_DB_VERSION
    );

}

This ensures the plugin always knows which schema version is currently installed.

Step 3: Implemented Version-Based Migration Manager

A migration manager was created to execute pending migrations only when required.

$current_version = get_option(
    'flipnzee_db_version',
    '1.0.0'
);

The migration manager compares the stored version with the latest schema version and runs only the necessary migrations.

Step 4: Added Migration Methods

Separate migration methods were implemented for each schema version.

Example:

private static function migrate_to_1_3_0() {

    Flipnzee_Auction_Database::create_watchlist_table();

    Flipnzee_Auction_Database::update_db_version();

}

This structure keeps every database upgrade isolated and easy to maintain.

Step 5: Created Dedicated Watchlist Table

A separate Watchlist table was added.

CREATE TABLE wp_flipnzee_watchlist

The table stores:

• Watchlist ID
• Auction ID
• User ID
• Created timestamp

Watchlist Table Structure

id
auction_id
user_id
created_at

To prevent duplicate watchlist entries, a composite unique key was added.

UNIQUE KEY auction_user
(
    auction_id,
    user_id
)

Step 6: Database Indexes

Indexes were added for efficient lookups.

KEY auction_id
KEY user_id

These indexes improve performance when retrieving user watchlists or auction followers.

Step 7: Updated Table Creation

The database creation routine now creates four plugin tables.

flipnzee_auctions
flipnzee_bids
flipnzee_transactions
flipnzee_watchlist

Each table is created independently using dbDelta().

Step 8: Activation Flow

The activation sequence now performs the following operations:

Plugin Activation
        │
        ▼
Create Core Tables
        │
        ▼
Check Stored DB Version
        │
        ▼
Run Pending Migrations
        │
        ▼
Update Database Version

This ensures new installations receive the latest schema while existing installations are upgraded safely.

Testing Performed

The implementation was tested by:

• Activating the plugin on a clean WordPress installation.
• Verifying automatic database version updates.
• Confirming the creation of the Watchlist table.
• Checking successful execution of migration methods.
• Ensuring existing auction, bid, and transaction tables remained intact.
• Running PHP syntax validation on modified files.
• Testing activation on multiple environments.

Challenges Encountered

During implementation, several issues were identified and resolved:

• Missing migration method caused activation errors.
• Incorrect dbDelta() variable usage during table creation.
• Separate table creation logic required refinement.
• Database version synchronization needed adjustment.
• Environment-specific activation behaviour differed between hosting providers.

Interestingly, the plugin activated successfully on a clean WP Engine installation, while one development environment produced activation warnings. Since the same code functioned correctly on another WordPress installation, the issue was determined to be environment-specific rather than a problem with the migration implementation.

Lessons Learned

This lesson reinforced several important WordPress development concepts:

• Database schema versions should be managed separately from plugin versions.
• Incremental migrations are safer than rebuilding database tables.
• Each migration should have a dedicated method to improve maintainability.
• dbDelta() should be executed independently for each table definition.
• Proper indexing and unique constraints improve performance and data integrity.
• Testing across multiple hosting environments is valuable, as environment-specific behaviour can expose issues that are not caused by the plugin itself.

Download Source Code

Download the starting version of the plugin before the lesson:

Download the completed version after this lesson:

Outcome

At the end of this lesson, Flipnzee Auctions now includes a robust versioned database migration system capable of upgrading existing installations without data loss. The new Watchlist table has been integrated successfully, providing the foundation for upcoming Watchlist functionality while establishing a scalable migration framework for future plugin releases.

Project: Flipnzee Auctions Plugin
Lesson: 90
Topic: Database Migration Framework and Schema Version Management
Plugin Version: 1.2.0

Introduction

As the Flipnzee Auctions plugin continues to evolve, simply creating database tables during plugin activation is no longer sufficient. Existing users must be able to upgrade to newer plugin versions without losing their auction data.

In this lesson, we implemented a production-ready database migration framework that allows the plugin to safely upgrade existing database schemas whenever new plugin versions introduce structural changes. We also created and thoroughly tested our first real migration by adding a new payment_reference column to the transactions table.

This lesson involved extensive real-world debugging and validation, ensuring the migration framework behaves correctly across fresh installations and upgrades.

Objectives

During this implementation we aimed to:

• Build a reusable database migration framework.
• Execute migrations only when required.
• Track database versions independently of plugin versions.
• Add new database columns safely.
• Prevent duplicate schema modifications.
• Verify migrations through real upgrade testing.
• Prepare the plugin for future database upgrades.

Files Modified

flipnzee-auctions.php

includes/class-database.php

includes/class-database-migration.php

Step 1 — Integrating Database Migrations

The activation hook was enhanced to distinguish between fresh installations and upgrades.

For new installations, database tables are created normally.

For existing installations, the migration manager is executed.

function flipnzee_auction_activate() {

	if ( false === get_option( 'flipnzee_db_version', false ) ) {

		Flipnzee_Auction_Database::create_tables();

	} else {

		Flipnzee_Database_Migration::run();

	}

}

This prevents unnecessary table recreation while ensuring existing installations receive required schema updates.

Step 2 — Creating the Migration Manager

A dedicated migration manager was introduced.

It determines the currently installed database version and executes only the pending migrations.

Example:

public static function run() {

	$current_version = get_option(
		'flipnzee_db_version',
		'1.0.0'
	);

	if ( version_compare( $current_version, '1.1.0', '<' ) ) {

		self::migrate_to_1_1_0();

	}

	if ( version_compare( $current_version, '1.2.0', '<' ) ) {

		self::migrate_to_1_2_0();

	}

}

Step 3 — Implementing the First Production Migration

The first production migration upgrades the database from version 1.1.0 to 1.2.0.

Its primary responsibility is adding the new payment reference column.

self::add_column(
	'flipnzee_transactions',
	'payment_reference',
	'`payment_reference` VARCHAR(100) NULL'
);

Once complete, the migration updates the stored database version.

Step 4 — Safe Schema Updates

The helper function first checks:

• whether the table exists
• whether the column already exists

before executing:

ALTER TABLE
ADD COLUMN

This prevents duplicate-column errors during repeated activations.

Step 5 — Debugging and Validation

During testing we investigated every stage of the migration process.

Temporary debugging was added to inspect:

• activation hook execution
• database version detection
• SQL queries
• migration execution
• WordPress option values
• LiteSpeed object cache
• database updates
• activation sequence

This systematic debugging helped identify and eliminate every suspected issue.

Step 6 — Real Upgrade Simulation

To simulate a real plugin upgrade:

1. The database version was manually changed to:

1.1.0

2. The plugin was deactivated.
3. The plugin was activated again.

The migration manager correctly detected the older version:

FLIPNZEE RUN: Current version = 1.1.0

It then executed:

FLIPNZEE RUN: Calling migrate_to_1_2_0()

The schema update completed successfully:

FLIPNZEE MIGRATION: Added payment_reference column.

Finally, the stored database version became:

flipnzee_db_version = 1.2.0

This confirmed that the migration framework correctly performs production upgrades.

Step 7 — Cleaning the Framework

Once the migration was verified:

• temporary debugging statements were removed
• cache investigation code was removed
• SQL diagnostic code was removed
• activation logic was simplified
• the migration runner was restored to a clean production-ready implementation

Testing Performed

The migration framework was tested using:

• Fresh installation
• Existing installation
• Manual version downgrade
• Plugin deactivate/reactivate
• phpMyAdmin verification
• Debug log inspection
• SQL verification
• Column existence validation

Every test completed successfully.

Download Source Code

Download the starting version of the plugin before the lesson:

Download the completed version after this lesson:

Lessons Learned

This lesson reinforced several important development practices:

• Database migrations are essential for production plugins.
• Schema updates should always be incremental.
• Migration code must be idempotent.
• Version numbers should drive upgrade logic.
• Thorough debugging is invaluable when validating activation workflows.
• Real upgrade simulations provide greater confidence than relying solely on fresh installs.

Final Outcome

By the end of this lesson, Flipnzee Auctions now includes a fully functional database migration framework capable of upgrading existing installations without requiring users to reinstall the plugin or lose data.

The first production migration (v1.2.0) was successfully implemented, tested, and validated through a complete upgrade simulation. This framework provides a scalable foundation for future database enhancements, ensuring that new tables, columns, indexes, and data transformations can be introduced safely as the plugin evolves.

Git Commit

Lesson 90: Implement and validate database migration framework with schema versioning

This lesson marks a major architectural milestone for the Flipnzee Auctions plugin, bringing its database management in line with best practices used in mature WordPress plugins and laying the groundwork for future feature development.

As Flipnzee Auctions continues to evolve into a complete auction marketplace, maintaining the database safely across plugin updates becomes increasingly important. While WordPress provides the dbDelta() function for creating and updating database tables, real-world testing during development revealed that relying entirely on dbDelta() for schema upgrades can sometimes lead to unexpected behaviour across different environments.

In this lesson, the focus shifted from simply adding new payment-related database fields to designing a more reliable foundation for future database upgrades. Along the way, a challenging activation issue became an opportunity to investigate WordPress database migrations in greater depth.

Objectives

The primary objectives of this lesson were to:

• Extend the transactions table for payment processing.
• Introduce database schema versioning.
• Build the foundation for future database migrations.
• Improve the payment infrastructure.
• Resolve plugin activation issues.
• Prepare the plugin for safe future upgrades.

Features Implemented

During this lesson, several important improvements were completed.

1. Payment Database Foundation

The transactions table was extended to support the upcoming payment workflow.

New database fields include:

• payment_status
• payment_gateway
• payment_proof_id
• payment_submitted_at

These fields provide the information required for buyers to submit payments and for sellers or administrators to verify them.

2. Database Schema Versioning

A dedicated database version constant was introduced:

define( 'FLIPNZEE_DB_VERSION', '1.1.0' );

Separating the plugin version from the database schema version lays the groundwork for future database migrations without affecting plugin functionality.

3. Database Version Tracking

A new helper method was added to store the installed schema version:

public static function update_db_version() {
    update_option( 'flipnzee_db_version', FLIPNZEE_DB_VERSION );
}

The activation routine now records the installed database version after creating or updating the database tables.

4. Payment Infrastructure

The lesson also introduced the initial payment management components, including:

• Payment Manager class
• Payment administration page
• Payment styling
• Supporting transaction updates

These components will be expanded in upcoming lessons as the complete payment workflow is implemented.

Debugging the Activation Issue

One of the most educational parts of this lesson involved diagnosing a difficult plugin activation problem.

Initially, activating the plugin generated thousands of characters of unexpected output.

Rather than immediately rewriting the implementation, the issue was investigated systematically.

The debugging process included:

• validating SQL syntax
• checking PHP syntax
• comparing generated SQL with the database
• examining table structures
• isolating dbDelta() calls
• reviewing activation hooks
• inspecting plugin output
• checking for whitespace before PHP opening tags
• analysing activation behaviour across multiple iterations

This systematic approach made it possible to eliminate several possible causes before identifying and correcting the underlying issues.

Lessons Learned

Several valuable engineering lessons emerged from this implementation.

Database migrations deserve careful planning

Although dbDelta() is extremely useful for initial table creation, upgrading existing database schemas requires additional care. Introducing database version tracking provides a much safer path for future enhancements.

Debugging should be systematic

Rather than changing multiple components simultaneously, isolating one potential cause at a time greatly reduced the complexity of diagnosing the activation issue.

Build infrastructure before features

Instead of rushing into the payment workflow itself, creating the supporting database and versioning infrastructure first will make future development significantly easier.

Testing Performed

The following tests were successfully completed:

• Plugin activation
• Database table verification
• Payment column creation
• Existing data preservation
• Database version storage
• Payment infrastructure integration
• Administrative page verification
• Transaction table verification

The plugin returned to a stable working state after the activation issue was resolved.

Complete Source Code

The implementation involved updates across several files, including:

flipnzee-auctions.php

includes/class-database.php

includes/class-payment-manager.php

includes/class-payment-page.php

includes/class-transaction-manager.php

includes/class-auction-manager.php

includes/class-shortcodes.php

admin/class-admin.php

admin/class-admin-payments.php

admin/class-admin-transaction-details.php

assets/css/admin.css

These changes collectively establish the foundation for payment processing and future database migrations.

What Comes Next?

With the payment infrastructure and database versioning now in place, the next lesson will focus on implementing the first stage of the buyer payment workflow.

Upcoming work will include:

• Payment submission interface
• Payment proof upload
• Transaction status updates
• Seller verification workflow
• Administrative payment approval

These features will build upon the infrastructure completed during Lesson 85.

Download Source Code

Download the starting version of the plugin before the lesson:

Download the completed version after this lesson:

Final Thoughts

This lesson demonstrates an important aspect of professional software development: sometimes the most valuable progress comes not from adding visible features, but from strengthening the architecture beneath them.

By introducing database versioning, expanding the payment schema, and carefully investigating a complex activation issue, Flipnzee Auctions is now built on a much stronger technical foundation. These improvements will make future enhancements easier to implement, safer to deploy, and more reliable for users as the project continues to grow.

In this lesson, work continued on preparing Flipnzee Auctions for a complete payment workflow.

The initial objective was to extend the transaction database so that future lessons could support payment proof uploads, manual verification, and payment tracking.

Database Enhancements

The transactions table was extended to include:

• payment_status
• payment_gateway
• payment_proof_id
• payment_submitted_at

These additions provide the foundation required for recording buyer payments after an auction has ended.

Unexpected Challenge

After implementing the schema changes, plugin activation began producing thousands of characters of unexpected output.

Instead of immediately changing the implementation, the issue was investigated systematically.

The debugging process included:

• validating SQL syntax
• checking PHP syntax
• isolating individual dbDelta() calls
• inspecting plugin activation logs
• comparing generated SQL with the existing database
• examining the database structure using SHOW CREATE TABLE
• logging the SQL sent to dbDelta()
• analysing the return values from dbDelta()

Findings

The investigation revealed several important observations:

• The SQL statements themselves were valid.
• The existing database tables were structurally correct.
• WordPress repeatedly generated malformed index upgrade statements while processing database upgrades.
• The issue originated from dbDelta() attempting to parse existing indexes rather than from invalid SQL definitions.

Although the payment schema itself was correct, relying on dbDelta() for complex schema upgrades proved unreliable in this development environment.

Engineering Decision

Rather than continuing to force dbDelta() to modify existing tables, the project will adopt a dedicated migration system in the next lesson.

Future database upgrades will:

• check whether tables exist
• verify individual columns
• verify indexes
• execute explicit ALTER TABLE statements only when required
• maintain a plugin database version for safe upgrades

This approach is widely used in mature WordPress plugins because it provides greater control over database evolution and reduces compatibility issues across different hosting environments.

What Was Learned

One of the biggest lessons from this implementation is that software development often involves validating assumptions.

The payment database design itself was correct. The challenge lay in how WordPress attempted to upgrade an existing schema.

Carefully isolating each database operation, examining generated SQL, and validating the existing database structure made it possible to identify the true source of the problem rather than assuming the SQL definitions were incorrect.

Source Code Highlights

During this lesson, the transaction table was extended with new payment-related fields, including:

payment_status VARCHAR(30) DEFAULT 'pending',
payment_gateway VARCHAR(50) DEFAULT '',
payment_proof_id BIGINT UNSIGNED DEFAULT NULL,
payment_submitted_at DATETIME NULL,

Extensive logging was also added temporarily to inspect the SQL generated during activation and analyse the output returned by dbDelta() before deciding on a more robust migration strategy.

Download Source Code

Download the starting version of the plugin before the lesson:

Download the completed version after this lesson:

Next Lesson

Lesson 85 will focus on building Flipnzee’s database migration engine.

Instead of depending on dbDelta() for every schema change, the plugin will introduce version-based database migrations that safely upgrade existing installations while remaining compatible with future releases.

After planning the architecture in Lesson 80, it was time to implement one of the most important structural improvements in the Flipnzee Auctions plugin.

Earlier versions of the plugin prevented duplicate auctions by updating the existing auction whenever the same listing was selected. While this solved duplicate auction creation, it also prevented maintaining a complete auction history.

This lesson redesigns the logic so that a listing can have unlimited historical auctions while ensuring that only one auction can remain active at any given time.

The Problem

Originally, the plugin searched for any auction belonging to the listing.

$existing_auction = $wpdb->get_var(
    $wpdb->prepare(
        "SELECT id
        FROM {$table}
        WHERE listing_id = %d
        LIMIT 1",
        $listing_id
    )
);

If one existed, it was updated instead of creating a new auction.

Although simple, this approach caused a major limitation:

• historical auctions could never be preserved
• every new auction overwrote the previous one
• reporting and analytics became inaccurate

New Design

Instead of checking whether the listing has ever been auctioned, the plugin now checks only for active auctions.

Conceptually the lookup becomes:

SELECT id
FROM wp_flipnzee_auctions
WHERE listing_id = ?
AND status = 'active'
LIMIT 1;

This small change completely changes the behaviour of the system.

Behaviour Before

Listing 494

Creating another auction resulted in:

Auction #15 being updated.

No historical record remained.

Behaviour After

Listing 494

Each completed auction remains permanently stored.

Only one auction is active.

Code Changes

The auction lookup logic was modified so only active auctions are considered duplicates.

If an active auction exists:

• update that active auction
• return its ID

Otherwise:

• insert a completely new auction record

This preserves auction history while still preventing multiple active auctions for the same listing.

Testing Performed

Several scenarios were tested.

Test 1

Create first auction

Result:

• New auction created

✔ Passed

Test 2

Create another auction while first auction is active

Result:

• Existing active auction updated

✔ Passed

Test 3

Close auction

Result:

Auction status became Closed.

✔ Passed

Test 4

Create a new auction for the same listing

Result:

A brand-new auction record was inserted.

Previous closed auction remained untouched.

✔ Passed

Test 5

Verify All Auctions page

The administration screen correctly displayed:

Auction #35   Active
Auction #34   Closed

Both auctions belong to the same listing.

History is preserved.

✔ Passed

Benefits

The redesigned architecture provides several advantages:

• Unlimited auction history
• One active auction per listing
• Cleaner reporting
• Better analytics
• Easier auditing
• Marketplace-style auction lifecycle
• Future support for auction history pages

What Was Learned

A small SQL condition can dramatically change application behaviour.

Instead of asking:

“Has this listing ever had an auction?”

the system now asks:

“Does this listing currently have an active auction?”

This subtle change aligns the plugin with how professional marketplace platforms typically manage auction lifecycles.

Source Code

The implementation focused primarily on:

• includes/class-auction-manager.php

Key improvements included:

• checking only for active auctions
• preserving closed auctions
• creating new records only when no active auction exists
• maintaining a single active auction per listing

Download Source Code

Download the starting version of the plugin before the lesson:

Download the completed version after this lesson:

Final Result

Lesson 80 successfully redesigned the auction creation workflow.

The Flipnzee Auctions plugin now supports unlimited historical auctions while enforcing a single active auction per listing, providing a scalable foundation for future features such as auction archives, historical analytics, relisting workflows, and seller performance tracking.