# DS 13 — Insights

Two main data sources: backend security events and Amplitude data.

## api_events

Security events emitted by the Humand backend.

| Column | Description |
|--------|-------------|
| `uuid` | PK |
| `instance_id` | FK to Instances (note: snake_case, not camelCase) |
| `user_id` | The user **affected** by the action |
| `actor_id` | Who **performed** the action (may be another user or the same one) |
| `event_type` | Event type (see table below) |
| `timestamp` | When it occurred |
| `department_id` | User's department |
| `agent` | User agent |
| `ip` | Actor's IP |
| `platform` | web / mobile |
| `internal` | boolean — whether it was an internal action |
| `source` | Action origin |
| `metadata` | jsonb — additional data depending on event_type |

### Event types

| event_type | What it logs | metadata |
|------------|-------------|----------|
| `USER_CREATION` | User creation | `{}` |
| `USER_ACTIVATION` | Account activation | `{}` |
| `USER_DEACTIVATION` | Deactivation | `{}` |
| `USER_REACTIVATION` | Reactivation | `{}` |
| `USER_DELETION` | User deletion | `{}` |
| `USER_LOGIN` | Successful login | `{}` |
| `USER_LOGOUT` | Logout | `{}` |
| `USER_LOGIN_WRONG_PASSWORD` | Failed login | `{}` |
| `USER_PASSWORD_CHANGED` | Password change | `{}` |
| `USER_EMAIL_UPDATE` | Email change | `{}` |
| `USER_EMPLOYEE_INTERNAL_ID_UPDATE` | Employee ID change | `{}` |
| `MISSING_PERMISSIONS_LOGOUT` | Logout due to missing permissions | `{}` |
| `DEPARTMENT_MEMBER_CREATION` | Added to department | `{}` |
| `DEPARTMENT_MEMBER_DELETION` | Removed from department | `{}` |
| `SEGMENTATION_GROUP_CREATION` | Group creation | `{id, name}` |
| `SEGMENTATION_GROUP_UPDATE` | Group rename | depends |
| `SEGMENTATION_GROUP_DELETION` | Group deletion | depends |
| `SEGMENTATION_ITEM_CREATION` | Item creation | `{id, name, groupId, groupName}` |
| `SEGMENTATION_ITEM_UPDATE` | Item rename | `{id, oldName, newName}` |
| `SEGMENTATION_ITEM_DELETION` | Item deletion | depends |
| `MASSIVE_USER_SEGMENTATION_DELETION` | Bulk user removal | `{itemId, itemName, groupId, groupName, userIds: [...]}` |

**IMPORTANT**: `SEGMENTATION_*` events log changes to the **structure** (CRUD of groups and items), NOT individual user assignment/unassignment to items. The only one that logs user-level changes is `MASSIVE_USER_SEGMENTATION_DELETION`, and it only covers bulk removals. **There is no event for segmentation assignment to users**.

## Amplitude (product data)

### Table structure

Amplitude syncs data every ~1 hour via cron. Tables are **split by month**:

- `amplitude_events_YYYY_MM` — raw events (e.g.: `amplitude_events_2026_05`)
- `daily_active_users_YYYY_MM` — pre-calculated DAU per month
- `knowledge_library_events_YYYY_MM` — knowledge library events

There are also "base" tables without month suffix (`amplitude_events`, `daily_active_users`) which are the root table.

### daily_active_users (pre-calculated)

Most useful table for activity dashboards. One row per user per day with activity.

| Column | Description |
|--------|-------------|
| `user_id` | User ID in Humand |
| `instance_id` | FK to Instances |
| `date` | Activity date |
| `platforms` | ARRAY of platforms used that day (web, mobile, etc.) |
| `modules` | ARRAY of modules used |
| `events` | ARRAY of event types generated |

Use the corresponding month's table for better performance: `daily_active_users_2026_05` instead of the base table.

### amplitude_events (raw events)

Key columns for queries:

| Column | Description |
|--------|-------------|
| `event_type` | Amplitude event type |
| `event_time` | Event timestamp |
| `user_id` | User ID (string) |
| `instance_id` | FK to Instances |
| `platform` | web / ios / android |
| `event_properties` | text — JSON with event properties |
| `user_properties` | text — JSON with user properties |
| `device_type` | Device type |
| `country` / `city` | Geolocation |

Use the corresponding month's table: `amplitude_events_2026_05`.

### Other useful tables

| Table | Contents |
|-------|----------|
| `ai_chatbot_events` | AI chatbot events |
| `ai_chatbot_conversation_summary` | Conversation summaries |
| `ai_chatbot_feedback_summary` | User feedback on chatbot |
| `ai_chatbot_metrics_summary` | Aggregated chatbot metrics |
| `ai_chatbot_kb_document_usage` | KB document usage in chatbot |
| `knowledge_library_events_YYYY_MM` | Knowledge library events |
| `feed_post_events` | Feed post events |
| `group_post_events` | Group post events |
| `otp_events` | OTP (one-time password) events |
