logosophe Cursor Rules

# Logosophe Project Rules and Context

## Project Structure
- **Monorepo**: Uses Yarn workspaces with apps/* and packages/* structure
- **Main App**: `apps/worker` - Next.js app using OpenNext for Cloudflare Workers
- **Email Worker**: `apps/email-worker` - Separate Cloudflare Worker for email processing
- **Packages**: `packages/common`, `packages/config`, `packages/database` for shared code

## Development Setup
- **Package Manager**: Yarn (not npm) - use `yarn` commands
- **Local Development**: `yarn dev` starts the development server
- **Tunnel**: `yarn tunnel:logosophe-dev` starts Cloudflare tunnel for local-dev.logosophe.com
- **Database**: Cloudflare D1 database with local and remote instances

## Database Configuration
- **Database Name**: `logosophe`
- **Database ID**: `fd7b2b89-eedd-4111-ba68-fdb05cdf2995`
- **Binding**: `DB` in wrangler configuration
- **Local Database**: Located at `.wrangler/state/v3/d1`
- **Remote Database**: Cloudflare D1 instance

## Database Commands
- **List databases**: `yarn wrangler d1 list`
- **Execute on local**: `yarn wrangler d1 execute logosophe --command "SQL"`
- **Execute on remote**: `yarn wrangler d1 execute logosophe --remote --command "SQL"`
- **Create database**: `yarn wrangler d1 create logosophe`
- **Delete database**: `yarn wrangler d1 delete logosophe`

## Database Schema Lookup
- **Check table structure**: `yarn wrangler d1 execute logosophe --command "PRAGMA table_info(table_name);"`
- **Session token lookup**: Join sessions and users tables to get email from session token:
  ```sql
  SELECT s.sessionToken FROM sessions s 
  JOIN users u ON s.userId = u.id 
  WHERE u.email = 'user@example.com' 
  ORDER BY s.expires DESC LIMIT 1;
  ```
- **Email from session token**: Reverse lookup to get email from session token:
  ```sql
  SELECT u.email FROM sessions s 
  JOIN users u ON s.userId = u.id 
  WHERE s.sessionToken = 'token_here';
  ```
- **Session expiration check**: Include expires field when querying sessions:
  ```sql
  SELECT s.sessionToken, s.expires FROM sessions s 
  JOIN users u ON s.userId = u.id 
  WHERE u.email = 'user@example.com' 
  ORDER BY s.expires DESC LIMIT 1;
  ```

## Wrangler Configuration
- **Config file**: `apps/worker/wrangler.jsonc`
- **Must run wrangler commands from**: `apps/worker/` directory
- **Database binding**: Configured in `d1_databases` section
- **Environment**: Uses production database by default, no local-dev environment configured

## UI Framework
- **No Tailwind CSS**: Project uses Radix-UI/Themes instead
- **Components**: Located in `packages/common/components/`
- **UI Components**: Radix-UI components in `packages/common/components/ui/`

### Header Component Architecture
- **Main Header**: Located at `apps/worker/app/components/Header/index.tsx` - used on root page (`/`)
- **Harbor AppBar**: Located at `apps/worker/app/[lang]/harbor/appbar.tsx` - used in harbor routes
- **Dashboard AppBar**: Located at `apps/worker/app/dashboard/appbar.tsx` - used in dashboard routes
- **Subscriber Pages AppBar**: Located at `apps/worker/app/components/SubscriberPagesAppBar.tsx` - used in subscriber page routes

### Header Component Role-Based Navigation
- **Credentials users (admin/tenant)**: See "Dashboard" → navigate to `/dashboard`
- **Non-Credentials users who are subscribers**: See "Harbor" (in their language) → navigate to `/[lang]/harbor`
- **Everyone else** (including OAuth users without subscriber role and unauthenticated users): See "Sign In" → navigate to `/signin`
- **Role checking**: Uses `session.user.role` from RBAC system to determine display and navigation
- **Internationalization**: Subscriber users see "Harbor" in their language, others see English fallback text

## Authentication
- **Framework**: NextAuth v5 with D1Adapter
- **Providers**: Google, Apple, Resend, Credentials
- **Configuration**: `apps/worker/app/auth.ts`

### Auth.js v5 Database Access Patterns
- **OpenNext Cloudflare**: Use `getCloudflareContext({async: true})` to access D1 database
- **Database Binding**: Access via `context.env.DB` after getting context
- **Async Pattern**: Always await the context: `const context = await getCloudflareContext({async: true})`
- **Error Handling**: Wrap database access in try/catch blocks
- **Fallback Strategy**: Continue with operation even if database logging fails

### Auth.js v5 Configuration Patterns
- **Async NextAuth**: Configure NextAuth as async function to get database context
- **Session Strategy**: Use `'database'` strategy for session management
- **Custom Adapter**: Create custom adapter with role support for D1
- **Event Logging**: Log authentication events in SystemLogs table
- **Provider Configuration**: Configure each provider with proper callbacks

### Database Schema for Auth.js
- **Users Table**: Standard Auth.js users table with id, name, email, emailVerified, image
- **Accounts Table**: OAuth provider accounts with userId, provider, type, access_token, etc.
- **Sessions Table**: Session management with sessionToken, userId, expires
- **Verification Tokens**: For email verification flows
- **Credentials Table**: Custom table for admin/tenant users with Email, Password, Role
- **Subscribers Table**: Custom table for regular users with email, provider, etc.

### SystemLogs Integration
- **Log Types**: Use 'AUTH' for authentication events
- **Required Fields**: userId, userEmail, provider, activityType, ipAddress, userAgent
- **Metadata**: Store additional context as JSON string
- **Column Names**: Use 'LogType' and 'UserEmail' (not 'Type' and 'Email')
- **Error Handling**: Log errors but don't fail authentication flow

### Sign-In/Sign-Out Patterns
- **Server Actions**: Use inline server actions with 'use server' directive
- **Redirect Handling**: Let Auth.js handle redirects with `redirectTo` parameter
- **Error Handling**: Don't catch `NEXT_REDIRECT` errors from Auth.js
- **Session Checking**: Use `await auth()` to check current session
- **Role-Based Redirects**: Check user role before redirecting to appropriate page

### NEXT_REDIRECT Error Handling
- **Root Cause**: `redirect()` throws `NEXT_REDIRECT` error for Next.js to handle
- **Problem**: Try/catch blocks can intercept this error and prevent proper redirects
- **Solution**: Move `redirect()` calls outside try/catch blocks
- **Pattern**: Store error messages in variables, handle redirects separately
- **Auth.js Redirects**: Let `signIn()` with `redirectTo` handle successful redirects naturally

### Database Query Best Practices
- **Direct Queries**: Use `db.prepare().bind().first()` or `db.prepare().bind().all()`
- **Type Safety**: Always type query results explicitly
- **Error Handling**: Let SQL errors propagate naturally
- **Transaction Avoidance**: Don't use transactions with D1 (not supported)
- **Connection Management**: Get fresh context for each database operation

### Authentication Flow
1. **Sign-In**: `signIn('credentials', { email, password, redirectTo })`
2. **Session Check**: `const session = await auth()`
3. **Role Resolution**: Query Credentials/Subscribers tables for user role
4. **Redirect Logic**: Based on role (admin → dashboard, others → harbor)
5. **Logging**: Log authentication events to SystemLogs
6. **Sign-Out**: Use `handleSignOut()` action for proper logging

### Common Pitfalls
- **Context Access**: Always use `await getCloudflareContext({async: true})`
- **Column Names**: Use correct database column names (LogType, UserEmail)
- **Error Catching**: Don't catch `NEXT_REDIRECT` errors from Auth.js
- **Redirect Timing**: Handle redirects outside try/catch blocks
- **Session Management**: Use database strategy for persistent sessions

## Migration Context
- **Source Project**: anchorwrite (uses Cloudflare Pages + Next.js from Vercel)
- **Target Project**: logosophe (uses OpenNext + Cloudflare Workers)
- **Migration Strategy**: Stage-by-stage migration from anchorwrite to logosophe
- **Current Status**: Database setup complete, ready for schema migration

## Key Files
- **Main config**: `apps/worker/wrangler.jsonc`
- **Auth config**: `apps/worker/app/auth.ts`
- **Database utils**: `packages/common/utils/database.ts`
- **Local dev setup**: `LOCAL_DEVELOPMENT.md`
- **Project structure**: `Structure.md`

## Development Workflow
1. Start tunnel: `yarn tunnel:logosophe-dev`
2. Start dev server: `yarn dev`
3. Access at: https://local-dev.logosophe.com
4. Database operations: Run from `apps/worker/` directory

## Database Migration System
- **Migration Location**: `packages/database/migrations/`
- **Current Migration**: `001-initial-schema.sql` (applied to both local and remote)
- **Migration Script**: `packages/database/migrations/apply-migration.sh`
- **Documentation**: `packages/database/migrations/README.md`

### Migration Commands
- **Apply to local**: `./apply-migration.sh 001-initial-schema.sql`
- **Apply to remote**: `./apply-migration.sh 001-initial-schema.sql --remote`
- **Manual local**: `yarn wrangler d1 execute logosophe --file=../../packages/database/migrations/001-initial-schema.sql`
- **Manual remote**: `yarn wrangler d1 execute logosophe --remote --file=../../packages/database/migrations/001-initial-schema.sql`

### Database Schema Overview
- **40+ Tables** organized by system (Authentication, Media, Messaging, Workflows, etc.)
- **Foreign Key Constraints** for data integrity
- **Performance Indexes** on frequently queried columns
- **Initial Data** including roles, permissions, and access templates
- **Soft Deletes** using IsDeleted flags where appropriate
- **Audit Logging** via SystemLogs table

### Key Database Tables
- **Authentication**: Credentials, Subscribers, accounts, sessions, users, verification_tokens
- **Tenant Management**: Tenants, TenantUsers, UserRoles
- **Media Management**: MediaFiles, MediaAccess, MediaAccessTemplates, MediaShareLinks
- **Messaging**: Messages, MessageRecipients, MessageAttachments, MessageThreads, MessageRateLimits, UserBlocks
- **Workflows**: Workflows, WorkflowHistory, WorkflowMessages, WorkflowParticipants
- **System**: SystemLogs, SystemSettings, UserActivity, UserAvatars, TestSessions
- **Resources**: Resources, ResourceAccess, TenantResources, PublishedContent, Preferences

### Migration Guidelines
1. **Use IF NOT EXISTS** for CREATE TABLE statements
2. **Use INSERT OR IGNORE** for INSERT statements
3. **Include Indexes** for performance
4. **Document Changes** in README.md
5. **Test Locally First** before applying to remote 

## Email Worker Deployment
- After making changes to the email worker:
  1. Navigate to the email worker directory: `cd apps/email-worker`
  2. Run `yarn deploy` which:
     - Builds the worker (`yarn build`)
     - Deploys to Cloudflare (`wrangler deploy`)
  3. This ensures the compiled JavaScript matches the TypeScript source
  4. Required for changes to take effect in production

## Logging & Analytics System
- **Current Status**: Production-ready, enterprise-grade analytics platform
- **Implementation**: Complete NormalizedLogging system with dual analytics dashboards
- **Features**: Real-time analytics, trend analysis, multi-language support
- **Documentation**: See `LOGGING_AND_ANALYTICS_README.md` for complete details



## User Authentication & Roles
- There are two types of users:
  - Authenticated users who have signed in using an Auth JS v5 provider
  - Non-Authenticated users who have not signed in using an Auth JS v5 provider
- There are three types of authenticated users:
  - Credentials provider users
  - Non-Credentials provider users who have not opted in as subscribers
  - Non-Credentials provider users who have opted in as subscribers
 - Credentials provider users are administrative and are stored in the Credentials table:
  - admin role: Full system access
  - tenant role: Full access to their assigned tenants
- Non-Credentials provider users who have not opted in as subscribers have limited access and are stored in the TenantUsers and UserRoles tables:
  - TenantId: default
  - RoleId: user
- Non-Credentials provider users who have opted in as subscribers are also stored in the Subscribers table
- Subscribers have enhanced access and can be assigned roles and tenants:
  - Roles are in the Roles table
  - Tenants are in the Tenants table
  - Can have multiple different roles per tenant (e.g., author and editor in tenant-001, author in tenant-002)
  - Tenant membership is stored in the TenantUsers table
  - Role assignments are stored in the UserRoles table

## User Profile Data
- **Only subscribers and admin users have profiles** - regular users (non-subscribers) do not have profile data
- **Subscriber profiles** are stored in the Subscribers table with fields: Name, Email, Active, etc.
- **Admin user profiles** are stored in the Credentials table with fields: Email, Role, etc.
- **Profile API endpoints** should check the appropriate table based on user type:
  - Subscribers: Check Subscribers table first, fall back to users table
  - Admin users: Check Credentials table first, fall back to users table
  - Regular users: Only check users table (limited profile data)

## Role-Based Permissions
- Static permissions per role:
  - author: write, view, upload, download, delete, link, share
  - agent: view, download, link
- Access types: 'view', 'download', 'edit', 'delete', 'upload', 'share', 'unshare', 'link', 'unlink'

## Access Control Patterns
- Use shared access control functions from @/lib/access:
  - isSystemAdmin: Check if user has global admin privileges (Credentials user with admin role)
  - isTenantAdminFor: Check if user has tenant admin privileges for a specific tenant
  - withTenantPermission: Check tenant-specific permissions
- Admin access patterns:
  - Global admins (isSystemAdmin) have full access to all resources
  - Credentials users with 'tenant' role:
    - Have system admin-level control within their assigned tenants
    - Can perform all operations (create, read, update, delete) for their tenants
    - Cannot access resources from other tenants
    - Cannot perform system-wide operations
  - Regular users require explicit tenant membership and role assignments
- Access control implementation:
  - Check authentication first (session?.user?.email)
  - Then check admin status using isSystemAdmin
  - For Credentials tenant users:
    - Check Credentials table for 'tenant' role
    - Use isTenantAdminFor to verify tenant membership and role
    - Grant full access within tenant scope
  - Finally check tenant-specific permissions if needed
- Role resolution:
  - Auth flow directly checks Credentials and Subscribers tables
  - Credentials table takes precedence for admin/tenant roles
  - Subscribers table checked for subscriber role
  - Default to 'user' role if no other roles found
- Avoid duplicating admin checks:
  - Use isSystemAdmin instead of direct Credentials table queries
  - Use isTenantAdminFor for tenant-specific admin checks
  - Keep admin check logic consistent across all routes
  - Maintain single source of truth for admin status

## Database Schema Verification
- Always verify actual database schema using wrangler:
- Don't rely solely on migration files:
  - Migration files may be out of sync with actual schema
  - Direct database queries show current state
  - Helps identify discrepancies between environments
- Common tables to verify:
  - MediaFiles
  - MediaShareLinks
  - UserRoles
  - TenantUsers
  - MediaAccess
  - Credentials
  - Subscribers
  - users
- When debugging issues:
  - Check schema first
  - Compare local vs production
  - Verify foreign key constraints
  - Check indexes and unique constraints

## Share Link Requirements
- User must have:
  - A role with 'link' permission
  - Membership in at least one tenant that overlaps with the media file's tenants
- Share links are viewable by any anonymous Internet user
- No tenant context needed in the share URL
- Access control happens at creation time
- Share links are tenant-agnostic:
  - Once created, they can be accessed by anyone with the valid token
  - No tenant membership or authentication required to use the link
  - Access is controlled solely by the share token and its properties (expiration, max uses)
  - TenantId in MediaShareLinks is only used for creation permissions, not access control

## Logging Requirements

### 🎯 **IMPORTANT: Use NormalizedLogging for All User Actions**

**The Logosophe system has been completely migrated to use `NormalizedLogging` for all user action logging. DO NOT use `SystemLogs` directly for user actions.**

#### **Logging Architecture**
- **User Action Logging**: Use `NormalizedLogging` class for all user interactions
- **Database Management**: `SystemLogs` is retained ONLY for essential database operations
- **Import Pattern**: `import { NormalizedLogging, extractRequestContext } from '@/lib/normalized-logging'`

#### **NormalizedLogging Usage Pattern**
```typescript
// 1. Import the required classes
import { NormalizedLogging, extractRequestContext } from '@/lib/normalized-logging';

// 2. Initialize in your route handler
const normalizedLogging = new NormalizedLogging(db);

// 3. Extract request context for IP and User-Agent
const { ipAddress, userAgent } = extractRequestContext(request);

// 4. Use appropriate logging method based on operation type
await normalizedLogging.logMediaOperations({
  userEmail: access.email,
  tenantId: tenantId || 'system',
  activityType: 'upload_file',
  accessType: 'write',
  targetId: mediaId.toString(),
  targetName: fileName,
  ipAddress,
  userAgent,
  metadata: { fileSize, contentType, language: 'en' }
});
```

#### **Available Logging Methods**
| Method | Purpose | LogType | Use Case |
|--------|---------|---------|----------|
| `logMediaOperations()` | Media file operations | `media_operations` | Upload, download, view, delete, share |
| `logWorkflowOperations()` | Workflow management | `workflow_operations` | Create, update, delete, invite |
| `logMessagingOperations()` | Messaging system | `messaging_operations` | Send, archive, read, delete |
| `logUserManagement()` | User operations | `user_management` | Role assignment, profile updates |
| `logAuthentication()` | Auth events | `authentication` | Sign in, sign out, password changes |
| `logTestOperations()` | Test sessions | `test_operations` | Test creation, validation |
| `logSystemOperations()` | System operations | `system_operations` | Settings, configuration, errors |

#### **Required Parameters for All Logging Methods**
```typescript
interface NormalizedLogData {
  userEmail: string;           // User's email address
  tenantId: string;            // Tenant context ('system' for admin operations)
  activityType: string;        // Specific action (e.g., 'upload_file', 'assign_role')
  accessType: NormalizedAccessType; // Operation type ('read', 'write', 'delete', 'admin', 'auth')
  targetId: string;            // ID of the target resource
  targetName: string;          // Human-readable description
  ipAddress?: string;          // From extractRequestContext(request)
  userAgent?: string;          // From extractRequestContext(request)
  metadata?: Record<string, any>; // Additional structured context
}
```

#### **Access Type Values**
- **`read`**: View operations, access settings, content viewing
- **`write`**: Create, update, modify operations
- **`delete`**: Remove, delete, permanent deletion operations
- **`admin`**: Administrative operations, system settings
- **`auth`**: Authentication-related operations

#### **Metadata Best Practices**
```typescript
// Include relevant context for analytics
metadata: {
  fileSize: file.FileSize,
  contentType: file.ContentType,
  language: 'en',              // User's preferred language
  userRole: 'author',          // User's role in this context
  tenantName: 'acme-corp',     // Tenant information
  action: 'upload_file',       // Operation description
  success: true,               // Operation result
  errorMessage: error?.message // If operation failed
}
```

#### **Request Context Extraction**
```typescript
// Always use this for consistent IP and User-Agent extraction
const { ipAddress, userAgent } = extractRequestContext(request);

// This handles multiple IP headers automatically:
// - x-forwarded-for (Cloudflare)
// - cf-connecting-ip (Cloudflare)
// - x-real-ip (Reverse proxy)
```

#### **Tenant ID Guidelines**
- **`'system'`**: For admin operations that affect the entire system
- **`tenantId`**: For operations within a specific tenant
- **`'default'`**: For operations in the default tenant (use sparingly)

#### **Activity Type Naming Convention**
- Use lowercase with underscores: `upload_file`, `assign_role`, `create_workflow`
- Be descriptive: `update_protection_settings` not just `update_settings`
- Include context: `tenant_add_user` not just `add_user`

#### **Error Handling**
```typescript
try {
  await normalizedLogging.logMediaOperations({...});
} catch (error) {
  // Logging failure should not break the main operation
  console.error('Failed to log operation:', error);
  // Continue with the main operation
}
```

#### **When to Use SystemLogs (Database Management Only)**
```typescript
// ONLY for these database operations:
import { SystemLogs } from '@/lib/system-logs';

const systemLogs = new SystemLogs(db);

// Database retrieval
await systemLogs.getArchivedLogs({...});
await systemLogs.getLogStats();
await systemLogs.queryLogs({...});

// Database cleanup
await systemLogs.archiveExpiredLogs(retentionDays);
await systemLogs.hardDeleteArchivedLogs(hardDeleteDelay);
```

#### **Migration Status**
- ✅ **100% Complete**: All user action logging migrated to `NormalizedLogging`
- ✅ **Build Success**: All TypeScript compilation errors resolved
- ✅ **Analytics Ready**: Rich, consistent data structure for advanced analytics
- ✅ **Type Safety**: Full TypeScript support with proper interfaces

#### **Examples by Operation Type**

##### **Media Operations**
```typescript
// File upload
await normalizedLogging.logMediaOperations({
  userEmail: access.email,
  tenantId: tenantId,
  activityType: 'upload_file',
  accessType: 'write',
  targetId: mediaId.toString(),
  targetName: fileName,
  ipAddress,
  userAgent,
  metadata: { fileSize, contentType, language: 'en' }
});

// File download
await normalizedLogging.logMediaOperations({
  userEmail: access.email,
  tenantId: tenantId,
  activityType: 'download_file',
  accessType: 'read',
  targetId: mediaId.toString(),
  targetName: fileName,
  ipAddress,
  userAgent,
  metadata: { downloadMethod: 'direct_link' }
});
```

##### **User Management**
```typescript
// Role assignment
await normalizedLogging.logUserManagement({
  userEmail: adminEmail,
  tenantId: 'system',
  activityType: 'assign_role',
  accessType: 'admin',
  targetId: userEmail,
  targetName: `User ${userEmail}`,
  ipAddress,
  userAgent,
  metadata: { role: roleName, tenant: tenantId }
});
```

##### **Workflow Operations**
```typescript
// Workflow creation
await normalizedLogging.logWorkflowOperations({
  userEmail: access.email,
  tenantId: tenantId,
  activityType: 'create_workflow',
  accessType: 'write',
  targetId: workflowId,
  targetName: workflowName,
  ipAddress,
  userAgent,
  metadata: { participants: participantCount, mediaFiles: mediaFileCount }
});
```

#### **Common Mistakes to Avoid**
❌ **Don't use SystemLogs for user actions**
```typescript
// WRONG - Don't do this
const systemLogs = new SystemLogs(db);
await systemLogs.createLog({...});
```

✅ **Do use NormalizedLogging for user actions**
```typescript
// CORRECT - Do this
const normalizedLogging = new NormalizedLogging(db);
await normalizedLogging.logMediaOperations({...});
```

❌ **Don't manually extract IP/User-Agent**
```typescript
// WRONG - Don't do this
ipAddress: request.headers.get('x-forwarded-for') || request.headers.get('x-real-ip'),
userAgent: request.headers.get('user-agent')
```

✅ **Do use extractRequestContext**
```typescript
// CORRECT - Do this
const { ipAddress, userAgent } = extractRequestContext(request);
```

#### **Testing Your Logging**
1. **Check Build**: Ensure `yarn build` succeeds
2. **Verify Logs**: Check that logs appear in the SystemLogs table
3. **Test Analytics**: Verify logs appear in analytics dashboards
4. **Check Types**: Ensure TypeScript compilation succeeds

#### **Need Help?**
- Check existing examples in the codebase
- Refer to `apps/worker/app/lib/normalized-logging.ts` for method signatures
- Use the logging utilities in `apps/worker/app/lib/logging-utils.ts`
- Ensure all required parameters are provided

## Database Tables
- MediaShareLinks:
  - Id, MediaId, ShareToken, CreatedBy, TenantId, CreatedAt, ExpiresAt, MaxAccesses, AccessCount
- MediaAccess:
  - Id, MediaId, TenantId, RoleId, AccessType, GrantedAt, GrantedBy, ExpiresAt
- TenantUsers:
  - TenantId, Email, RoleId, CreatedAt, UpdatedAt
- SystemLogs:
  - Id, LogType, Timestamp, UserId, UserEmail, Provider, TenantId, ActivityType, AccessType, TargetId, TargetName, IpAddress, UserAgent, Metadata, IsDeleted

## SQL Query Preferences
- Prefer direct SQL queries over intermediate variables
- Avoid storing query results in variables unless necessary for reuse
- Let SQL errors propagate naturally rather than checking success flags
- Keep SQL queries inline for better readability and debugging
- Use template literals for multi-line SQL queries
- Maintain consistent SQL formatting with proper indentation
- Use descriptive table aliases (e.g., 't' for Tenants, 'tu' for TenantUsers)
- Include all relevant fields in SELECT statements rather than using *

## API Structure

### Media API Structure
- All media-related endpoints should be under /api/media
- Share link endpoints:
  - /api/media/[id]/link - Create share links
  - /api/media/share/[token] - Access shared media
  - /api/media/share/[token]/download - Download shared media
- Cache control headers for share links:
  - Cache-Control: no-store, no-cache, must-revalidate, proxy-revalidate
  - Pragma: no-cache
  - Expires: 0

### Workflow API Structure
The workflow system uses a three-tier API structure to separate concerns:

#### Core Workflow APIs (`/api/workflow/`)
**Purpose**: General workflow functionality for all authenticated users
**Access**: All authenticated users with appropriate roles
**Used by**: Harbor components, general workflow interfaces
**Endpoints**:
- `/api/workflow` (POST/GET) - Create workflows and list workflows for tenant
- `/api/workflow/[id]` (GET/PUT/DELETE) - Get, update, delete individual workflows
- `/api/workflow/[id]/stream` - SSE endpoint for real-time updates
- `/api/workflow/history` - Get workflow history for user
- `/api/workflow/history/detail/[id]` - Get detailed workflow history

#### Harbor-Specific APIs (`/api/harbor/workflow/`)
**Purpose**: Harbor interface-specific functionality
**Access**: Harbor users with appropriate roles
**Used by**: Harbor workflow components only
**Endpoints**:
- `/api/harbor/workflow/messages` (POST) - Send messages to workflows
- `/api/harbor/workflow/stats` (GET) - Get workflow statistics for tenant

#### Dashboard-Specific APIs (`/api/dashboard/workflow/`)
**Purpose**: Admin-focused functionality for system management
**Access**: System admins and tenant admins only
**Used by**: Dashboard admin components only
**Endpoints**:
- `/api/dashboard/workflow/list` (GET) - List workflows with admin filtering
- `/api/dashboard/workflow/[id]` (GET/PUT) - Admin workflow details and management
- `/api/dashboard/workflow/stats` (GET) - System-wide workflow statistics
- `/api/dashboard/workflow/analytics` (GET) - Workflow analytics
- `/api/dashboard/workflow/reports` (GET) - Workflow reports
- `/api/dashboard/workflow/history` (GET) - Admin workflow history
- `/api/dashboard/workflow/bulk` (POST) - Bulk workflow operations
- `/api/dashboard/workflow/health` (GET) - System health checks
- `/api/dashboard/workflow/settings` (GET/PUT) - System settings

### Workflow API Usage Guidelines
- **DO NOT create new workflow routes** - Use existing routes based on the interface context
- **For Harbor components**: Use `/api/workflow/` (core) and `/api/harbor/workflow/` (harbor-specific)
- **For Dashboard components**: Use `/api/dashboard/workflow/` (admin-specific)
- **For general workflow functionality**: Use `/api/workflow/` (core APIs)
- **When adding features**: Determine which interface needs the feature and use the appropriate API tier
- **Avoid duplication**: Check existing APIs before creating new routes

access_control:
  user_authentication:
    - All users must be authenticated with a valid email
    - Users except system admins must belong to at least one tenant
    - New users are initially assigned to the default tenant as part of the subscriber opt-in flow
    - Each tenant has its own set of roles and permissions
    - Roles are tenant-specific and cannot be shared across tenants

 
  access_control_patterns:
    - Media files are always associated with a tenant
    - Access is granted at the tenant level
    - Users can only access media files from tenants they belong to
    - Share links provide temporary or indefinite access to specific media files
    - Share links can be restricted by expiration date and access count

  database_schema:
    - MediaFiles table must have TenantId column
    - MediaAccess table must have MediaId, TenantId, and RoleId columns
    - MediaShareLinks table must have MediaId, ShareToken, and TenantId columns

  share_link_requirements:
    - Must have a unique ShareToken
    - Can be restricted by expiration date
    - Can be restricted by maximum number of accesses
    - Must be associated with a tenant
    - Must track access count
    - Must log all access attempts in SystemLogs

# User Onboarding and Role Management
tenant_users:
  table: TenantUsers
  primary_key: [TenantId, Email]
  roles:
    credentials_users:
      - admin: Global admin role

## TypeScript Database Query Patterns
- Always type database query results explicitly:
  ```typescript
  const message = await db.prepare(`
    SELECT SenderEmail, CreatedAt, IsRecalled FROM Messages WHERE Id = ?
  `).bind(messageId).first() as { SenderEmail: string; CreatedAt: string; IsRecalled: boolean } | undefined;
  ```
- For array results, use proper typing:
  ```typescript
  const tenants = await db.prepare(`
    SELECT Id FROM Tenants
  `).all() as { results: { Id: string }[] };
  ```
- Import ResourceType from access module:
  ```typescript
  import { isSystemAdmin, isTenantAdminFor, hasPermission, type ResourceType } from './access';
  ```

## Messaging System Architecture
- Database schema includes:
  - Messages: Core message storage with tenant awareness
  - MessageRecipients: Many-to-many relationship for message delivery
  - MessageAttachments: Media file attachments for messages
  - MessageThreads: Reply threading support
  - UserBlocks: User blocking functionality
  - MessageRateLimits: Rate limiting per user
  - SystemSettings: System-wide messaging controls
- Access control integration:
  - Extends ResourceType to include 'message'
  - Extends Action type to include 'send'
  - Uses existing tenant-aware RBAC system
  - System admins have full messaging access
  - Tenant admins can send broadcast/announcement messages
  - Regular users need explicit 'message:send' permission

### API Route Organization
- **Harbor Messaging** (`/api/harbor/messaging/*`): Subscriber-facing messaging functionality
  - Real-time messaging with SSE streaming
  - File attachments and link sharing
  - User message composition and management
- **Dashboard Messaging** (`/api/dashboard/messaging/*`): Admin-facing messaging management
  - System-wide message management
  - User blocking and recipient management
  - System controls and statistics
- **Clear Separation**: No route conflicts between harbor and dashboard APIs


## Messaging Features
- Real-time messaging with FIFO queue for offline users
- Rate limiting (configurable, default 1 message per minute)
- User blocking system with tenant scope
- Message states: read, deleted, forwarded, saved, replied
- Media file attachments with access control
- Message recall within configurable window
- System-wide enable/disable toggle
- Admin controls for message management
- Tenant-aware recipient selection
- Message threading and replies

## Rate Limiting Implementation
- Uses MessageRateLimits table to track per-user limits
- Configurable via SystemSettings table
- Default: 60 seconds between messages
- Tracks message count and reset time
- Returns detailed rate limit information to clients

## User Blocking System
- Tenant-scoped blocking (users can only block within their tenants)
- Admin users can see all blocks in their tenants
- Regular users can only see their own blocks
- Blocks prevent message delivery but don't delete existing messages
- Soft delete pattern (IsActive flag)

## System Controls
- messaging_enabled: Global system toggle
- messaging_rate_limit: Seconds between messages
- messaging_max_recipients: Maximum recipients per message
- messaging_recall_window: Seconds to recall messages
- messaging_message_expiry: Days before message cleanup
- All settings stored in SystemSettings table
- Admin-only access to system controls

## API Endpoint Structure

### Harbor Messaging APIs (Subscribers)
- `/api/harbor/messaging/send`: Send messages (POST)
- `/api/harbor/messaging/stream/[tenantId]`: SSE streaming for real-time updates
- `/api/harbor/messaging/unread-count`: Get unread message count (GET)
- `/api/harbor/messaging/attachments/*`: File attachment management
- `/api/harbor/messaging/links/*`: Link sharing functionality
- `/api/harbor/messaging/messages/[id]`: Individual message operations (GET, PUT, DELETE)

### Dashboard Messaging APIs (Admins)
- `/api/dashboard/messaging/send`: Send messages (POST)
- `/api/dashboard/messaging/messages/[id]`: Message management (GET, PUT, DELETE)
- `/api/dashboard/messaging/blocks/*`: User blocking management
- `/api/dashboard/messaging/recipients`: Recipient listing (GET)
- `/api/dashboard/messaging/system`: System controls (GET, PUT, POST for cleanup)
- `/api/dashboard/messaging/route.ts`: Dashboard statistics (GET)

### API Design Principles
- **Clear Separation**: Harbor and dashboard APIs are completely separate
- **No Route Conflicts**: Each interface has its own namespace
- **Consistent Patterns**: All endpoints require authentication and proper tenant access
- **Error Handling**: Consistent error handling and logging patterns across all APIs

## Logging Integration
- Uses existing SystemLogs table for messaging activities
- Log types: MESSAGING for all messaging-related events
- Logs include: send, read, block, unblock, system changes
- Maintains audit trail for compliance and debugging
- Includes metadata for additional context

## Development Patterns
- Prefer minimal, targeted changes over sweeping modifications
- Fix TypeScript errors incrementally
- Use explicit type assertions for database results
- Maintain consistency with existing access control patterns
- Test API endpoints before building UI components
- Follow existing error handling and logging patterns
      - tenant: Tenant admin role
    non_credentials_users:
      - user: Default role in default tenant

user_roles:
  table: UserRoles
  primary_key: [TenantId, Email, RoleId]
  foreign_keys:
    tenant_users:
      columns: [TenantId, Email]
      references: TenantUsers
      on_delete: CASCADE
    roles:
      column: RoleId
      references: Roles
      on_delete: CASCADE
  usage:
    - Tracks additional roles
    - Used for subscribers who have opted in
    - Non-Credentials users get "subscriber" role after opt-in
    - Enables additional role assignments

onboarding_flow:
  credentials_users:
    - Get admin/tenant role in TenantUsers
  non_credentials_users:
    - Start with "user" role in TenantUsers
    - Get "subscriber" role in UserRoles after opt-in
    - Can receive additional roles in UserRoles

user_addition_process:
  steps:
    1: Add user to TenantUsers with 'user' role
    2: Add 'user' role to UserRoles for that user
    3: Add any additional roles to UserRoles

benefits:
  - Clear separation between base roles and additional capabilities
  - Proper user onboarding and role progression
  - Flexible role assignment after initial onboarding
  - Ensures all users have at least the 'user' role in both tables

# Role Checking Logic
- Role types for non-sysadmin and non-tenant-admin users are dynamically validated against the Roles table
- All roles from the database are included in the UserRole type
- Runtime validation ensures type safety and database consistency
- Access control checks all user roles:
  - Users get access if ANY of their roles are allowed
  - No artificial hierarchy between roles
  - Order of roles doesn't matter
  - Special cases for admin and tenant roles are maintained
  - Subscriber role is handled as a fallback
- Role validation process:
  1. Check for admin role first
  2. Check for tenant role in Credentials
  3. Check all roles in UserRoles table
  4. Check Subscriber table as fallback
- Access is granted if:
  - User has admin role
  - User has tenant role in Credentials and has a tenant in common with the resource tenant
  - User has ANY role that matches allowedRoles
- Access is denied only if:
  - User has no roles
  - None of user's roles are in allowedRoles
  - User is not a subscriber (when subscriber role is required)

## Subscriber Management
- Subscriber opt-in process:
  - Check for existing subscriber in Subscribers table
  - Create subscriber record in Subscribers table
  - Add subscriber role in UserRoles table
  - Keep subscriber record creation and role assignment separate
  - Handle opt-in at component level with sequential API calls
- Role assignment:
  - Subscriber role should only be added during opt-in
  - Roles API handles role assignments

## Code Change Guidelines

### Error Handling
- If a change introduces errors, stop and explain
- Do not proceed with additional changes until current errors are resolved
- If reverting changes, ensure all files are returned to original state
- Document any errors encountered during changes

## Activity Logging Patterns
- ActivityType should be descriptive but generic:
  - Use 'add_role' instead of 'add_role_editor'
  - Use 'tenant_add_user' for first-time tenant user addition
- TargetId and TargetName should include specific details:
  - For role additions: 'email_role_name' (e.g., 'user@example.com_editor')
  - For user additions: just the email
- Always include:
  - TenantId for tenant-specific operations
  - IpAddress when available
  - UserAgent when available
- Log both the initial user addition and subsequent role assignments separately
- Use appropriate LogType (ACTIVITY, AUTH, MEDIA_ACCESS, MEDIA_SHARE)
- Store additional context in Metadata field as JSON

## Role Management
- First-time tenant user addition:
  - Must be logged as 'tenant_add_user' with LogType ACTIVITY
  - Requires entry in TenantUsers table
  - Initial role must be specified
- Additional role assignments:
  - Must be logged as 'add_role' with LogType ACTIVITY
  - Requires entry in UserRoles table
  - Must check for existing role before adding
  - Must include both user and role in TargetId
- Role verification:
  - Always verify role exists before assignment
  - Convert role names to lowercase with underscores for consistency
  - Handle unknown roles gracefully with fallback

version: 1
rules:
  - name: Translation Preferences
    description: Preferences for handling translations
    rules:
      - name: Use Familiar Form
        description: Use familiar form (du/tú/tu/jij) instead of formal form (Sie/usted/vous/u) in translations
        pattern: translation.json
        languages: [de, es, fr, nl]
      - name: Translation Structure
        description: Maintain consistent structure across all language files
        pattern: translation.json

  - name: Code Style Preferences
    description: Preferences for code style and formatting
    rules:
      - name: TypeScript Strict
        description: Use strict TypeScript settings
        pattern: tsconfig.json
      - name: React Component Structure
        description: Use functional components with TypeScript
        pattern: "*.tsx"

  - name: UI/UX Preferences
    description: Preferences for user interface and experience
    rules:
      - name: Text Alignment
        description: Prefer wider text containers to avoid unnecessary line breaks
        pattern: "*.tsx"
      - name: Form Validation
        description: Use comprehensive form validation with clear error messages
        pattern: "*.tsx"
      - name: Text and Button Spacing
        description: Ensure proper spacing between text and buttons to prevent elements running together
        pattern: "*.tsx"

# Convert role names to lowercase with underscores for consistency
# Handle unknown roles gracefully with fallback

## Radix UI Components
- Container:
  - Default behavior forces min-height: 100vh
  - Use custom Container component instead
  - Custom Container properties:
    - width: 100%
    - maxWidth: 1200px
    - margin: 0 auto
    - padding: 0 var(--space-4)
  - Implementation: Simple Box component with above properties
  - Rule: Avoid using Radix UI Container directly

## Layout Structure
- Page structure:
  - Header at top
  - Container with content
  - Footer right after content
- Best practices:
  - Avoid forcing full viewport height
  - Let content determine natural height
  - Use Box component for padding and margins
  - Maintain consistent max-width across pages

## UI Component Spacing Guidelines

### Text and Button Spacing
**Problem**: Text and Button elements running together without proper separation, appearing as a single element in the browser.

**Solution**: Always use proper layout structure to separate descriptive text from action buttons.

#### Dashboard Card Pattern (REQUIRED)
```tsx
<Card style={{ flex: '1', minWidth: '300px' }}>
  <Box style={{ padding: '1.5rem' }}>
    <Heading size="4" style={{ marginBottom: '1rem' }}>
      Card Title
    </Heading>
    <Text color="gray" size="2" style={{ marginBottom: '1.5rem', display: 'block' }}>
      Description text that explains what this section does
    </Text>
    <Flex gap="2" wrap="wrap">
      <Button asChild>
        <Link href="/path">Primary Action</Link>
      </Button>
      <Button variant="soft" asChild>
        <Link href="/path">Secondary Action</Link>
      </Button>
    </Flex>
  </Box>
</Card>
```

#### Key Requirements
1. **Text Display**: Always add `display: 'block'` to Text components that precede buttons
2. **Button Containers**: Wrap buttons in `<Flex gap="2" wrap="wrap">` containers
3. **Consistent Spacing**: Use `marginBottom: '1.5rem'` on text elements before buttons
4. **Responsive Design**: Use `wrap="wrap"` for proper responsive behavior

#### Anti-Pattern (AVOID)
```tsx
// ❌ BAD: Text and button run together
<Text color="gray" size="2" style={{ marginBottom: '1.5rem' }}>
  Description text here
</Text>
<Button asChild>
  <Link href="/path">Button Text</Link>
</Button>
```

#### Correct Pattern (USE)
```tsx
// ✅ GOOD: Proper separation and layout
<Text color="gray" size="2" style={{ marginBottom: '1.5rem', display: 'block' }}>
  Description text here
</Text>
<Flex gap="2" wrap="wrap">
  <Button asChild>
    <Link href="/path">Button Text</Link>
  </Button>
</Flex>
```

### When to Apply These Rules
- Dashboard card layouts with descriptive text and action buttons
- Any interface where text content is immediately followed by interactive elements
- Form layouts with explanatory text and submit buttons
- Modal dialogs with description text and action buttons