# Organization Tables & Schemas

Access read-only tables containing your organization's operational data.

{% hint style="info" %}
Guidelines

* Read-only tables with stable, production schemas.
* All timestamps are UTC.
* IDs are strings unless otherwise noted.
* JSON columns may contain nested objects or arrays.
* Availability varies by enabled features.
  {% endhint %}

### Available Tables

## Schema Overview

{% @mermaid/diagram content="%%{init: {"theme": "base", "themeVariables": {"primaryColor": "#D4E2E7", "primaryTextColor": "#100F0F", "primaryBorderColor": "#083241", "lineColor": "#575452", "textColor": "#100F0F"}}}%%
erDiagram
ORGANIZATION ||--o{ USER : contains
ORGANIZATION ||--o{ SERVICE : contains
USER ||--o{ CONVERSATION : starts
CONVERSATION ||--o{ MESSAGE : includes
SERVICE ||--o{ CONVERSATION : powers
CONVERSATION ||--o{ METRIC : evaluates
ROLE ||--o{ USER : assigns
SERVICE ||--o{ TOOL : uses
SIMULATION ||--o{ CONVERSATION : tests" %}

## Common Queries

```sql
-- Active conversations in last 7 days
SELECT COUNT(*) as active_conversations
FROM conversations
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
  AND is_finished = false;

-- Daily message volume
SELECT DATE(created_at) as day,
       COUNT(*) as message_count
FROM messages
WHERE org_id = 'your-org-id'
GROUP BY DATE(created_at)
ORDER BY day DESC
LIMIT 30;

-- User engagement metrics
SELECT u.user_id,
       u.email,
       COUNT(DISTINCT c._id) as conversation_count
FROM users u
LEFT JOIN conversations c ON u.user_id = c.user_id
GROUP BY u.user_id, u.email;
```

{% hint style="success" %}
Browse the subpages below for detailed schemas and query examples for each table category.
{% endhint %}

## Subpages

{% content-ref url="organization-tables/conversation" %}
[conversation](https://docs.amigo.ai/developer-guide/classic-api/data-access/organization-tables/conversation)
{% endcontent-ref %}

{% content-ref url="organization-tables/user" %}
[user](https://docs.amigo.ai/developer-guide/classic-api/data-access/organization-tables/user)
{% endcontent-ref %}

{% content-ref url="organization-tables/service" %}
[service](https://docs.amigo.ai/developer-guide/classic-api/data-access/organization-tables/service)
{% endcontent-ref %}

{% content-ref url="organization-tables/organization" %}
[organization](https://docs.amigo.ai/developer-guide/classic-api/data-access/organization-tables/organization)
{% endcontent-ref %}

{% content-ref url="organization-tables/dynamic-behaviors" %}
[dynamic-behaviors](https://docs.amigo.ai/developer-guide/classic-api/data-access/organization-tables/dynamic-behaviors)
{% endcontent-ref %}

{% content-ref url="organization-tables/metric" %}
[metric](https://docs.amigo.ai/developer-guide/classic-api/data-access/organization-tables/metric)
{% endcontent-ref %}

{% content-ref url="organization-tables/role" %}
[role](https://docs.amigo.ai/developer-guide/classic-api/data-access/organization-tables/role)
{% endcontent-ref %}

{% content-ref url="organization-tables/tool" %}
[tool](https://docs.amigo.ai/developer-guide/classic-api/data-access/organization-tables/tool)
{% endcontent-ref %}

{% content-ref url="organization-tables/simulation" %}
[simulation](https://docs.amigo.ai/developer-guide/classic-api/data-access/organization-tables/simulation)
{% endcontent-ref %}

{% content-ref url="organization-tables/common-query-patterns" %}
[common-query-patterns](https://docs.amigo.ai/developer-guide/classic-api/data-access/organization-tables/common-query-patterns)
{% endcontent-ref %}