Lesson 84 Implementation: Extending the Payment Database and Investigating WordPress Database Upgrades

Team Wpnzee on July 9, 2026

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 Plugin (After Lesson 83) (4 downloads )

Download the completed version after this lesson:

⬇ Download Plugin (After Lesson 84) (3 downloads )

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.

Leave a Reply