Idea. Use magic links table

We need to explore this idea.

AI generated context:

Problem

Magic link keys are currently stored in wp_postmeta and wp_usermeta using a meta_key pattern
({root}_{type}_magic_key). This approach has several limitations:

1. No usage tracking - Can't see when a link was last used or how many times
2. No built-in expiration - Expiration logic lives in plugin code, not data
3. Hard to query - Can't easily list all magic links for a record or user
4. No revocation - Can only delete, no audit trail of who revoked and when
5. No scoped permissions - Can't limit what a magic link can access
6. Scattered storage - Links for posts in postmeta, links for users in usermeta

Proposed Solution

Create a dedicated dt_magic_links table that stores all magic link data in one place.

Database Schema

CREATE TABLE {prefix}dt_magic_links (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

-- What this link accesses (one of these will be set)
post_id BIGINT UNSIGNED NULL,
user_id BIGINT UNSIGNED NULL,

-- Link identity
token VARCHAR(64) NOT NULL,
link_type VARCHAR(100) NOT NULL, -- 'coaching', 'update-form', etc.

-- Scoped permissions
scope LONGTEXT NULL, -- JSON array: ["read", "update:contact_phone"]

-- Expiration
expires_at DATETIME NULL, -- NULL = never expires

-- Revocation
revoked_at DATETIME NULL,
revoked_by BIGINT UNSIGNED NULL,

-- Tracking
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
created_by BIGINT UNSIGNED NULL,
last_used_at DATETIME NULL,
use_count INT UNSIGNED DEFAULT 0,

-- Indexes
UNIQUE KEY token_idx (token),
KEY post_id_idx (post_id),
KEY user_id_idx (user_id),
KEY link_type_idx (link_type),
KEY expires_at_idx (expires_at)
) DEFAULT CHARSET=utf8mb4;

Migration Path

1. Create new table via migration
2. Update DT_Magic_URL class to read/write from new table
3. Add migration script that copies existing keys from postmeta/usermeta to new table
4. Deprecate direct postmeta access for magic keys
5. Clean up old meta entries after confirmation period

Migration Script Logic

// For each registered magic link type
foreach ($registered_types as $root => $types) {
foreach ($types as $type => $config) {
$meta_key = "{$root}_{$type}_magic_key";

// Migrate post-based magic links
$post_links = $wpdb->get_results($wpdb->prepare("
SELECT post_id, meta_value as token
FROM {$wpdb->postmeta}
WHERE meta_key = %s
", $meta_key));

foreach ($post_links as $link) {
// Insert into new table (skip if token already exists)
$wpdb->insert('dt_magic_links', [
'post_id' => $link->post_id,
'token' => $link->token,
'link_type' => $type,
'scope' => json_encode(['read', 'update:*']), // Default full access
'created_at' => current_time('mysql')
]);
}

// Similar for user-based magic links from usermeta
}
}

API Changes

New Methods for DT_Magic_URL

// Get all links for a post
DT_Magic_URL::get_links_for_post($post_id): array

// Get all links for a user
DT_Magic_URL::get_links_for_user($user_id): array

// Revoke a specific link
DT_Magic_URL::revoke_link($token, $revoked_by = null): bool

// Revoke all links for a post
DT_Magic_URL::revoke_all_links_for_post($post_id, $revoked_by = null): int

// Create link with scope
DT_Magic_URL::create_link($post_id, $type, $scope = [], $expires_at = null): string

// Check if link has specific permission
DT_Magic_URL::link_can($token, $permission): bool

Updated Validation

public function get_post_id(string $meta_key, string $public_key) {
global $wpdb;

// Update usage tracking and validate in one query
$result = $wpdb->get_row($wpdb->prepare("
UPDATE {$wpdb->prefix}dt_magic_links
SET use_count = use_count + 1, last_used_at = NOW()
WHERE token = %s
AND (expires_at IS NULL OR expires_at > NOW())
AND revoked_at IS NULL
RETURNING post_id, scope
", $public_key));

if ($result) {
$this->current_scope = json_decode($result->scope, true);
return $result->post_id;
}

return false;
}

Benefits

| Before | After |
|--------------------------------|---------------------------------------------|
| No usage data | use_count, last_used_at tracked |
| No expiration in data | expires_at column |
| Delete to revoke | revoked_at, revoked_by for audit |
| Full access only | Scoped permissions via scope column |
| Query each meta_key separately | Single table query |
| No admin visibility | Can build admin UI to view/manage all links |

New Admin UI (Future)

With this table, we can add an admin page showing:
- All active magic links
- Usage statistics
- Bulk revocation
- Expiration management

Breaking Changes

- Plugins directly querying postmeta for magic keys will need updates
- get_post_meta($post_id, '{root}_{type}_magic_key') pattern deprecated
- Bulk Magic Link Sender plugin will need updates to use new table

Backward Compatibility

During transition period:
1. Check new table first
2. Fall back to postmeta if not found
3. Auto-migrate to new table on access
4. Log deprecation warnings for direct meta access

Tasks

- Create migration file for new table
- Update DT_Magic_URL::get_post_id() to use new table
- Update DT_Magic_URL::get_user_id() to use new table
- Update DT_Magic_URL::get_link_url_for_post() to use new table
- Add migration script for existing magic links
- Add new API methods (revoke, list, scope check)
- Update Bulk Magic Link Sender plugin
- Add scope checking in magic link base class
- Update documentation
- Add admin UI for link management (optional, future PR)