> For the complete documentation index, see [llms.txt](https://docs.osmosworks.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.osmosworks.com/data-model/entities.md).

# Entities and relationships

This page describes the most important entities, their relationships, constraints, and JSONB structures.

## Entity relationship overview

```mermaid
erDiagram
    Tenant ||--|| TenantProfile : has
    Tenant ||--|| TenantSettings : has
    Tenant ||--o{ Principal : contains
    Tenant ||--o{ TenantMember : has
    Tenant ||--o{ TenantCustomer : has
    Tenant ||--o{ ServiceDefinition : offers
    Tenant ||--o{ ServiceRequest : receives
    Tenant ||--o{ Application : manages
    Tenant ||--o{ Invoice : issues
    Tenant ||--|| TenantLandingPage : has
    Tenant ||--|| TenantPayoutAccount : has
    Tenant ||--|| TenantSubscription : subscribes

    Principal ||--|| TenantMember : profiles
    Principal ||--o| TenantCustomer : profiles

    TenantCustomer ||--o{ Party : has
    Party ||--o{ ServiceRequest : subject
    Party ||--o{ Application : subject

    ServiceDefinition ||--o{ ServiceStage : has
    ServiceDefinition ||--o{ ServiceQuestion : has
    ServiceDefinition ||--o{ ServiceRequirement : has
    ServiceDefinition ||--o{ ServiceCharge : has
    ServiceDefinition ||--o{ ServiceValueSet : has

    ServiceStage ||--o{ ServiceQuestion : contains
    ServiceStage ||--o{ ServiceRequirement : contains
    ServiceStage ||--o{ ServiceCharge : contains

    ServiceRequest ||--o{ ServiceRequestAnswer : has
    ServiceRequest ||--o{ ServiceRequestDocument : has
    ServiceRequest ||--|| Application : becomes

    Application ||--o{ ApplicationStage : has
    Application ||--o{ ApplicationQuestion : has
    Application ||--o{ ApplicationRequirement : has
    Application ||--o{ ApplicationAnswer : has
    Application ||--o{ ApplicationDocument : has
    Application ||--o{ Invoice : may_have

    Invoice ||--o{ InvoiceItem : contains
    Invoice ||--o{ Payment : has

    Role ||--o{ TenantMember : assigned
```

## Core identity entities

### Tenant

Represents the organization workspace.

| Column         | Type         | Notes                         |
| -------------- | ------------ | ----------------------------- |
| `id`           | UUID         | Primary key.                  |
| `slug`         | VARCHAR(100) | Unique URL identifier.        |
| `name`         | VARCHAR(200) | Display name.                 |
| `activated_at` | TIMESTAMP    | Null until workspace created. |
| `suspended_at` | TIMESTAMP    | Non-null when suspended.      |
| `removed_at`   | TIMESTAMP    | Non-null when removed.        |

### Principal

Login identity for all actor types.

| Column                                         | Type         | Notes                                                        |
| ---------------------------------------------- | ------------ | ------------------------------------------------------------ |
| `id`                                           | UUID         | Primary key.                                                 |
| `tenant_id`                                    | UUID         | Null for platform users.                                     |
| `tenant_slug`                                  | VARCHAR(100) | Denormalized for lookup.                                     |
| `principal_type`                               | VARCHAR(20)  | `TENANT_ADMIN`, `TENANT_STAFF`, `CUSTOMER`, `PLATFORM_USER`. |
| `email`                                        | VARCHAR(320) | Normalized to lowercase.                                     |
| `password_hash`                                | VARCHAR(255) | BCrypt hash.                                                 |
| `activated_at` / `suspended_at` / `removed_at` | TIMESTAMP    | Lifecycle timestamps.                                        |

Partial unique indexes enforce:

* One live admin email globally.
* One live admin per tenant.
* One live email per tenant.
* One live platform email globally.

### TenantMember

Profile for tenant admins and staff.

| Column                                                | Type         | Notes                    |
| ----------------------------------------------------- | ------------ | ------------------------ |
| `tenant_id`                                           | UUID         | FK to tenant.            |
| `principal_id`                                        | UUID         | FK to principal, unique. |
| `role_id`                                             | UUID         | FK to role.              |
| `name`                                                | VARCHAR(200) | Display name.            |
| `invited_at` / `invitation_expires_at` / `revoked_at` | TIMESTAMP    | Invitation lifecycle.    |

### TenantCustomer

Customer record under a tenant.

| Column                     | Type         | Notes                                                     |
| -------------------------- | ------------ | --------------------------------------------------------- |
| `tenant_id`                | UUID         | FK to tenant.                                             |
| `principal_id`             | UUID         | Optional FK to principal; null for org-managed customers. |
| `first_name` / `last_name` | VARCHAR(100) | Display name.                                             |
| `email`                    | VARCHAR(320) | Unique per tenant.                                        |

### Party

Subject of a service request or application.

| Column                      | Type         | Notes                              |
| --------------------------- | ------------ | ---------------------------------- |
| `tenant_id` / `customer_id` | UUID         | FKs.                               |
| `name`                      | VARCHAR(200) | Required.                          |
| `email` / `phone`           | VARCHAR      | Optional.                          |
| `details`                   | JSONB        | Flexible map of custom fields.     |
| `is_self`                   | BOOLEAN      | True for the customer's own party. |

## Service catalog entities

### ServiceDefinition

Blueprint for a service.

| Column                         | Type         | Notes                                  |
| ------------------------------ | ------------ | -------------------------------------- |
| `tenant_id`                    | UUID         | FK.                                    |
| `code`                         | VARCHAR(64)  | Unique per tenant (excluding deleted). |
| `name`                         | VARCHAR(200) | Unique per tenant.                     |
| `currency`                     | VARCHAR(3)   | Billing currency.                      |
| `party_role`                   | VARCHAR(100) | Label for parties.                     |
| `min_parties` / `max_parties`  | INT          | Optional limits.                       |
| `published_at` / `archived_at` | TIMESTAMP    | Lifecycle timestamps.                  |
| `version`                      | BIGINT       | Optimistic lock.                       |

### ServiceStage

A step within a service.

| Column                  | Type        | Notes                     |
| ----------------------- | ----------- | ------------------------- |
| `service_id`            | UUID        | FK.                       |
| `title` / `description` | TEXT        | Optional.                 |
| `kind`                  | VARCHAR(16) | `ORDINARY` or `PAYMENT`.  |
| `show_rule`             | JSONB       | Optional visibility rule. |
| `order_index`           | INT         | Display order.            |

### ServiceQuestion

A question in a service.

| Column                    | Type         | Notes                                   |
| ------------------------- | ------------ | --------------------------------------- |
| `service_id` / `stage_id` | UUID         | FKs.                                    |
| `code`                    | VARCHAR(64)  | Unique within service.                  |
| `label`                   | VARCHAR(300) | Display text.                           |
| `answer_type`             | VARCHAR(24)  | `TEXT`, `NUMBER`, `SINGLE_CHOICE`, etc. |
| `applies_to`              | VARCHAR(16)  | `REQUEST` or `PARTY`.                   |
| `required`                | BOOLEAN      | Mandatory when visible.                 |
| `options`                 | JSONB        | List of `{label, value, active}`.       |
| `validation`              | JSONB        | Optional validation.                    |
| `show_rule`               | JSONB        | Optional visibility rule.               |

### ServiceRequirement

A required document.

| Column                    | Type        | Notes                      |
| ------------------------- | ----------- | -------------------------- |
| `service_id` / `stage_id` | UUID        | FKs.                       |
| `code`                    | VARCHAR(64) | Unique within service.     |
| `name` / `instructions`   | TEXT        | Display text.              |
| `applies_to`              | VARCHAR(16) | `REQUEST` or `PARTY`.      |
| `required`                | BOOLEAN     | Mandatory when visible.    |
| `review_required`         | BOOLEAN     | Staff review required.     |
| `need_rule`               | JSONB       | Optional requirement rule. |

### ServiceCharge

A billable line item.

| Column                    | Type          | Notes                                       |
| ------------------------- | ------------- | ------------------------------------------- |
| `service_id` / `stage_id` | UUID          | FKs.                                        |
| `name`                    | VARCHAR(200)  | Line item label.                            |
| `basis`                   | VARCHAR(16)   | `PER_REQUEST`, `PER_PARTY`, `PER_QUANTITY`. |
| `amount`                  | NUMERIC(19,2) | Unit price.                                 |
| `question_id`             | UUID          | For `PER_QUANTITY`.                         |
| `charge_rule`             | JSONB         | Optional applicability rule.                |

### ServiceValueSet

Named list of values for rules.

| Column       | Type         | Notes                  |
| ------------ | ------------ | ---------------------- |
| `service_id` | UUID         | FK.                    |
| `code`       | VARCHAR(64)  | Unique within service. |
| `name`       | VARCHAR(200) | Display name.          |
| `members`    | JSONB        | List of string values. |

## Request and application entities

### ServiceRequest

Customer draft submission.

| Column                                                  | Type        | Notes                                       |
| ------------------------------------------------------- | ----------- | ------------------------------------------- |
| `tenant_id` / `service_id` / `customer_id` / `party_id` | UUID        | FKs.                                        |
| `reference`                                             | VARCHAR(64) | Unique per tenant.                          |
| `status`                                                | VARCHAR(16) | `DRAFT`, `PENDING`, `ACCEPTED`, `REJECTED`. |
| `submitted_at` / `decided_at`                           | TIMESTAMP   | Lifecycle.                                  |

### Application

Working record after request acceptance.

| Column                                                                  | Type        | Notes                                 |
| ----------------------------------------------------------------------- | ----------- | ------------------------------------- |
| `tenant_id` / `service_id` / `customer_id` / `party_id` / `assignee_id` | UUID        | FKs.                                  |
| `request_id`                                                            | UUID        | One-to-one with request.              |
| `reference`                                                             | VARCHAR(64) | Unique per tenant.                    |
| `status`                                                                | VARCHAR(16) | `IN_PROGRESS`, `COMPLETED`, `CLOSED`. |
| `service_revision`                                                      | BIGINT      | Service version at acceptance.        |
| `service_snapshot`                                                      | JSONB       | Snapshot of service metadata.         |

### ApplicationStage

Snapshot of a service stage within an application.

| Column            | Type        | Notes                             |
| ----------------- | ----------- | --------------------------------- |
| `application_id`  | UUID        | FK.                               |
| `source_stage_id` | UUID        | Original service stage id.        |
| `kind`            | VARCHAR(16) | `ORDINARY` or `PAYMENT`.          |
| `state`           | VARCHAR(16) | `LOCKED`, `CURRENT`, `COMPLETED`. |
| `show_rule`       | JSONB       | Copied from service stage.        |

### ApplicationQuestion / ApplicationRequirement

Copies of service questions and requirements, with snapshot fields so the application remains stable even if the service changes.

### ApplicationAnswer / ApplicationDocument

Runtime answers and uploaded files.

| Entity                | Key fields                                                                |
| --------------------- | ------------------------------------------------------------------------- |
| `ApplicationAnswer`   | `application_id`, `question_id`, `value` JSONB, `answered_by` UUID.       |
| `ApplicationDocument` | `application_id`, `requirement_id`, `file_object_id`, `uploaded_by` UUID. |

## Finance entities

### Invoice

Amount owed by a customer.

| Column                                                 | Type          | Notes                   |
| ------------------------------------------------------ | ------------- | ----------------------- |
| `tenant_id` / `service_id`                             | UUID          | FKs.                    |
| `party_id` / `application_id` / `application_stage_id` | UUID          | Optional links.         |
| `reference`                                            | VARCHAR(64)   | Unique per tenant.      |
| `currency`                                             | VARCHAR(3)    | Currency code.          |
| `amount_due` / `amount_paid`                           | NUMERIC(19,2) | Balances.               |
| `status`                                               | VARCHAR(16)   | `OPEN`, `PAID`, `VOID`. |
| `due_at` / `paid_at`                                   | TIMESTAMP     | Lifecycle.              |

### Payment

Checkout attempt against an invoice.

| Column                              | Type              | Notes                                           |
| ----------------------------------- | ----------------- | ----------------------------------------------- |
| `tenant_id` / `invoice_id`          | UUID              | FKs.                                            |
| `reference`                         | VARCHAR(64)       | Unique per tenant.                              |
| `provider`                          | VARCHAR(24)       | `PAYSTACK`.                                     |
| `provider_reference`                | VARCHAR(128)      | Provider transaction id.                        |
| `amount` / `currency`               | NUMERIC / VARCHAR | Paid amount.                                    |
| `status`                            | VARCHAR(16)       | `PENDING`, `SUCCESSFUL`, `FAILED`, `ABANDONED`. |
| `authorization_url` / `access_code` | TEXT / VARCHAR    | Paystack checkout data.                         |

### BillingPlan / TenantSubscription

Platform subscription billing.

| Entity               | Key fields                                                                                  |
| -------------------- | ------------------------------------------------------------------------------------------- |
| `BillingPlan`        | `code`, `name`, `price`, `currency`, `billing_interval`, `paystack_plan_code`, limits.      |
| `TenantSubscription` | `tenant_id` (unique), `plan_id`, `status`, Paystack codes, period start/end, `grace_until`. |

## JSONB structures

### Rule

```json
{
  "answer": "request.express_processing",
  "operator": "EQUALS",
  "value": true
}
```

Group example:

```json
{
  "all": [
    { "answer": "request.nationality", "operator": "IN_SET", "set": "ECOWAS" },
    { "answer": "request.travel_date", "operator": "AFTER", "value": "2026-01-01" }
  ]
}
```

### Service snapshot

```json
{
  "id": "c3d4e5f6-...",
  "code": "OSM-SD-000001",
  "name": "Family Visa Assistance",
  "description": "...",
  "hint": "...",
  "currency": "NGN",
  "partyRole": "Traveller",
  "minParties": 1,
  "maxParties": 6
}
```

### Question options

```json
[
  { "label": "Nigeria", "value": "Nigeria", "active": true },
  { "label": "Ghana", "value": "Ghana", "active": true }
]
```

### Party details

```json
{
  "date_of_birth": "1990-01-15",
  "nationality": "Nigeria",
  "passport_number": "A12345678"
}
```

## Deletion behavior

* Most tenant-scoped entities use status flags (`ACTIVE`, `INACTIVE`, `DELETED`) rather than hard deletes.
* Tenant, principal, member, and customer records use lifecycle timestamps for activation, suspension, and removal.
* Foreign keys with `deleteCascade` remove child records when a parent is deleted (for example, tenant deletion cascades to profiles and settings).
* Value sets referenced by rules cannot be deleted.
* Questions referenced by rules cannot be deleted.
* Stages containing questions, charges, or requirements cannot be deleted.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.osmosworks.com/data-model/entities.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.