Introduce system user for automated changes

[GitAddRemote]

Technical Summary

Introduce a dedicated system user record used for all automated changes (e.g., UEX sync jobs) so that added_by and modified_by foreign keys are consistent and never tied to real user accounts.

Epic

Part of #10 - Inventory MVP epic

Technical Elaboration

Architecture Decisions

System User Pattern

• Single, application-managed user account representing automated processes
• Non-authenticatable (no password, no OAuth providers, no sessions)
• Stable UUID that never changes across environments
• Excluded from all user-facing queries and member lists

Rationale

• Audit trail integrity: Clearly distinguish automated changes from human actions
• FK consistency: Avoid NULL values in added_by/modified_by (enables better query optimization)
• Security: Prevent accidental exposure of system operations as user activity
• Maintainability: Single pattern for all background jobs, cron tasks, migrations

Schema Changes

Users Table Extension

ALTER TABLE users ADD COLUMN is_system_user BOOLEAN NOT NULL DEFAULT FALSE;

CREATE INDEX idx_users_system ON users(is_system_user) WHERE is_system_user = TRUE;
CREATE INDEX idx_users_non_system ON users(id) WHERE is_system_user = FALSE;  -- For normal user queries

System User Seed Migration

-- Migration: 003_seed_system_user.sql
INSERT INTO users (
  id,  -- Use fixed UUID for consistency
  email,
  username,
  display_name,
  is_system_user,
  email_verified,
  active,
  date_added,
  date_modified
) VALUES (
  '00000000-0000-0000-0000-000000000001'::uuid,  -- Reserved UUID
  'system@station.internal',
  'station-system',
  'Station System',
  TRUE,
  TRUE,  -- Mark verified to pass validation
  TRUE,
  NOW(),
  NOW()
)
ON CONFLICT (id) DO NOTHING;  -- Idempotent

-- Ensure system user is never returned in normal queries
CREATE OR REPLACE VIEW users_active AS
SELECT * FROM users WHERE is_system_user = FALSE AND active = TRUE;

Application Layer

System User Service

// services/system-user.service.ts
import { Injectable, OnModuleInit } from '@nestjs/common';
import { PrismaService } from './prisma.service';

@Injectable()
export class SystemUserService implements OnModuleInit {
  private systemUserId: string | null = null;
  private readonly SYSTEM_USER_UUID = '00000000-0000-0000-0000-000000000001';

  constructor(private prisma: PrismaService) {}

  async onModuleInit(): Promise<void> {
    // Cache system user ID at startup
    const systemUser = await this.prisma.user.findUnique({
      where: { id: this.SYSTEM_USER_UUID },
      select: { id: true },
    });

    if (!systemUser) {
      throw new Error(
        'System user not found! Run migrations to seed system user.'
      );
    }

    this.systemUserId = systemUser.id;
  }

  getSystemUserId(): string {
    if (!this.systemUserId) {
      throw new Error('System user not initialized. Call onModuleInit first.');
    }
    return this.systemUserId;
  }

  // Helper for creating records with system user attribution
  createAuditFields(userId?: string): {
    added_by: string;
    modified_by: string;
    date_added: Date;
    date_modified: Date;
  } {
    const user = userId || this.getSystemUserId();
    return {
      added_by: user,
      modified_by: user,
      date_added: new Date(),
      date_modified: new Date(),
    };
  }
}

Usage in Sync Jobs

// services/uex-sync/categories.sync.service.ts
import { Injectable } from '@nestjs/common';
import { SystemUserService } from '../system-user.service';

@Injectable()
export class CategoriesSyncService {
  constructor(
    private prisma: PrismaService,
    private systemUser: SystemUserService
  ) {}

  async syncCategories(categories: UEXCategory[]): Promise<void> {
    const systemUserId = this.systemUser.getSystemUserId();

    for (const category of categories) {
      await this.prisma.uexCategory.upsert({
        where: { uex_id: category.id },
        create: {
          uex_id: category.id,
          name: category.name,
          type: category.type,
          added_by: systemUserId,
          modified_by: systemUserId,
          date_added: new Date(),
          date_modified: new Date(),
        },
        update: {
          name: category.name,
          type: category.type,
          modified_by: systemUserId,
          date_modified: new Date(),
        },
      });
    }
  }
}

Query Filtering

Repository Layer Pattern

// repositories/user.repository.ts
export class UserRepository {
  constructor(private prisma: PrismaService) {}

  // Never include system user in normal queries
  async findMany(filter: UserFilter): Promise<User[]> {
    return this.prisma.user.findMany({
      where: {
        ...filter,
        is_system_user: false,  // Always exclude
        deleted: false,
      },
    });
  }

  // Explicit method for admin-only system user access
  async findSystemUser(): Promise<User | null> {
    return this.prisma.user.findUnique({
      where: { is_system_user: true },
    });
  }
}

API Layer Protection

// controllers/users.controller.ts
@Get()
async getUsers(@Query() query: UserQueryDto): Promise<UserListResponse> {
  // UserService.findMany automatically excludes system user
  const users = await this.userService.findMany({
    search: query.search,
    limit: query.limit,
    offset: query.offset,
  });

  return {
    users,
    total: users.length,
  };
}

Environment-Specific Handling

Consistent UUID Across Environments

• Dev, staging, prod all use same UUID: 00000000-0000-0000-0000-000000000001
• Seed migration is idempotent (uses ON CONFLICT DO NOTHING)
• Makes database backups/restores between environments seamless

Backup & Restore Considerations

-- When restoring from backup, system user automatically included
-- No special handling needed

-- When cloning prod → staging:
-- System user ID remains consistent
-- All audit trails reference same UUID

Testing Strategy

Unit Tests

describe('SystemUserService', () => {
  it('should cache system user ID at startup', async () => {
    const service = new SystemUserService(prismaMock);
    await service.onModuleInit();
    expect(service.getSystemUserId()).toBe('00000000-0000-0000-0000-000000000001');
  });

  it('should throw if system user missing', async () => {
    prismaMock.user.findUnique.mockResolvedValue(null);
    const service = new SystemUserService(prismaMock);
    await expect(service.onModuleInit()).rejects.toThrow('System user not found');
  });

  it('should create audit fields with system user', () => {
    const service = new SystemUserService(prismaMock);
    service['systemUserId'] = '00000000-0000-0000-0000-000000000001';
    const fields = service.createAuditFields();
    expect(fields.added_by).toBe('00000000-0000-0000-0000-000000000001');
  });
});

Integration Tests

describe('UEX Sync with System User', () => {
  it('should attribute synced categories to system user', async () => {
    await categoriesSyncService.syncCategories(mockCategories);
    
    const category = await prisma.uexCategory.findFirst({
      where: { uex_id: mockCategories[0].id },
    });
    
    expect(category.added_by).toBe('00000000-0000-0000-0000-000000000001');
    expect(category.modified_by).toBe('00000000-0000-0000-0000-000000000001');
  });

  it('should exclude system user from user list API', async () => {
    const response = await request(app).get('/api/users');
    const userIds = response.body.users.map(u => u.id);
    expect(userIds).not.toContain('00000000-0000-0000-0000-000000000001');
  });
});

Tasks

1. Add is_system_user column to users table
2. Create seed migration for system user with fixed UUID
3. Implement SystemUserService with caching
4. Update all repository methods to exclude system user by default
5. Refactor UEX sync jobs to use system user service
6. Add validation: system user cannot log in (block in auth middleware)
7. Update API endpoints to filter system user
8. Add integration tests for system user attribution
9. Document system user pattern for future background jobs
10. Create admin endpoint to view system user (for debugging)

Acceptance Criteria

• System user exists in all environments with consistent UUID
• All UEX sync jobs use system user for added_by/modified_by
• System user excluded from normal user search/list endpoints
• System user cannot authenticate (blocked at middleware layer)
• SystemUserService cached at app startup (fails fast if missing)
• Audit queries can easily distinguish automated vs manual changes
• Documentation clearly explains when to use system user
• Zero test failures related to system user presence

Dependencies

• None (foundational)

Technical Risks

• Migration ordering: System user must exist before UEX sync runs
  • Mitigation: SystemUserService throws startup error if missing
• Accidental deletion: Developer manually deletes system user
  • Mitigation: Add DB constraint or trigger to prevent deletion
• UUID collision: Extremely unlikely with reserved UUID pattern
  • Mitigation: Use NULL UUID namespace (00000000-...)

Performance Targets

• System user ID lookup: O(1) via in-memory cache
• Query filtering overhead: < 1ms (simple WHERE clause)
• Zero performance impact on normal user queries (separate index)