Idea. Use magic links table

[corsacca]

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)