7. Remove redundant companyId fields across schema models

Date: 2025-01-27

Status

Proposed

Context

During our migration from Fauna to PostgreSQL, we maintained both companyId and tenantId fields in several models to ensure compatibility and smooth data migration. However, analysis reveals that these fields always contain the same value and serve identical purposes:

1. Historical Redundancy: Both companyId and tenantId store the same value - the tenant identifier for multi-tenancy isolation
2. Fauna Migration Legacy: This duplication was necessary during our Fauna-to-PostgreSQL migration to maintain data consistency
3. PostgreSQL Simplification: With PostgreSQL, we no longer need both fields since they represent the same concept
4. Data Integrity Risk: Maintaining both fields creates the possibility of them getting out of sync, leading to data inconsistency
5. Query Complexity: Indexes and queries often prioritize tenantId over companyId, suggesting tenantId is the preferred field for multi-tenancy

Reference Implementation: SelfCheckInRequest

The new SelfCheckInRequest model will be implemented correctly from the start, using only tenantId for multi-tenancy. This will serve as our reference implementation and example for how all multi-tenant models should be structured in our PostgreSQL-based architecture. The model is used for self-check-in functionality where customers complete check-in processes without direct staff intervention.

Decision

We will establish a consistent multi-tenancy pattern across our PostgreSQL-based schema by removing the redundant companyId fields that were necessary during our Fauna migration. Since both fields contain identical values, we will standardize on tenantId for all multi-tenant functionality. This initiative will be implemented in phases, starting with SelfCheckInRequest as our reference implementation.

Phase 1: Reference Implementation (SelfCheckInRequest)

We will implement the SelfCheckInRequest model correctly from the start, using only tenantId. This will serve as our reference implementation for future models.

Implementation Requirements for SelfCheckInRequest

1. Database Schema:

   • Implement SelfCheckInRequest model with only tenantId field (no companyId)
   • Ensure proper indexing with @@index([tenantId, order])

2. GraphQL Schema:

   • Define SelfCheckInRequest type with only tenantId field
   • Create appropriate input types without companyId

3. Resolver Implementation:

   • Implement createSelfCheckInRequest resolver using only tenantId
   • Ensure all queries use tenantId for filtering

4. Service Layer:

   • Implement SelfCheckInRequestService and SelfCheckInRequestsService using only tenantId
   • Follow established patterns from other tenant-scoped services

Phase 2: Schema-wide Cleanup (Future)

After successful reference implementation, we will systematically remove the redundant companyId fields from existing models, keeping only tenantId:

• update_logs - Remove companyId (contains same value as tenantId)
• user_feedback - Remove companyId (contains same value as tenantId)
• Additional models identified during comprehensive schema audit

Standardization Principles

1. Single Source of Truth: Use only tenantId for multi-tenancy isolation
2. Consistent Indexing: All multi-tenant models should have @@index([tenantId])
3. Query Patterns: All tenant-scoped queries should use tenantId for filtering
4. Data Migration: Ensure proper migration scripts for each model

Consequences

Benefits

• Data Consistency: Eliminates the risk of companyId and tenantId getting out of sync
• Simplified Data Model: Reduces redundancy and follows the established pattern used by other models
• Cleaner Code: Removes unnecessary field assignments and reduces complexity
• Better Performance: Slightly reduced storage and memory usage per record

Drawbacks & Mitigations

Risk	Impact	Mitigation
Breaking changes for existing clients	API compatibility issues	Update GraphQL schema and ensure proper versioning
Data migration complexity	Potential data loss	Create proper migration script to handle existing data
Query performance	Slower queries if companyId was used for filtering	Ensure proper indexing on tenantId (already exists)

Migration Strategy

Phase 1: SelfCheckInRequest Reference Implementation

1. Model Design: Design SelfCheckInRequest model with only tenantId field
2. Database Schema: Create migration for the new model
3. GraphQL Schema: Implement GraphQL types and resolvers
4. Service Implementation: Create services following established patterns
5. Validation: Thoroughly test the reference implementation

Phase 2: Schema-wide Implementation

1. Comprehensive Audit: Identify all models with redundant companyId fields
2. Impact Assessment: Evaluate each model’s usage patterns and dependencies
3. Prioritization: Order models by complexity and business impact
4. Systematic Migration: Apply the same pattern to each identified model
5. Documentation: Update architecture documentation and guidelines

Success Criteria

• Phase 1: SelfCheckInRequest model successfully implemented with only tenantId as reference
• Phase 2: All existing multi-tenant models follow consistent tenantId-only pattern
• Performance: No degradation in query performance
• Data Integrity: No data loss or corruption during migrations
• Consistency: All new models follow the established tenantId-only pattern

This initiative aligns with our PostgreSQL-based architecture and will significantly simplify our data model by removing the redundant fields that were necessary during our Fauna migration. Since companyId and tenantId contain identical values, we can safely standardize on tenantId while maintaining all necessary functionality. The phased approach allows us to establish the correct pattern with a new model before applying it to existing models across the entire schema.