# DS 1 — Humand Prod | Read replica

Specialized monolith tables. Core tables (Users, Instances, InstanceCrmData, FeatureFlags, CommunityFeatures) are in `redash-connect`.

## Segmentations

5-table model for segmenting users and entities.

### SegmentationGroups

Segmentation groups (e.g.: "Department", "Work Center").

| Column | Description |
|--------|-------------|
| `id` | PK |
| `name` | Group name |
| `instanceId` | FK to Instances |

### SegmentationItems

Items within a group (e.g.: "HR", "Corporate").

| Column | Description |
|--------|-------------|
| `id` | PK |
| `groupId` | FK to SegmentationGroups |
| `name` | Item name |
| `instanceId` | FK to Instances |

### Segmentations

Intermediate table linking entities (users, forms, posts, etc.) to segmentation items.

| Column | Description |
|--------|-------------|
| `id` | PK |
| `groupId` | FK to SegmentationGroups |
| `itemId` | FK to SegmentationItems |
| `segmentableType` | Entity type: `'user'`, `'form'`, etc. |
| `segmentableId` | Entity ID |
| `deletedAt` | NULL = active, with value = was removed |

### UserSegmentationItems

Current segmentation state for a user. One row per user.

| Column | Description |
|--------|-------------|
| `userId` | FK to Users |
| `instanceId` | FK to Instances |
| `segmentations` | ARRAY of strings in format `groupId_itemId` (e.g.: `59851_1334579`) |
| `createdAt` / `updatedAt` | Timestamps |

To resolve to names: join with SegmentationGroups and SegmentationItems by parsing the array.

### SegmentationHistories

Complete history of assignments and removals per user.

| Column | Description |
|--------|-------------|
| `segmentationsId` | FK to **Segmentations.id** (NOT directly to Groups) |
| `itemId` | FK to SegmentationItems |
| `instanceId` | FK to Instances |
| `segmentableType` | `'user'` (lowercase) |
| `segmentableId` | userId |
| `createdAt` | When it was assigned |
| `updatedAt` | Last update |
| `deletedAt` | If set → was removed |

Each bulk operation generates a new record. `deletedAt` != null indicates removal.

### Relationships (gotchas)

- `SegmentationHistories.segmentationsId` → `Segmentations.id` (NOT directly to SegmentationGroups)
- `Segmentations.groupId` → `SegmentationGroups.id`
- `SegmentationHistories.itemId` → `SegmentationItems.id`
- `SegmentationItems.groupId` → `SegmentationGroups.id`
- `SegmentationHistories.segmentableType` is `'user'` (lowercase, not `'User'`)

## Sessions

Active user sessions. Useful for checking recent activity and refresh tokens.

| Column | Description |
|--------|-------------|
| `userId` | FK to Users |
| `instanceId` | FK to Instances |
| `token` | Session token |
| `agent` | Client type: `web`, `mobile`, etc. |
| `createdAt` | Session start |
| `expiresAt` | Expiration |

To count active users in the last 30 days:
```sql
SELECT COUNT(DISTINCT "userId")
FROM "Sessions"
WHERE "instanceId" = :id AND "createdAt" > NOW() - INTERVAL '30 days'
```

## Jobs

Bulk uploads. Allows monitoring performance and import status.

| Column | Description |
|--------|-------------|
| `id` | PK |
| `status` | Job status (e.g.: running, completed, failed) |
| `type` | Upload type |
| `fileName` | Imported file name |
| `creatorId` | FK to Users (who executed it) |
| `instanceId` | FK to Instances |
| `totalRows` | Total rows in file |
| `lastExecutedRow` | Last processed row (to track progress) |
| `rowsIgnored` | ARRAY of rows that were ignored |
| `createdAt` / `updatedAt` | Timestamps |

## Audit

### AuditLogs

Entity change logs. **Does not log everything** — only:
- Users (UPDATE/DELETE)
- TimeTrackingEntries (CRUD)
- VacationPolicies (CREATE/UPDATE)

**Does not log segmentation changes.**

| Column | Description |
|--------|-------------|
| `tableName` | Affected table |
| `tableId` | Record ID |
| `payload` | jsonb with the change |
| `modifiedBy` | Who made the change |
| `eventType` | CREATE / UPDATE / DELETE |
| `eventTime` | When |
| `instanceId` | FK to Instances |

### Audits

Authentication events only: LOGIN, LOGOUT, REFRESH, INTERNAL_LOGIN, EXTERNAL_LOGIN.

| Column | Description |
|--------|-------------|
| `userId` | FK to Users |
| `instanceId` | FK to Instances |
| `action` | Event type |
| `platform` | web / mobile |
| `agent` | User agent |

## Legacy permissions model (Capabilities)

Pre-Cerberus. Only applies if `cerberus_enabled = false` for the instance.

### Capabilities

Permissions that apply to 100% of a community's users (apps + global permissions). In Cerberus these are equivalent to the "All Users" role.

**Key distinction**: some capabilities are **apps** (e.g.: `view_forms`, `view_posts` → enable a module) and others are **permissions** (e.g.: `can_edit_users`, `view_metrics` → control access). `view_metrics` is an exception: starts with `view` but is a permission.

### UserCapabilities

Permissions assigned to specific users (not community-wide). This is the table that defines "who is an admin" in the old model.

## Modules in monolith vs microservices

| Module | Where the data lives | Controlling feature flag |
|--------|---------------------|------------------------|
| Users, org structure | DS 1 (always) | — |
| Segmentations | DS 1 (always) | — |
| Forms | DS 1 | — |
| Posts / Feed | DS 1 | — |
| Time tracking | DS 1 | — |
| Permissions (legacy) | DS 1 (Capabilities) | `cerberus_enabled = false` |
| Permissions (new) | DS 25 (Cerberus) | `cerberus_enabled = true` |
| Onboarding / Tasks | Employeelifecycle | `employee_lifecycle` |
| Service Management | Panda | — (associated with `isAgent`) |
