- The Problem Today
- The Solution
- Why This Approach (Industry Context)
- Database Schema
- Architecture Overview
- Sync Pipeline
- Data Mapping: API to Storage
- Query Performance: Before vs After
- Before vs After Comparison
- Key Numbers
- Rollout Phases
- AI-Accelerated Development
- WooCommerce Reference: Tables and Structure
- Summary
We currently rely on WP All Import to bring course data from third-party APIs into WordPress. This causes four recurring issues:
Slow imports -- Processing feeds takes too long and frequently times out. Daily syncs are unreliable.
Fragile field mapping -- Every time the source API changes a field, the import configuration breaks and requires manual reconfiguration.
Slow front-end -- Filtering courses by date, location, or subject is slow because WordPress stores every field as a separate row in a generic metadata table. Each filter means another expensive database lookup.
Heavy dependency -- WP All Import is a large, general-purpose tool. We pay for complexity we don't need, and every update risks breaking our setup.
Build a custom WordPress plugin that owns the full lifecycle: import data from the API, store it efficiently, and display it on the site. No third-party import tools needed.
How it works:
| Layer | What It Does | Why It Matters |
|---|---|---|
| Course pages (WordPress) | Each course is a custom post type (CPT) with a title, description, image, and URL | Works with the theme, Full Site Editing, SEO plugins, and the block editor out of the box |
| Course data table (custom) | A dedicated database table stores structured fields: dates, location, price, instructor, capacity | One fast lookup instead of many slow joins. Filtering and sorting is instant. |
| Sync engine (built-in) | A lightweight scheduled job fetches the API feed daily, compares checksums, and only updates what changed | No WP All Import. No spreadsheets. Changes are detected automatically and applied reliably. |
| Sync log (custom) | Every import run is logged: what was created, updated, skipped, or failed, with error details | Full visibility into what happened and when. No more silent failures. |
| Admin settings | Configure API endpoint, credentials, and field mapping per site | One plugin works across multiple client sites. Each client configures their own source. |
This is the same strategy used by WooCommerce, the largest WordPress e-commerce plugin (powers 28% of all online stores). They moved from generic WordPress tables to custom database tables for orders -- called "High-Performance Order Storage" -- because the old approach didn't scale. Companies like 10up and Human Made (the largest WordPress agencies globally) recommend the same pattern for performance-critical data.
Industry validation: WooCommerce 8.2+ uses custom database tables by default for all new stores. Their migration proves that the hybrid approach (custom post types for content, custom tables for structured data) is the production-tested standard for performance at scale.
How course data is stored. The WordPress layer handles content, the custom tables handle structured/queryable data.
erDiagram
wp_posts {
bigint ID PK
varchar post_title "Course title"
longtext post_content "Full description"
text post_excerpt "Short summary"
varchar post_name "URL slug"
varchar post_status "publish / draft"
varchar post_type "course"
datetime post_date
}
courseswp_data {
bigint id PK
bigint post_id FK "Links to course CPT post"
varchar external_id UK "ID from source API"
varchar source_url "Which API endpoint"
varchar source_checksum "Detects changes"
datetime start_date "Indexed for filtering"
datetime end_date "Indexed for filtering"
varchar location "Indexed for filtering"
varchar instructor
decimal price
int capacity
int seats_available
varchar enrollment_status "open / closed / waitlist"
varchar image_url "Original source image"
longtext raw_json "Full API response archived"
datetime last_synced_at
varchar sync_status "success / error / pending"
}
courseswp_sync_log {
bigint id PK
varchar sync_run_id "Groups one import run"
bigint post_id FK "null if create failed"
varchar external_id
varchar action "created / updated / skipped / error"
text message "Error details or diff summary"
datetime created_at
}
wp_terms {
bigint term_id PK
varchar name "e.g. Business, Science"
varchar slug
}
wp_term_taxonomy {
bigint term_taxonomy_id PK
bigint term_id FK
varchar taxonomy "course_category / course_tag"
}
wp_term_relationships {
bigint object_id FK "course post_id"
bigint term_taxonomy_id FK
}
wp_posts ||--|| courseswp_data : "1:1 linked by post_id"
wp_posts ||--o{ courseswp_sync_log : "import history"
wp_posts ||--o{ wp_term_relationships : "categorized by"
wp_term_relationships }o--|| wp_term_taxonomy : "belongs to"
wp_term_taxonomy }o--|| wp_terms : "named by"
courseswp_data -- one row per course, flat and indexed:
| Column | Type | Purpose |
|---|---|---|
id |
bigint, auto-increment | Primary key |
post_id |
bigint, unique index | Foreign key to wp_posts.ID |
external_id |
varchar, unique index | ID from the source API (idempotency key) |
source_url |
varchar | Which API endpoint this came from |
source_checksum |
varchar | Hash of last imported payload (diff detection) |
start_date |
datetime, indexed | Course start date |
end_date |
datetime, indexed | Course end date |
location |
varchar, indexed | Physical or virtual location |
instructor |
varchar | Instructor name |
price |
decimal(10,2) | Cost info (display only, not e-commerce) |
capacity |
int | Total seats |
seats_available |
int | Remaining seats |
enrollment_status |
varchar | open / closed / waitlist |
image_url |
varchar | Original image URL from source |
raw_json |
longtext | Full API response (archived for re-mapping) |
last_synced_at |
datetime | When this record was last imported |
sync_status |
varchar | success / error / pending |
courseswp_sync_log -- audit trail per import run:
| Column | Type | Purpose |
|---|---|---|
id |
bigint, auto-increment | Primary key |
sync_run_id |
varchar | Groups all entries from one import run |
post_id |
bigint, nullable | Null if creation failed |
external_id |
varchar | Source API ID |
action |
varchar | created / updated / skipped / deleted / error |
message |
text | Error details or change summary |
created_at |
datetime | Timestamp |
Taxonomies (registered on the course CPT):
course_category-- hierarchical (subject areas, topics)course_tag-- flat (free-form tags)
WordPress CPT stores: title, full description (block editor), short description, slug/URL, publish status, featured image.
How all the pieces fit together.
flowchart LR
subgraph external [External]
API[Third-Party Course API]
end
subgraph plugin [CourseWP Plugin]
SYNC[Sync Engine]
REPO[Data Repository]
ADMIN[Admin Settings UI]
REST[REST API Fields]
end
subgraph wordpress [WordPress]
CPT[Course CPT - wp_posts]
TAX[Taxonomies - categories and tags]
FSE[Full Site Editing Templates]
CRON[WP Cron Scheduler]
end
subgraph customdb [Custom Database Tables]
DATA[courseswp_data]
LOG[courseswp_sync_log]
end
subgraph frontend [Front-End]
ARCHIVE[Course Archive Page]
SINGLE[Single Course Page]
FACET[FacetWP Filters]
end
API -->|"JSON feed"| SYNC
CRON -->|"triggers daily"| SYNC
ADMIN -->|"configures"| SYNC
SYNC --> REPO
REPO --> CPT
REPO --> TAX
REPO --> DATA
REPO --> LOG
CPT --> FSE
FSE --> ARCHIVE
FSE --> SINGLE
DATA -->|"indexed columns"| FACET
FACET --> ARCHIVE
REST -->|"exposes custom fields"| CPT
What happens when the daily import runs.
flowchart TD
A[Scheduled trigger - daily via WP Cron] --> B[Fetch course list from API]
B --> C{API responded OK?}
C -- No --> D[Log error to sync_log, send alert, stop]
C -- Yes --> E[Loop through each course in response]
E --> F[Compute checksum of API record]
F --> G{Course exists in DB by external_id?}
G -- No --> H[Create new course post + custom table row]
G -- Yes --> I{Checksum changed?}
I -- No --> J[Skip - nothing changed]
I -- Yes --> K[Update course post + custom table row]
H --> L[Log action: created]
K --> L2[Log action: updated]
J --> L3[Log action: skipped]
L --> M[Next course]
L2 --> M
L3 --> M
M --> E
M --> N[Write sync run summary to sync_log]
N --> O[Done - results visible in admin]
- Idempotent on
external_id-- Running the same import twice produces the same result. No duplicates. - Checksum diff -- Only records that actually changed get written. Unchanged courses are skipped.
- Atomic per-course -- If one course fails, others still import. The failure is logged, not silent.
raw_jsonarchived -- The full API response is stored. If field mapping needs to change later, we can re-process from the archive without re-fetching.- Future: webhooks -- The API can notify the plugin of changes instead of requiring a full daily re-import.
Where each piece of API data ends up.
flowchart LR
subgraph api [API Response Fields]
A1[title]
A2[description]
A3[short_description]
A4[image]
A5[id / code]
A6[start_date]
A7[end_date]
A8[location]
A9[instructor]
A10[price]
A11[capacity]
A12[subject_areas]
A13[full JSON payload]
end
subgraph wp [Course CPT]
W1[post_title]
W2[post_content]
W3[post_excerpt]
W4[featured image]
end
subgraph custom [Custom Table]
C1[external_id]
C2[start_date]
C3[end_date]
C4[location]
C5[instructor]
C6[price]
C7[capacity]
C8[raw_json]
end
subgraph tax [Taxonomies]
T1[course_category terms]
end
A1 --> W1
A2 --> W2
A3 --> W3
A4 --> W4
A5 --> C1
A6 --> C2
A7 --> C3
A8 --> C4
A9 --> C5
A10 --> C6
A11 --> C7
A12 --> T1
A13 --> C8
Each client site can map their API field names to CourseWP fields via admin settings. The mapping is stored as a configuration, not hardcoded. Example:
| API Field (varies per client) | CourseWP Field (standard) |
|---|---|
course_name or title or Name |
post_title |
description_html or body |
post_content |
startDate or start or date_from |
start_date |
venue or location or campus |
location |
subjects[] or categories[] |
course_category taxonomy |
When a client's API uses different field names, the admin reconfigures the mapping. The plugin handles the rest.
Why the custom table matters -- the database work required for a single filtered listing page.
flowchart LR
subgraph before [Current: postmeta approach]
Q1[SELECT courses WHERE location = X AND start_date > Y AND subject = Z]
Q1 --> J1[JOIN wp_postmeta for location]
Q1 --> J2[JOIN wp_postmeta for start_date]
Q1 --> J3[JOIN wp_postmeta for subject]
Q1 --> J4[JOIN wp_postmeta for price]
Q1 --> J5[JOIN wp_postmeta for status]
J1 --> R1[Slow: 5+ joins on millions of rows]
end
subgraph after [Proposed: custom table approach]
Q2[SELECT courses WHERE location = X AND start_date > Y AND subject = Z]
Q2 --> T1[Single indexed lookup on courseswp_data]
T1 --> R2[Fast: 1 table with proper indexes]
end
WordPress wp_postmeta is a key-value store. Every field (start_date, location, price, instructor, etc.) is a separate row. A course with 15 fields creates 15 rows in wp_postmeta. When you have 500 courses with 15 fields each, that's 7,500 rows in a single table -- mixed with metadata from every other post type on the site.
Filtering by 5 fields means the database must join that table to itself 5 times. Each join scans through all those rows. This is why listing pages with filters get progressively slower.
courseswp_data has one row per course with typed, indexed columns. Filtering by 5 fields is one query on one table with proper indexes. The database knows exactly where to look.
| Current (WP All Import) | Proposed (CourseWP Plugin) | |
|---|---|---|
| Import speed | Minutes to process a full feed. Prone to timeouts. | Seconds. Only changed records are updated (checksum diff). |
| Reliability | Fails silently. Data corruption discovered later. | Every record logged. Errors surfaced immediately. |
| API changes | Mapping breaks. Manual reconfiguration needed. | Field mapping in code or admin. One change, all sites updated. |
| Front-end speed | Slow filtering. Each filter = extra database join. | Fast indexed queries. One table, proper indexes. |
| Maintenance cost | WP All Import license + debugging time. | Zero license cost. Plugin we own and control. |
| Multi-client | Configure WP All Import per site from scratch. | One plugin, per-site config. Deploy and go. |
- 20-500+ courses per client
- 1x/day sync frequency (with webhook support planned for the future)
- 0 external plugin licenses required
- 100% data we own and control
| Phase | Deliverable | Timeline Estimate |
|---|---|---|
| 1. Foundation | Plugin skeleton, database tables, course custom post type in WordPress | 1-2 weeks |
| 2. Sync engine | API importer with daily schedule, checksums, error logging | 1-2 weeks |
| 3. Front-end | Archive page, single course page, FacetWP filtering integration | 1 week |
| 4. Admin UI | Settings page: API config, field mapping, sync status, manual trigger | 1 week |
| 5. Multi-client | Per-site configuration, deployment documentation | 0.5 weeks |
Total estimated build time: 4.5-6.5 weeks
We use AI-assisted development tooling (Cursor IDE with custom skills and agents) to accelerate the build and reduce ongoing maintenance:
| Capability | What It Does |
|---|---|
| API schema skill | Documents the third-party API structure. When the API changes, the AI regenerates the mapping code and tests automatically. |
| Migration skill | Standardizes how we add or modify database tables. Reduces human error in schema changes. |
| Code review agent | Checks every change for performance problems (e.g., slow queries) before it reaches production. |
| Test generation | AI generates test cases from sample API responses. New endpoints get test coverage immediately. |
- New client onboarding: AI reads the client's API documentation and generates the field mapping configuration and initial test fixtures.
- API version changes: AI diffs the old and new API schemas, updates the mapper, and regenerates affected tests.
- Bug investigation: AI queries the sync log, identifies patterns, and suggests fixes.
For technical context: WooCommerce's journey validates the approach we are taking.
WooCommerce historically stored all order data in wp_posts and wp_postmeta (the same generic WordPress tables). As stores grew, this caused the exact same problems we face: slow queries, unreliable bulk operations, and scaling issues.
Starting with version 8.2 (October 2023), WooCommerce introduced High-Performance Order Storage (HPOS) -- custom database tables purpose-built for order data. New installations use custom tables by default.
WooCommerce now creates 38 custom tables, organized by purpose:
HPOS order tables (the core migration):
wc_orders-- order header (status, totals, customer, dates, payment method)wc_order_addresses-- billing/shipping addresseswc_order_operational_data-- operational flagswc_orders_meta-- arbitrary order metadata
Order line items:
woocommerce_order_items-- line items per orderwoocommerce_order_itemmeta-- metadata per line item
Product lookup tables (performance):
wc_product_meta_lookup-- denormalized product fields for fast queries (SKU, stock, prices)wc_product_attributes_lookup-- attribute-based filtering
Analytics/reporting:
wc_order_stats,wc_order_product_lookup,wc_order_coupon_lookup,wc_order_tax_lookup,wc_customer_lookup,wc_category_lookup
Tax, shipping, sessions, payments, misc:
- Tax:
woocommerce_tax_rates,woocommerce_tax_rate_locations,wc_tax_rate_classes - Shipping:
woocommerce_shipping_zones,woocommerce_shipping_zone_locations,woocommerce_shipping_zone_methods - Sessions/API/tokens:
woocommerce_sessions,woocommerce_api_keys,woocommerce_payment_tokens,woocommerce_payment_tokenmeta - Downloads:
woocommerce_downloadable_product_permissions,wc_download_log,wc_product_download_directories - Attributes:
woocommerce_attribute_taxonomies - Webhooks/logs:
wc_webhooks,woocommerce_log,wc_rate_limits,wc_reserved_stock - Admin:
wc_admin_notes,wc_admin_note_actions - Scheduler:
actionscheduler_actions,actionscheduler_groups,actionscheduler_logs,actionscheduler_claims
WooCommerce keeps products as a WordPress CPT (post type) but adds wc_product_meta_lookup as a flat, indexed lookup table for the fields that actually get queried on the front-end. CourseWP follows the exact same pattern: courses are a CPT, and courseswp_data is our equivalent of wc_product_meta_lookup.
We replace an expensive, fragile, general-purpose import tool with a purpose-built plugin that is faster, more reliable, cheaper to maintain, and reusable across all client sites. The approach is proven at scale by WooCommerce and endorsed by leading WordPress agencies.