UTB Product Builder creates 11 custom tables on activation. All table names are prefixed with the WordPress table prefix (default wp_). The unprefixed names can be customized via the utb_pb_table_config option in the WordPress options table.
DB version tracking
The plugin tracks its own schema version in the utb_pb_db_version WordPress option. On each plugins_loaded call, Plugin::init() compares the stored version against the current target:
$current_db_version = get_option('utb_pb_db_version', '0.0.0');
if (version_compare($current_db_version, '3.0.1', '<')) {
\UTB_PB_DB_Schema::install_tables();
update_option('utb_pb_db_version', '3.0.1');
}
dbDelta() function, which is safe to run on existing tables — it adds missing columns and indexes without dropping data.
Resolving table names at runtime
All internal code resolves table names through UTB_PB_DB_Schema::resolve_table(string $key):
// Example
global $wpdb;
$table = UTB_PB_DB_Schema::resolve_table('utb_certificates');
// Returns: 'wp_utb_certificates' (or custom prefix + name if overridden in config)
$rows = $wpdb->get_results("SELECT * FROM {$table} WHERE activo = 1", ARRAY_A);
Always use resolve_table() instead of hardcoding $wpdb->prefix . 'utb_certificates'. This respects any table name overrides configured in the admin.
Tables
wp_utb_certificates
Master catalog of academic certificates available for sale.
CREATE TABLE wp_utb_certificates (
id bigint(20) NOT NULL AUTO_INCREMENT,
slug varchar(100) NOT NULL,
nombre varchar(255) NOT NULL,
tipo_usuario varchar(50) DEFAULT 'ambos', -- 'estudiantes', 'egresados', 'ambos'
descripcion text,
sku varchar(100) DEFAULT '', -- Integration concept code
tiempo_expedicion varchar(100),
form_config_json text, -- JSON field array for this certificate
activo tinyint(1) DEFAULT 1,
qty_enabled tinyint(1) DEFAULT 0, -- Whether multiple copies can be ordered
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY slug (slug)
);
slug — URL-safe identifier, must be unique.tipo_usuario — Restricts which user type can request this certificate.form_config_json — Optional JSON field array that overrides the flow’s default form for this certificate.qty_enabled — When 1, the quantity selector is shown in the form.
Example query:
global $wpdb;
$table = UTB_PB_DB_Schema::resolve_table('utb_certificates');
$cert = $wpdb->get_row(
$wpdb->prepare("SELECT * FROM {$table} WHERE slug = %s AND activo = 1", 'diploma-grado'),
ARRAY_A
);
wp_utb_certificate_prices
Price matrix for certificates. A single certificate can have multiple price rows varying by academic level and delivery format.
CREATE TABLE wp_utb_certificate_prices (
id bigint(20) NOT NULL AUTO_INCREMENT,
certificate_id bigint(20) NOT NULL,
nivel_code varchar(50), -- 'pregrado', 'posgrado'
nivel_label varchar(100),
formato varchar(50), -- 'digital', 'fisico'
price_cop decimal(12,2),
pricing_mode varchar(50) DEFAULT 'fixed',
notes text,
activo tinyint(1) DEFAULT 1,
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY certificate_id (certificate_id)
);
certificate_id — Foreign key to wp_utb_certificates.id.nivel_code — Matches the utb_nivel form field value.formato — Matches the utb_formato form field value.price_cop — Price in Colombian pesos.
wp_utb_programs
Academic programs catalog used for the program selector in the Certificates flow.
CREATE TABLE wp_utb_programs (
id bigint(20) NOT NULL AUTO_INCREMENT,
nombre varchar(255) NOT NULL,
nivel varchar(50), -- 'pregrado', 'posgrado'
activo tinyint(1) DEFAULT 1,
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
wp_utb_cep_programs
Continuing Education (CEP) programs catalog. Each row is a purchasable program with its own price and integration code.
CREATE TABLE wp_utb_cep_programs (
id bigint(20) NOT NULL AUTO_INCREMENT,
codigo varchar(50) NOT NULL, -- Program code from Banner
nombre varchar(255) NOT NULL,
sku varchar(100) DEFAULT '', -- Auto-populated as 'EP-{codigo}'
precio decimal(12,2),
activo tinyint(1) DEFAULT 1,
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY codigo (codigo)
);
codigo — Unique Banner program code, used as the form’s select value.sku — Auto-populated during migration as EP-{CODIGO} (uppercased). Used as the integration concept when submitting to Banner.
Example query:
global $wpdb;
$table = UTB_PB_DB_Schema::resolve_table('utb_cep_programs');
$program = $wpdb->get_row(
$wpdb->prepare("SELECT * FROM {$table} WHERE codigo = %s AND activo = 1", $programa_codigo),
ARRAY_A
);
wp_utb_cep_inscriptions
Legacy inscription log for CEP orders. Retained for backward compatibility. New installations use wp_utb_product_submissions instead.
CREATE TABLE wp_utb_cep_inscriptions (
id bigint(20) NOT NULL AUTO_INCREMENT,
order_id bigint(20) NOT NULL,
product_id bigint(20) NOT NULL,
programa_codigo varchar(50),
programa_nombre varchar(255),
precio decimal(12,2),
es_estudiante tinyint(1) DEFAULT 0,
tipo_autenticacion varchar(50),
email_autenticacion varchar(100),
codigo_autenticacion varchar(100),
created_at datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
wp_utb_product_submissions
Generic submission store. Written when an order is completed. Replaces wp_utb_cep_inscriptions as the canonical submission record for all flows.
CREATE TABLE wp_utb_product_submissions (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
order_id bigint(20) unsigned NOT NULL DEFAULT 0,
order_item_id bigint(20) unsigned NOT NULL DEFAULT 0,
product_id bigint(20) unsigned NOT NULL DEFAULT 0,
flow_id varchar(100) DEFAULT '',
form_data longtext DEFAULT NULL, -- JSON: all submitted form fields
uploaded_files longtext DEFAULT NULL, -- JSON: uploaded file URLs
payment_status varchar(50) DEFAULT 'pending',
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY order_id (order_id),
KEY product_id (product_id),
KEY flow_id (flow_id),
KEY payment_status (payment_status)
);
flow_id — Matches FlowInterface::get_id(), e.g. utb_cep_programs.form_data — Full JSON snapshot of the submitted form at the time of payment.uploaded_files — JSON array of file paths stored under wp-content/utb-private-uploads/submissions/.payment_status — Mirrors the WooCommerce order status at time of writing.
Example query:
global $wpdb;
$table = UTB_PB_DB_Schema::resolve_table('utb_product_submissions');
$submissions = $wpdb->get_results(
$wpdb->prepare(
"SELECT * FROM {$table} WHERE order_id = %d",
$order_id
),
ARRAY_A
);
Stores product→flow assignments and the form configuration JSON edited through the admin Form Builder.
CREATE TABLE wp_utb_form_configs (
id bigint(20) NOT NULL AUTO_INCREMENT,
product_id bigint(20) NOT NULL,
flow_id varchar(100) NOT NULL,
form_config longtext, -- JSON field array
custom_css text,
pricing_config text,
ajax_endpoints text,
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY product_flow (product_id, flow_id)
);
product_id + flow_id — Composite unique key that is the primary routing lookup.form_config — The JSON array of field objects read by FormConfigManager.custom_css — Scoped CSS injected as an inline <style> block inside the form wrapper.
wp_utb_data_sources
Configurable data sources for dynamic <select> fields. Records here power DataSourceManager::get_options().
CREATE TABLE wp_utb_data_sources (
id bigint(20) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL, -- Identifier, e.g. 'utb_cep_programs'
label varchar(255) NOT NULL,
type varchar(50) NOT NULL, -- 'db_table', 'rest_api', etc.
config longtext NOT NULL, -- JSON: table, columns, filters
active tinyint(1) DEFAULT 1,
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY name (name),
KEY active (active)
);
utb_programs — Academic programs from wp_utb_programsutb_cep_programs — CEP programs from wp_utb_cep_programsutb_certificates — Certificate catalog from wp_utb_certificatesutb_certificate_prices — Price matrix from wp_utb_certificate_prices
wp_utb_validation_rules
Reusable validation rule definitions. Rules defined here can be attached to form fields in the Form Builder admin UI.
CREATE TABLE wp_utb_validation_rules (
id bigint(20) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL, -- Identifier, e.g. 'email_utb'
label varchar(255) NOT NULL,
type varchar(50) NOT NULL, -- 'regex', 'api', etc.
config longtext NOT NULL, -- JSON: pattern, flags, endpoint
error_message text,
active tinyint(1) DEFAULT 1,
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY name (name),
KEY active (active)
);
| Name | Type | Pattern |
|---|
email_utb | regex | ^[a-zA-Z0-9._%+-]+@utb\.edu\.co$ |
phone_colombia | regex | ^\+?57[0-9]{10}$ |
cedula_colombia | regex | ^[0-9]{7,10}$ |
numeric_only | regex | ^[0-9]+$ |
alphanumeric | regex | ^[a-zA-Z0-9]+$ |
no_special_chars | regex | ^[a-zA-Z0-9\s]+$ |
wp_utb_file_access_logs
Audit log for every download of a file stored in the private uploads directory.
CREATE TABLE wp_utb_file_access_logs (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
submission_id bigint(20) unsigned NOT NULL,
file_hash varchar(100) NOT NULL,
user_id bigint(20) unsigned DEFAULT 0,
user_ip varchar(45) DEFAULT '',
user_agent text DEFAULT NULL,
access_type varchar(50) DEFAULT 'download',
accessed_at datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY submission_id (submission_id),
KEY file_hash (file_hash),
KEY user_id (user_id),
KEY accessed_at (accessed_at)
);
wp_utb_webhook_logs
Delivery log for outbound webhook notifications sent when an order completes.
CREATE TABLE wp_utb_webhook_logs (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
order_id bigint(20) unsigned NOT NULL,
webhook_url varchar(500) NOT NULL,
status varchar(50) NOT NULL, -- 'success', 'failed'
attempt tinyint(2) NOT NULL DEFAULT 1, -- 1–3 retry attempts
response_code int(11) DEFAULT NULL,
response_body text DEFAULT NULL,
error_message text DEFAULT NULL,
created_at datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY order_id (order_id),
KEY status (status),
KEY created_at (created_at)
);
attempt — Increments up to 3 on retry. Delivery failures are retried automatically.status — Final delivery outcome: success or failed.
Manual table installation
If the activation hook does not fire (for example, after a ZIP upload bypassing the normal installer), run the install script with WP-CLI:
wp eval-file wp-content/plugins/utb-product-builder/install_tables.php
UTB_PB_DB_Schema::install_tables() directly and outputs a confirmation with the count of seeded data sources and validation rules.