Add Customer ID Schema & Basic Audit Logging for MVP Data Isolation

Customer ID Schema & Basic Audit Logging

Summary

Add customer_id to all database tables and implement basic audit logging to support data isolation and privacy compliance. Required foundation for anonymization layer (#6337) and enterprise customer trust.

Business Context

• Problem: Single-tenant database with no customer data isolation
• Risk: Cross-customer data leakage, no audit trail for compliance
• Solution: Row-level customer scoping + basic audit logging for transparency

Database Schema Changes

Add customer_id to Core Tables

File: arc-agent/database/schemas.sql

Tables requiring customer_id:

• conversations - Slack conversation threads
• plans - Teacher-generated execution plans
• executions - Student workflow execution history
• results - Query results and synthesis outputs
• temporal_workflows - Workflow execution metadata

New Audit Log Table

CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    customer_id VARCHAR(255) NOT NULL,
    user_id VARCHAR(255),
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(100) NOT NULL,
    resource_id VARCHAR(255),
    details JSONB,
    timestamp TIMESTAMPTZ DEFAULT NOW(),
    ip_address INET,
    user_agent TEXT
);

Code Integration Points

1. Database Repository Layer

File: arc-agent/database/repository.py

• Add customer_id parameter to all CRUD operations
• Implement row-level security filters
• Add audit logging wrapper functions

2. Temporal Workflow Context

File: arc-agent/orchestrator/temporal/workflows/arc_conversation_workflow.py

• Extract customer_id from Slack workspace/user context
• Pass customer_id through all workflow activities
• Log workflow start/completion events

3. Slack Bot Handler

File: arc-agent/slack_bot/handlers.py

• Derive customer_id from Slack team/workspace ID
• Log user interactions (conversation start, commands)
• Ensure all DB operations include customer_id

4. Agent Activities

Files:

• arc-agent/orchestrator/temporal/activities/teacher_activities.py
• arc-agent/orchestrator/temporal/activities/student_activities.py

Add audit logging for:

• Plan generation/modification
• Salesforce query execution
• Result synthesis completion

Audit Events to Track

Critical Events (MVP)

• conversation_started - User initiates new conversation
• plan_generated - Teacher creates execution plan
• plan_executed - Student completes workflow
• salesforce_query - CRM data accessed
• data_exported - Customer downloads their data
• data_deleted - Customer requests data deletion

Event Schema

{
  "customer_id": "workspace_12345",
  "user_id": "slack_user_67890", 
  "action": "salesforce_query",
  "resource_type": "opportunity",
  "resource_id": "query_abc123",
  "details": {
    "query_type": "opportunity_list",
    "record_count": 25,
    "anonymized": true
  }
}

Implementation Phases

Phase 1: Schema Foundation (Week 1)

• [ ] Add customer_id columns to all tables
• [ ] Create audit_logs table
• [ ] Update repository.py with customer scoping
• [ ] Migration scripts for existing data

Phase 2: Workflow Integration (Week 2)

• [ ] Extract customer_id in Slack handlers
• [ ] Pass customer_id through Temporal workflows
• [ ] Add audit logging to core activities

Phase 3: Customer-Facing Features (Week 3)

• [ ] Data export endpoint (customer downloads their audit log)
• [ ] Data deletion endpoint (customer purges their data)
• [ ] Basic audit dashboard in Slack bot

Customer Trust Features

Transparency Commands

• /arc audit - Show recent activity for this customer
• /arc data export - Download all customer data
• /arc data delete - Purge all customer data (with confirmation)

Privacy Controls

• Customer-scoped data retention (30/90 days configurable)
• Clear audit trail of what data was accessed when
• Proof that customer data never mixed with other customers

Success Criteria

• [ ] All database records tagged with customer_id
• [ ] No cross-customer data access possible
• [ ] Audit trail of all sensitive operations
• [ ] Customer can see exactly what data exists about them
• [ ] Customer can delete all their data on demand

Dependencies

• Prerequisite for anonymization layer (#6337)
• Required for enterprise security reviews
• Foundation for SOC 2 compliance path

Priority: High (blocks anonymization implementation)
Effort: 2-3 weeks
Risk: Medium (database migration required)