Memory Wiki

I'll start by locating the relevant files and examining the database schema on the sandbox server.

Let me read the local copy of profe-sync.php and also check the Docker path.

Let me continue reading the Docker volume file to find the profe_crm functions, and also check the database for the profe_crm table.

Let me read the rest of the file and also check the archived copy.

The profe_crm functions are not in the local copies. Let me search all PHP files and attempt to SSH into the server.

Found the actual files. Let me read all three source files now.

Now let me verify the actual database table schema that was created, and also check if the `profe` vs `profe_main` database naming is correct.

Let me check the actual database schema inside the testing container.

Let me check the PMPro hook behavior and verify there's no `profe_main` database overlap.

Let me try to SSH to the actual test server and also check the staging site to see if there's a separate setup there.

Now I have all the information I need. Let me verify one more thing about the PMPro hook behavior with old_levels.

I now have a complete picture. Here is my report. --- ## Findings Report: `profe_crm` Architecture Review ### Scope Reviewed all three components on the **testing** Docker environment (not staging — see Finding #7). The staging site (test.thelinguagroup.com) has **no** `profe_crm` table, no `profe-membership-levels-sync.php`, and no Fluent snippet consumer. --- ### 1. TABLE SCHEMA — Actual vs Specified **Actual table** (in `profe` database, verified via `SHOW CREATE TABLE`): ``` CREATE TABLE profe_crm ( id bigint(20) NOT NULL AUTO_INCREMENT, user_id bigint(20) NOT NULL, tag varchar(100) NOT NULL, created_at datetime NOT NULL DEFAULT current_timestamp(), processed tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY idx_processed (processed), KEY idx_user_tag (user_id, tag) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ``` **FINDING 1a — Index is NOT unique.** The task spec says "UNIQUE INDEX on (user_id, tag)". The actual table has only `KEY idx_user_tag (user_id, tag)` — a plain non-unique index. This means: - Duplicate rows with the same (user_id, tag) **can** be inserted. - The write hook (see Finding 2) does a bare INSERT with no `ON DUPLICATE KEY UPDATE`, so duplicates **will** accumulate if `pmpro_after_change_membership_level` fires multiple times for the same user+level (e.g., on checkout retry, admin re-save, or subscription renewal). - The consumer handles this gracefully (checks `$existingTagIds` before attaching), but each duplicate creates wasted DB rows and redundant processing. **Severity:** Medium — functional but wasteful. Would be correct with a UNIQUE index. **FINDING 1b — Database name mismatch.** The task spec references `profe_main`. The actual table is in database `profe`. The `create-profe-crm-table.php` script connects to `profe_main` using `wordpress` credentials, but the `profe_main` database on staging has no `profe_crm` table (only `profe_users`, `profe_mem_levels`, `profe_recovery_questions`). The write hook and consumer both connect to `profe`. If `create-profe-crm-table.php` was ever run, it would create the table in the wrong database. --- ### 2. WRITE HOOK (`profe_crm_log_membership_change`) File: `wp-content/mu-plugins/profe-membership-levels-sync.php` (lines 121–150) ```php function profe_crm_log_membership_change(int $level_id, int $user_id, $old_levels): void { global $wpdb; if (empty($level_id)) { return; } // ← handles cancellation $level = $wpdb->get_row($wpdb->prepare( "SELECT name FROM {$wpdb->pmpro_membership_levels} WHERE id = %d", $level_id )); if (!$level || empty($level->name)) { return; } $slug = profe_map_membership_slug($level->name); try { $db = profe_get_db(); $stmt = $db->prepare("INSERT INTO profe_crm (user_id, tag) VALUES (?, ?)"); $stmt->execute([$user_id, $slug]); } catch (Exception $e) { error_log("[ProfeCRM] Log failed: " . $e->getMessage()); } } ``` **FINDING 2a — Cancellation (level_id=0) is silently ignored.** PMPro fires two `do_action` calls: | Scenario | `$level_id` | `$old_levels` | |---|---|---| | Level added/changed | new level ID | `null` | | Level cancelled | `0` | the *old* level ID | The current handler returns immediately when `$level_id` is 0 (cancellation). This means: - No row is inserted into `profe_crm` when a membership is cancelled. - The old FluentCRM tag persists on the user's contact — there is no downstream tag removal. - If a cancellation should result in a "free" tag (or any tag signifying no membership), this is not happening. The handler **could** use `$old_levels` (which holds the old level ID on cancellation) to insert a removal action, or insert a "free" tag. **FINDING 2b — No deduplication at insert time.** The INSERT is bare — no `ON DUPLICATE KEY UPDATE`. Combined with the non-unique index (Finding 1a), the same (user_id, tag) pair can be inserted multiple times. On a checkout retry or admin re-save, this creates duplicate rows. **FINDING 2c — Redundant PDO connection.** The file defines TWO separate PDO connection helpers: - `Profe_Membership_Levels_Sync::db()` (class static, connects to `profe`) - `profe_get_db()` (function-level static, connects to `profe`) Both use the same credentials but maintain independent connection pools. Not a correctness bug but unnecessary duplication. --- ### 3. CONSUMER (`1-profe-crm-tag-sync.php`) File: `wp-content/fluent-snippet-storage/1-profe-crm-tag-sync.php` (85 lines) ```php // Only run on frontend or REST API, not in admin if (is_admin() && !wp_doing_ajax() && !defined("REST_REQUEST")) { return; } try { $pdo = new PDO(...); // fresh connection every request $stmt = $pdo->prepare( "SELECT id, user_id, tag FROM profe_crm WHERE processed = 0 ORDER BY id ASC LIMIT 50" ); $stmt->execute(); $rows = $stmt->fetchAll(); foreach ($rows as $row) { // ... find contact, get/create tag, attach, mark processed } } catch (Exception $e) { error_log("[ProfeCrmTagSync] Error: " . $e->getMessage()); } ``` **FINDING 3a — Race condition: no locking.** Multiple concurrent page loads can pick up the same unprocessed rows: 1. Request A SELECTs row 1 (processed=0). 2. Request B SELECTs row 1 (processed=0) before A's UPDATE. 3. Both process row 1. 4. Both UPDATE processed=1 (second UPDATE succeeds, same value). 5. FluentCRM tag is attached twice — but the `$existingTagIds` check prevents actual duplication. **Risk:** Low — the consumer is self-healing (idempotent). But it wastes DB and API cycles on contention. **Mitigation options:** `SELECT ... FOR UPDATE SKIP LOCKED LIMIT 50` (MySQL 8+), or use `UPDATE ... LIMIT 1` + `LAST_INSERT_ID()` pattern, or process inside a transaction. **FINDING 3b — FluentCRM snippet lifecycle risk.** The consumer is stored as a FluentCRM snippet (in `fluent-snippet-storage/`). If an admin deletes it via the FluentCRM Snippets UI, the consumer stops running entirely. There is no redundancy or fallback. **FINDING 3c — Separate PDO connection (no reuse).** The consumer creates a **new** PDO object every request — no static cache. `profe_get_db()` in the other file has a static cache. This is minor but adds unnecessary connection overhead on every page load. **FINDING 3d — Missing tag namespace/safety.** `profe_map_membership_slug()` simply lowercases and replaces spaces with underscores. If a PMPro level name contains special characters (e.g., "VIP - Monthly"), the slug becomes `vip_-_monthly`. The consumer uses `FluentCrm\App\Models\Tag::firstOrCreate(["slug" => $tagSlug])`, which will create whatever slug is passed. No validation or sanitization beyond trim(). --- ### 4. EDGE CASES | Edge Case | Behavior | Issue? | |---|---|---| | `level_id=0` (cancellation) | Silent return — no row inserted | YES — no downstream tag removal | | `level_id` non-existent in PMPro | `$level->name` is null, returns early | OK — no action taken | | `user_id` doesn't exist in WP | Consumer marks row as processed, skips | OK — poison pill handled | | User exists in WP but not in FluentCRM | Consumer marks row as processed, skips | OK — no orphan rows | | Tag creation fails in FluentCRM | `firstOrCreate` throws exception, caught by outer try/catch, row stays unprocessed | MINOR — row stays unprocessed, retried on next request (but error is logged, poison row stays until fixed) | | Duplicate tag attach to contact | `$existingTagIds` check prevents re-attach | OK — idempotent | | Same user+tag inserted twice | Both rows processed, second attach is no-op | OK but wasteful | | PDO connection to `profe` DB fails | Exception caught, row stays unprocessed, error logged | OK — safe retry | --- ### 5. RACE CONDITIONS **Scenario: Two concurrent page loads during PMPro checkout.** 1. PMPro hook fires → Write hook inserts row into `profe_crm`. 2. First page load (init): Consumer picks up the row (processed=0). 3. Second page load (init): Consumer also picks up the same row before first marks it processed=1. 4. Both find the FluentCRM contact. 5. Both call `firstOrCreate` for the tag — FluentCRM handles this internally (returns existing tag). 6. Both check `$existingTagIds` — tag not yet attached, both call `attachTags`. 7. FluentCRM's `attachTags` is likely idempotent, so no harm. But the `$contact->tags` relation might be stale. 8. Both UPDATE `processed=1`. Second UPDATE is a no-op. **Verdict:** Safe but wasteful. The race window is narrow (between SELECT and UPDATE). For a site with low-to-moderate traffic, this is acceptable. For high traffic, add `FOR UPDATE SKIP LOCKED`. --- ### 6. TRANSACTION SAFETY The write hook does not use transactions — it's a single INSERT statement, which is atomic in InnoDB. The consumer does not use transactions — it processes each row independently. If processing fails mid-batch (e.g., on row 30 of 50), rows 1–29 are already marked processed and rows 31–50 remain unprocessed. **This is acceptable** because each row is independent. **No cross-database transaction issues** since both the `profe` table and FluentCRM data live in separate databases (the `profe` DB and the WordPress DB respectively). Two-phase commit would be needed for true consistency between them, which is overkill here. --- ### 7. NOT DEPLOYED TO STAGING The entire profe_crm architecture resides only on the **testing** Docker environment: - `profe` database has the `profe_crm` table with 2 rows (both processed). - Staging site (`test.thelinguagroup.com`) has `profe_main` database with no `profe_crm` table. - No `profe-membership-levels-sync.php` exists on staging. - No FluentCRM snippet consumer exists on staging. The `create-profe-crm-table.php` script targets `profe_main` with `wordpress` credentials, but the actual implementation uses `profe` with `profe` credentials. These are different code paths that haven't been reconciled. --- ### SUMMARY OF FINDINGS | # | Finding | Severity | Status | |---|---|---|---| | 1a | `idx_user_tag` is a plain INDEX, not UNIQUE — duplicates possible | **Medium** | Should be UNIQUE | | 1b | Table in `profe` DB, script targets `profe_main` DB | **Medium** | Mismatch in deployment | | 2a | `level_id=0` (cancellation) silently ignored — no tag removal | **Medium** | Missing feature | | 2b | No `ON DUPLICATE KEY UPDATE` or dedup on insert | **Low** | Wasteful with duplicates | | 2c | Two redundant PDO connection helpers in same file | **Low** | Cleanup | | 3a | Race condition — concurrent requests pick same rows | **Low** | Add SKIP LOCKED | | 3b | Consumer lives in FluentCRM snippet — can be deleted by admin | **Medium** | Fragile lifecycle | | 3c | Consumer creates fresh PDO each request (no cache) | **Low** | Performance | | 3d | No tag slug sanitization beyond trim/lowercase | **Low** | Edge case | | 4 | Missing contact stays unprocessed if tag creation fails (minor poison row) | **Low** | Acceptable | | 7 | Architecture not deployed to staging site at all | **High** | Not yet live |