Orders Data Model

Purpose

Describe the local PayPress order record, what it stores, and the timestamp invariants every order persistence path must preserve.

Overview

Orders are local merchant-facing records for one-time payments, subscription invoices, donations, fundraising donations, refunds, shipping details, notes, documents, and Payment Form snapshots.

The Orders table is not a financial ledger. Stripe remains authoritative for payment state and reconciliation. PayPress stores the operational snapshot needed for admin screens, success pages, exports, diagnostics, refunds, and support workflows.

How It Works

Orders are created or updated from accepted Stripe webhook events. PayPress uses business keys and Stripe identifiers to prevent duplicate orders while still enriching records as later webhook events arrive.

One-time payments, subscription invoice orders, donations, and fundraising donations all use the same shared order upsert architecture. Donation orders do not bypass the repository or use a special persistence table.

Fresh inserts assign a validated UTC created_at value before insertion. Existing-row updates preserve a valid created_at; if an existing row contains an invalid timestamp, the update path repairs it instead of carrying the invalid value forward.

Important Components

• Orders table.
• order_key.
• Stripe object identifiers.
• Payment status.
• Amount and currency.
• Customer details.
• Receipt and invoice links.
• Shipping details JSON.
• Customer notes.
• Form submission snapshot.
• Refund totals and operation fields.
• Created/updated timestamps.
• Timestamp repair diagnostics.

Data Flow

Stripe event -> ownership accepted -> order key resolved -> insert or duplicate recovery -> merge/enrich fields -> validate or repair timestamps -> admin tables, Order Details, exports, success page.

Timestamp Invariants

The created_at value must never be NULL, empty, or 0000-00-00 00:00:00 after persistence.

Fresh insert behavior:

1. Generate a UTC timestamp.
2. Validate the timestamp.
3. Fall back to gmdate('Y-m-d H:i:s') if needed.
4. Write created_at explicitly.
5. Read the row back after insert.
6. Repair and log if MySQL persisted an invalid timestamp.

Existing update behavior:

1. Read the existing order.
2. Never overwrite a valid created_at.
3. If created_at is invalid, repair it using valid updated_at when available.
4. If updated_at is also invalid, repair with the current UTC timestamp.
5. Log targeted timestamp diagnostics when a repair occurs or fails.

The v1.3.12 to v1.3.13 investigation confirmed that blank Created dates were caused by invalid database values, not display rendering. The v1.3.13 update-path repair prevents existing invalid rows from surviving later webhook updates or duplicate recovery.

Security Considerations

Order writes happen only after webhook signature and ownership validation. Raw sensitive card data is never stored by PayPress. Payment Form response values are stored locally on the order snapshot and are not sent to Stripe metadata.

Known Limitations

Orders are not a complete accounting ledger. Stripe remains authoritative for financial reporting and reconciliation.

Timestamp diagnostics are operational support records. They can be removed by log retention cleanup without altering the repaired order data.

Related Articles

• Order Creation Flow
• Stripe Metadata Strategy
• Refund Architecture
• Diagnostics and Event Timeline