8.4Masking Requirements

1. Format Preservation

Masked data must look like the original data type. Emails must look like emails. Phone numbers must be valid phone number format.

def test_masked_emails_have_valid_format(masked_db):
    cursor = masked_db.cursor()
    cursor.execute("SELECT email FROM users LIMIT 100")
    for row in cursor.fetchall():
        email = row[0]
        assert "@" in email, f"Invalid email format: {email}"
        assert "." in email.split("@")[1], f"Invalid domain: {email}"

2. Relationship Preservation

The same customer should have the same masked name across all tables. If "john@company.com" becomes "user_8a3f@masked.test" in the users table, it should be the same in the orders table and the audit log.

def test_masked_relationships_consistent(masked_db):
    cursor = masked_db.cursor()
    cursor.execute("""
        SELECT u.email AS user_email, o.customer_email AS order_email
        FROM users u
        JOIN orders o ON o.user_id = u.id
        LIMIT 50
    """)
    for row in cursor.fetchall():
        assert row[0] == row[1], \
            f"Email mismatch: users.email={row[0]}, orders.customer_email={row[1]}"

3. Referential Integrity

Foreign keys must still resolve. Masked data should not break joins.

def test_masked_data_referential_integrity(masked_db):
    cursor = masked_db.cursor()

    # No orphaned orders (all orders reference existing users)
    cursor.execute("""
        SELECT COUNT(*)
        FROM orders o
        LEFT JOIN users u ON u.id = o.user_id
        WHERE u.id IS NULL
    """)
    orphans = cursor.fetchone()[0]
    assert orphans == 0, f"Found {orphans} orders referencing non-existent users"

def test_masked_foreign_keys_valid(masked_db):
    cursor = masked_db.cursor()
    cursor.execute("""
        SELECT COUNT(DISTINCT o.user_id) AS referenced,
               (SELECT COUNT(*) FROM users) AS total_users
        FROM orders o
    """)
    row = cursor.fetchone()
    # All referenced user IDs should exist in the users table
    assert row[0] <= row[1]

4. Irreversibility

Masking must be one-way. It should not be possible to reverse the masking to recover original data.

def test_masking_is_irreversible(original_db, masked_db):
    """No original values should appear in masked data."""
    # Get a sample of original emails
    orig_cursor = original_db.cursor()
    orig_cursor.execute("SELECT email FROM users LIMIT 100")
    original_emails = {row[0] for row in orig_cursor.fetchall()}

    # Check none of them appear in masked data
    masked_cursor = masked_db.cursor()
    masked_cursor.execute("SELECT email FROM users")
    masked_emails = {row[0] for row in masked_cursor.fetchall()}

    overlap = original_emails & masked_emails
    assert len(overlap) == 0, f"Original emails found in masked data: {overlap}"