TVL Platform - PII Handling & Data Security

Summary

This document defines PII classification, encryption strategies, data retention policies, and GDPR compliance measures for the TVL platform's PostgreSQL database.

PII Classification
Encryption Strategy
Data Retention Policies
GDPR Compliance
Data Access Controls
Incident Response
Validation & Alternatives

PII Classification

PII Categories

The TVL platform handles the following categories of Personally Identifiable Information (PII):

High Sensitivity PII (Red)

Payment Information: Credit card numbers, CVV, bank account numbers (handled by Stripe, NOT stored in TVL database)
Government IDs: Passport numbers, driver's license numbers (not collected in MVP)
Authentication Credentials: API keys, webhook secrets, OAuth tokens

Storage: NEVER stored in TVL database; delegated to Stripe for payment data

Medium Sensitivity PII (Orange)

User Identity: Full name, email address, phone number
Guest Information: Booking guest names, emails, phones
Location Data: Property addresses, GPS coordinates
User Activity: Audit logs with IP addresses, user agents

Storage: Encrypted at rest (application-level encryption recommended)

Low Sensitivity PII (Yellow)

Organization Data: Organization name, subdomain
Property Metadata: Property descriptions, amenity lists
Booking Metadata: Booking numbers, check-in/out dates (without guest info)

Storage: Standard database security (RLS, TLS in transit)

PII Field Inventory

Table	Column	Sensitivity	Encryption Required	Notes
`users`	`email`	Medium	Recommended	Used for authentication
`users`	`full_name`	Medium	Recommended	Display name
`users`	`avatar_url`	Low	No	Public URL
`users`	`auth_provider_id`	Medium	Recommended	External auth system ID
`bookings`	`guest_name`	Medium	Required	Primary guest identity
`bookings`	`guest_email`	Medium	Required	Guest contact
`bookings`	`guest_phone`	Medium	Required	Guest contact
`booking_guests`	`guest_name`	Medium	Required	Additional guest identity
`booking_guests`	`guest_email`	Medium	Required	Additional guest contact
`booking_guests`	`guest_phone`	Medium	Required	Additional guest contact
`properties`	`address_line1`	Medium	Recommended	Physical location
`properties`	`address_line2`	Medium	Recommended	Apartment/unit number
`properties`	`latitude`	Medium	No	GPS coordinates (public)
`properties`	`longitude`	Medium	No	GPS coordinates (public)
`connector_configs`	`api_key_encrypted`	High	Required	External API credentials
`connector_configs`	`api_secret_encrypted`	High	Required	External API credentials
`connector_configs`	`webhook_secret`	High	Required	Webhook verification
`audit_logs`	`ip_address`	Medium	Recommended	User activity tracking
`audit_logs`	`user_agent`	Low	No	Browser/client info

Encryption Strategy

Encryption at Rest

Database-Level Encryption (Supabase Default)

Provider: Supabase (AWS RDS encryption)
Method: AES-256 block-level encryption
Key Management: AWS KMS (managed by Supabase)
Coverage: All tables, indexes, backups
Status: Enabled by default (no configuration required)

Limitations: Database-level encryption protects against disk theft but does NOT protect against:

Database compromise (attacker with database access can read plaintext)
SQL injection attacks
Insider threats (DBAs can read data)

Application-Level Encryption (Recommended for PII)

Fields: guest_name, guest_email, guest_phone, api_key_encrypted, api_secret_encrypted
Method: AES-256-GCM (authenticated encryption)
Key Storage: Environment variables (NOT in database)
Key Rotation: Annual rotation with re-encryption

Implementation Example:

import { createCipheriv, createDecipheriv, randomBytes } from 'crypto';

// Encryption configuration
const ENCRYPTION_KEY = process.env.ENCRYPTION_KEY; // 32-byte hex string
const ALGORITHM = 'aes-256-gcm';

export function encrypt(plaintext: string): string {
  const iv = randomBytes(16);
  const cipher = createCipheriv(ALGORITHM, Buffer.from(ENCRYPTION_KEY, 'hex'), iv);

  let encrypted = cipher.update(plaintext, 'utf8', 'hex');
  encrypted += cipher.final('hex');

  const authTag = cipher.getAuthTag();

  // Format: iv:authTag:ciphertext (hex encoded)
  return `${iv.toString('hex')}:${authTag.toString('hex')}:${encrypted}`;
}

export function decrypt(ciphertext: string): string {
  const [ivHex, authTagHex, encrypted] = ciphertext.split(':');

  const decipher = createDecipheriv(
    ALGORITHM,
    Buffer.from(ENCRYPTION_KEY, 'hex'),
    Buffer.from(ivHex, 'hex')
  );

  decipher.setAuthTag(Buffer.from(authTagHex, 'hex'));

  let decrypted = decipher.update(encrypted, 'hex', 'utf8');
  decrypted += decipher.final('utf8');

  return decrypted;
}

Usage in Application:

// Before INSERT
const encryptedName = encrypt(booking.guest_name);
await db.bookings.insert({
  guest_name: encryptedName,
  guest_email: encrypt(booking.guest_email),
  // ...
});

// After SELECT
const booking = await db.bookings.findById(bookingId);
const decryptedName = decrypt(booking.guest_name);

Encryption in Transit

TLS/SSL Configuration

Supabase Connection: TLS 1.2+ enforced
API Endpoints: HTTPS only (HTTP redirects to HTTPS)
Certificate: Let's Encrypt (auto-renewed via Vercel/Railway)
Cipher Suites: Modern ciphers only (no TLS 1.0/1.1)

Connection String Example:

postgresql://user:password@db.supabase.co:5432/postgres?sslmode=require

API Security Headers:

Strict-Transport-Security: max-age=31536000; includeSubDomains
X-Content-Type-Options: nosniff
X-Frame-Options: DENY
Content-Security-Policy: default-src 'self'

Key Management

Encryption Key Lifecycle

Generation:

# Generate 256-bit key (32 bytes)
openssl rand -hex 32

Storage:
- Development: .env.local (excluded from git)
- Staging: Railway environment variables
- Production: Railway environment variables (team access only)
Rotation (Annual):
- Generate new key
- Re-encrypt all PII fields with new key (background job)
- Deprecate old key after 30-day overlap period

Key Rotation Script:

async function rotateEncryptionKey(oldKey: string, newKey: string) {
  const tables = [
    { table: 'bookings', columns: ['guest_name', 'guest_email', 'guest_phone'] },
    { table: 'booking_guests', columns: ['guest_name', 'guest_email', 'guest_phone'] },
    { table: 'connector_configs', columns: ['api_key_encrypted', 'api_secret_encrypted'] },
  ];

  for (const { table, columns } of tables) {
    const records = await db[table].findAll();

    for (const record of records) {
      const updates: any = {};
      for (const col of columns) {
        if (record[col]) {
          const decrypted = decryptWithKey(record[col], oldKey);
          updates[col] = encryptWithKey(decrypted, newKey);
        }
      }
      await db[table].update(record.id, updates);
    }
  }
}

Data Retention Policies

Retention Periods

Data Category	Retention Period	Deletion Method	Rationale
Bookings (active)	Until check-out + 30 days	Soft delete	Guest support window
Bookings (completed)	7 years	Archive to cold storage	Tax/legal compliance
Payments	7 years	Archive to cold storage	Financial compliance
Transactions	7 years	Archive to cold storage	Audit trail
Audit Logs	3 years	Hard delete	Compliance/debugging
Analytics Events	2 years	Hard delete	Product analytics
Webhook Logs	90 days	Hard delete	Debugging only
Sync Logs	90 days	Hard delete	Debugging only
Quotes	30 days	Hard delete	Expired quotes
Holds	7 days after expiration	Hard delete	Expired holds
User Accounts (deleted)	30 days soft delete, then purge	Hard delete after 30 days	Right to be forgotten

Automated Retention Jobs

Daily Cleanup Job (runs at 2 AM UTC):

-- Delete expired quotes (older than 30 days)
DELETE FROM quotes
WHERE created_at < NOW() - INTERVAL '30 days';

-- Delete expired holds (older than 7 days after expiration)
DELETE FROM holds
WHERE expires_at < NOW() - INTERVAL '7 days';

-- Delete old webhook logs (older than 90 days)
DELETE FROM webhook_logs
WHERE received_at < NOW() - INTERVAL '90 days';

-- Delete old sync logs (older than 90 days)
DELETE FROM sync_logs
WHERE started_at < NOW() - INTERVAL '90 days';

-- Delete old analytics events (older than 2 years)
DELETE FROM analytics_events
WHERE timestamp < NOW() - INTERVAL '2 years';

-- Delete old audit logs (older than 3 years)
DELETE FROM audit_logs
WHERE created_at < NOW() - INTERVAL '3 years';

-- Purge soft-deleted users (deleted > 30 days ago)
DELETE FROM users
WHERE deleted_at IS NOT NULL AND deleted_at < NOW() - INTERVAL '30 days';

Annual Archival Job (runs January 1st):

async function archiveOldBookings() {
  // Archive bookings older than 7 years to S3 cold storage
  const cutoffDate = new Date();
  cutoffDate.setFullYear(cutoffDate.getFullYear() - 7);

  const oldBookings = await db.bookings
    .where('created_at', '<', cutoffDate)
    .select('*');

  // Export to JSON and upload to S3
  const archive = {
    archived_at: new Date().toISOString(),
    count: oldBookings.length,
    data: oldBookings,
  };

  await s3.putObject({
    Bucket: 'tvl-archives',
    Key: `bookings/archive-${cutoffDate.getFullYear()}.json.gz`,
    Body: gzip(JSON.stringify(archive)),
    StorageClass: 'GLACIER',
  });

  // Delete from database after successful archive
  await db.bookings.deleteMany({ id: { in: oldBookings.map(b => b.id) } });
}

Data Subject Rights

Implementation: API endpoint /api/v1/gdpr/data-export

Response Format:

{
  "user_id": "uuid",
  "email": "user@example.com",
  "data_collected_at": "2025-01-24T10:30:00Z",
  "data": {
    "profile": { "full_name": "...", "email": "...", "phone": "..." },
    "bookings": [ { "booking_number": "...", "check_in": "...", ... } ],
    "payments": [ { "amount": 150.00, "status": "succeeded", ... } ],
    "audit_logs": [ { "action": "login", "timestamp": "...", ... } ]
  }
}

Delivery: Secure download link (expires after 24 hours)

Implementation: API endpoint /api/v1/gdpr/delete-account

Process:

Soft delete user account (users.deleted_at = NOW())

Anonymize PII in bookings:

UPDATE bookings
SET guest_name = 'DELETED',
    guest_email = 'deleted@privacy.tvl',
    guest_phone = NULL
WHERE id IN (SELECT booking_id FROM bookings WHERE user_id = ?);

Remove from active sessions
Schedule hard delete after 30-day grace period
Send confirmation email

Exceptions (GDPR Article 17(3)):

Bookings with active payments (retain for financial compliance)
Transactions (retain for 7 years per tax law)
Audit logs (retain for 3 years for legal compliance)

Implementation: Standard update endpoints with audit logging

Example:

app.patch('/api/v1/users/me', async (req, res) => {
  const { email, full_name } = req.body;

  await db.users.update(req.user.id, { email, full_name });

  // Log change for audit trail
  await db.audit_logs.insert({
    org_id: req.user.org_id,
    user_id: req.user.id,
    action: 'updated',
    resource_type: 'users',
    resource_id: req.user.id,
    changes: { email: { old: req.user.email, new: email } },
  });

  res.json({ success: true });
});

Implementation: Export to machine-readable format (JSON, CSV)

Supported Formats:

JSON (structured data)
CSV (tabular exports for bookings, payments)

Email marketing (opt-in checkbox at registration)
SMS notifications (opt-in checkbox at registration)
Analytics tracking (opt-out banner)

Consent Storage:

ALTER TABLE users ADD COLUMN consent_email_marketing BOOLEAN DEFAULT false;
ALTER TABLE users ADD COLUMN consent_sms_notifications BOOLEAN DEFAULT false;
ALTER TABLE users ADD COLUMN consent_analytics_tracking BOOLEAN DEFAULT true;
ALTER TABLE users ADD COLUMN consent_updated_at TIMESTAMPTZ;

Consent Withdrawal:

app.post('/api/v1/users/me/consent/withdraw', async (req, res) => {
  const { consent_type } = req.body; // 'email_marketing', 'sms_notifications', 'analytics_tracking'

  await db.users.update(req.user.id, {
    [`consent_${consent_type}`]: false,
    consent_updated_at: new Date(),
  });

  // Stop processing immediately (remove from mailing lists, etc.)
  await emailService.unsubscribe(req.user.email);

  res.json({ success: true });
});

Data Processing Agreements (DPA)

Third-Party Data Processors

Processor	Purpose	Data Shared	DPA Status
Supabase	Database hosting	All platform data	✅ GDPR-compliant (AWS Frankfurt region option)
Stripe	Payment processing	Payment info, guest email	✅ GDPR-compliant (PCI-DSS certified)
Hostaway	PMS integration	Property data, bookings	✅ DPA signed
SendGrid/AWS SES	Email delivery	Email addresses, names	✅ GDPR-compliant
Twilio	SMS delivery	Phone numbers	✅ GDPR-compliant
Upstash Redis	Caching/queues	Session data (no PII)	✅ GDPR-compliant (EU region)
Railway	Backend hosting	All platform data	✅ GDPR-compliant
Vercel	Frontend hosting	Session tokens (no PII)	✅ GDPR-compliant

Data Transfer Mechanisms:

EU Region Selection: Supabase (AWS Frankfurt), Upstash (EU region)
Standard Contractual Clauses (SCCs): For US-based services (Stripe, Twilio)

Data Access Controls

Role-Based Access Control (RBAC)

Role	Access Level	PII Access	Notes
Platform Admin	Full access	Yes (all orgs)	TVL internal staff only
Owner	Org-wide	Yes (own org)	Organization owner
Manager	Org-wide (read/write)	Yes (own org)	Property manager
Staff	Limited (assigned properties)	Partial (booking details only)	Operations staff
Finance	Financial data only	No (anonymized bookings)	Accounting team

RLS Policy Example:

-- Users can only access bookings in their organization
CREATE POLICY "Users can access bookings in their organization"
ON bookings FOR ALL
USING (org_id = current_setting('app.current_org_id')::uuid);

-- Staff can only access bookings for properties they manage
CREATE POLICY "Staff can access assigned property bookings"
ON bookings FOR SELECT
USING (
  unit_id IN (
    SELECT u.id FROM units u
    JOIN properties p ON u.property_id = p.id
    JOIN property_managers pm ON p.id = pm.property_id
    WHERE pm.user_id = current_setting('app.current_user_id')::uuid
  )
);

Database Access Logging

Enable PostgreSQL Audit Logging (Supabase Dashboard):

Log all SELECT, UPDATE, DELETE on PII tables
Retention: 90 days
Review: Weekly automated reports to security team

Example Audit Log Entry:

{
  "timestamp": "2025-01-24T10:30:00Z",
  "user": "postgres",
  "database": "tvl_production",
  "query": "SELECT guest_name, guest_email FROM bookings WHERE id = ?",
  "parameters": ["uuid-123"],
  "source_ip": "10.0.1.50"
}

Incident Response

Data Breach Response Plan

Phase 1: Detection & Containment (0-1 hour)

Detect: Automated alerts (Sentry, Supabase logs)
Isolate: Revoke compromised credentials immediately
Assess: Determine scope (which tables/records affected)
Notify: Alert incident response team via PagerDuty

Phase 2: Investigation (1-24 hours)

Root Cause Analysis: Review logs, identify attack vector
Data Impact Assessment: Count affected users/records
Regulatory Review: Determine if notification required (GDPR Article 33: 72-hour deadline)

Phase 3: Notification (24-72 hours)

Data Protection Authority (if >1000 users affected or high-risk data)
Affected Users (email notification with mitigation steps)
Public Disclosure (if legally required)

Phase 4: Remediation

Patch Vulnerability: Deploy security fixes
Key Rotation: Rotate all encryption keys, API keys
Password Reset: Force password reset for affected users
Post-Mortem: Document incident, update security policies

Notification Template:

Subject: Important Security Notice - TVL Platform Data Incident

Dear [User],

We are writing to inform you of a security incident that may have affected your account.

What Happened:
On [date], we discovered unauthorized access to [affected system]. We immediately took action to secure our systems and investigate the incident.

What Information Was Involved:
The following information may have been accessed:
- [List of PII fields]

What We Are Doing:
- We have secured the vulnerability
- We are working with security experts to investigate
- We have notified the relevant authorities

What You Should Do:
- Change your password immediately
- Monitor your account for suspicious activity
- Contact us at security@tvl.com with any concerns

We sincerely apologize for this incident and are committed to protecting your data.

Sincerely,
The TVL Security Team

Validation & Alternatives

Security Architecture Decisions

✅ Agree: Application-level encryption for high-sensitivity PII

Alternative: Database-level encryption only (Supabase default)
Trade-off: Application-level adds complexity but provides defense in depth

✅ Agree: 7-year retention for financial records

Alternative: 10 years (some jurisdictions require)
Trade-off: 7 years sufficient for US/EU, can extend per jurisdiction

✅ Agree: Soft delete with 30-day grace period

Alternative: Immediate hard delete on user request
Trade-off: Grace period allows recovery of accidental deletions, compliant with GDPR Article 17

⚠️ Consider: Field-level encryption vs. full row encryption

Current: Individual field encryption (guest_name, guest_email, etc.)
Alternative: Encrypt entire booking JSON and store in single column
Recommendation: Field-level allows granular access control and indexing

⚠️ Consider: Tokenization for guest emails

Current: Encrypted storage
Alternative: Store hashed token, keep plaintext in external vault (e.g., HashiCorp Vault)
Recommendation: Tokenization adds complexity; encryption sufficient for MVP

Known Gaps & Assumptions

Assumptions

Supabase default encryption (AES-256) sufficient for database-level protection
US/EU operations only (no APAC-specific regulations like China PIPL)
Annual key rotation acceptable (not quarterly)
7-year retention compliant with all target markets

Gaps

No Hardware Security Module (HSM) for key storage
- Risk: Encryption keys in environment variables (accessible to admins)
- Mitigation: Upgrade to AWS KMS or HashiCorp Vault for production
No Data Loss Prevention (DLP) tooling
- Risk: Accidental PII leakage in logs, error messages
- Mitigation: Implement log sanitization (redact PII before logging)
No Automated PII Discovery in codebase
- Risk: Developers inadvertently log PII
- Mitigation: Add pre-commit hooks to scan for PII patterns (regex for emails, phones)
No Customer-Managed Encryption Keys (CMEK)
- Risk: Supabase manages encryption keys (not customer-controlled)
- Mitigation: Enterprise Supabase plan supports CMEK if needed

Compliance Checklist

PCI-DSS Compliance (Delegated to Stripe)

No card data stored in TVL database
Stripe-hosted payment forms
Tokenized payment methods only
TLS for all payment transactions

SOC 2 Type II (Future)

Formal security policies documented
Annual penetration testing
Security awareness training for team
Incident response drills (tabletop exercises)
Third-party audit (deferred until Series A)

Sources

/mnt/c/GitHub/claude-test/docs/04-data/schema-overview.md
/mnt/c/GitHub/claude-test/docs/04-data/data-dictionary.md
GDPR Official Text (Regulation EU 2016/679)
Supabase Security Documentation
OWASP Application Security Guidelines

Summary​

Table of Contents​

PII Classification​

PII Categories​

High Sensitivity PII (Red)​

Medium Sensitivity PII (Orange)​

Low Sensitivity PII (Yellow)​

PII Field Inventory​

Encryption Strategy​

Encryption at Rest​

Database-Level Encryption (Supabase Default)​

Application-Level Encryption (Recommended for PII)​

Encryption in Transit​

TLS/SSL Configuration​

Key Management​

Encryption Key Lifecycle​

Data Retention Policies​

Retention Periods​

Automated Retention Jobs​

GDPR Compliance​

Data Subject Rights​

Right to Access (GDPR Article 15)​

Right to Erasure / Right to be Forgotten (GDPR Article 17)​

Right to Rectification (GDPR Article 16)​

Right to Data Portability (GDPR Article 20)​

Consent Management​

Explicit Consent Required For:​

Data Processing Agreements (DPA)​

Third-Party Data Processors​

Data Access Controls​

Role-Based Access Control (RBAC)​

Database Access Logging​

Incident Response​

Data Breach Response Plan​

Phase 1: Detection & Containment (0-1 hour)​

Phase 2: Investigation (1-24 hours)​

Phase 3: Notification (24-72 hours)​

Phase 4: Remediation​

Validation & Alternatives​

Security Architecture Decisions​

Known Gaps & Assumptions​

Assumptions​

Gaps​

Compliance Checklist​