---
title: Data model
description: The 19 entities and 3 join tables behind TALA, the dual user-or-organization ownership chain, every enum, and the four migrations that build them.
---

Schema is migration-driven. Four migrations exist, and TypeORM's `synchronize` must be turned **off explicitly** on every shared database — it is not off by default outside production. See [the warning below](#migrations).

## The ownership chain

```text
User ─┬─> Collection ──< Asset ──< AssetVersion
      │       └──< collection_assets >── Asset          (many-to-many)
      │
      └─> Organization ──< OrganizationMember ──> User
              ├──< OrganizationInvitation
              ├──< Project ──< Task ──< TaskComment
              │                 └──< task_assignees >── OrganizationMember
              │                                └──< task_comment_assets >── Asset
              ├──< Collection    (org-scoped)
              └──< Asset         (org-scoped, also project-linked)
```

An asset can reach a user three different ways — `owner_user_id`, `created_by_user_id`, and through its collection — and reach an organization two ways, directly or through its project. Each column is individually nullable, and which one is authoritative depends on the `scope` the caller asked for.

## Ownership is exclusive

Nullable per column does **not** mean free-form. Both owner-bearing tables carry a `CHECK` constraint requiring exactly one of the owner pair:

| Table | Constraint | Rule |
| --- | --- | --- |
| `assets` | `CHK_assets_exactly_one_owner` | `num_nonnulls("owner_user_id", "organization_id") = 1` |
| `collections` | `CHK_collections_exactly_one_owner` | `num_nonnulls("user_id", "organization_id") = 1` |

So an asset is personal or organizational, never both and never orphaned. `created_by_user_id`, `collection_id` and `project_id` are unconstrained and orthogonal — an org-owned asset still records who uploaded it.

:::danger[A violation surfaces as a 500, not a 400]
The constraint lives in Postgres, so a service that sets both columns (or neither) fails after validation has already passed. The global exception filter has no branch for check violations — only `23505` unique violations are mapped — so the caller gets a generic 500. Decide the owner before the insert; do not rely on a readable error.
:::

## Tables

| Table | Key | Notable columns | Soft delete |
| --- | --- | --- | --- |
| `users` | char(10) | `role`, `account_status`, `provider`, `provider_id`, `image_url`, `plan_id`; `password` is `select: false` | no |
| `organizations` | char(10) | `name`, unique `slug`, `owner_id` | yes |
| `organization_members` | char(10) | `role`, `status`; unique (org, user); index (org, status) | no |
| `organization_invitations` | char(10) | `email`, `role`, unique `token_hash`, `expires_at`, `accepted_at`, `revoked_at` | no |
| `collections` | char(10) | `title`, `description`, `user_id` + `organization_id` — exactly one non-null | yes |
| `assets` | varchar | `asset_type`, `asset_name`, short + long URL, `current_version`, `collection_id`, `created_by_user_id`, `project_id`; `owner_user_id` + `organization_id` — exactly one non-null | yes |
| `asset_versions` | varchar | `version_number`, `s3_key`, `change_note`; unique (asset, version_number) | no |
| `asset_logs` | char(10) | `message`, `activity_type`; user FK is `onDelete: RESTRICT` | yes |
| `asset_favorites` | char(14) | per-user favourite join | no |
| `collection_favorites` | char(14) | per-user favourite join | no |
| `projects` | char(12) | `status`, `priority`, `start_date`, `due_date`, `position`; index (org, status, position) | yes |
| `tasks` | char(12) | `status`, `priority`, `start_at`, `due_at`, `position`; indexes (project, status, position) and (start_at, due_at) | yes |
| `task_comments` | char(12) | `body`, `author_user_id`, attached assets | yes |
| `activity_logs` | varchar | `action`, `endpoint`, `status_code`, `resource_type`, `resource_id`, `ip_address`, `user_agent`, jsonb `metadata`, `duration_ms` | no |
| `refresh_tokens` | `jti` char(36) | sha256 `token_hash`, `expires_at`, `revoked_at` | no |
| `oauth_exchange_codes` | `code_hash` | sha256 at rest, ~60s expiry, single use | no |
| `email_verification` | `email` | `token` | no |
| `password_reset` | `email` | `token` char(64) | no |
| `plans` | varchar | unique `name`, `minimum_seat`, `maximum_seat` | no |

Join tables carry no entity of their own: `collection_assets`, `task_assignees`, `task_comment_assets`.

All timestamps use TypeORM's `@CreateDateColumn` / `@UpdateDateColumn` / `@DeleteDateColumn`. Soft delete is `deleted_at`.

## Enums

<Expandable title="Every enum and its values" defaultOpen>

| Enum | Values | Defined in |
| --- | --- | --- |
| `Role` | `CONTENT_CREATOR`, `ADMIN` | `auth/auth.types.ts` |
| `AccountStatus` | `VERIFIED`, `PENDING`, `SUSPENDED` | `auth/auth.types.ts` |
| `AuthProvider` | `LOCAL`, `GOOGLE`, `GITHUB` | `auth/auth.types.ts` |
| `OrgRole` | `OWNER`, `ADMIN`, `MEMBER` | `organization/organization.types.ts` |
| `MembershipStatus` | `ACTIVE`, `INVITED`, `SUSPENDED` | `organization/organization.types.ts` |
| `WorkStatus` | `QUEUED`, `IN_PROGRESS`, `COMPLETED` | `project/project.types.ts` |
| `WorkPriority` | `LOW`, `MEDIUM`, `HIGH` | `project/project.types.ts` |
| `ActivityType` | `created`, `updated`, `deleted`, `accessed`, `shared`, `version_added`, `version_restored` | `assets/activity-type.ts` |
| `LogAction` | namespaced strings — `auth.login`, `collection.created`, `asset.uploaded`, … | `activity-logs/log-action.types.ts` |

</Expandable>

`WorkStatus` and `WorkPriority` are shared by projects and tasks through named Postgres types (`work_status_enum`, `work_priority_enum`), so a value added to one applies to both.

`Role` is the platform role and gates `/api/timeline` and `/api/seeder`. `OrgRole` is the tenant role and gates membership mutations. They are unrelated — a platform `ADMIN` is not automatically an org `OWNER`.

## Migrations

| Migration | What it does |
| --- | --- |
| `InitialSchema` | Base tables |
| `SeedPlans` | Inserts the plan rows — no `/api/seeder` call needed |
| `LockDownPublicSchema` | Enables RLS and revokes `anon` / `authenticated` grants and default privileges |
| `CoreMvpCollaboration` | Organizations, invitations, projects, tasks, comments, favourites, dual ownership |

```bash
npm run migration:generate -- src/migrations/MigrationName
npm run migration:run
npm run migration:revert
```

:::warning[`migration:generate` takes a path, not a name]
TypeORM's CLI treats the argument as the output path. `npm run migration:generate "AddThing"` writes `AddThing.ts` to the backend root, where the runtime glob (`src/migrations/[0-9]*.{js,ts}`) will never find it — the migration silently never runs. Always pass `-- src/migrations/<Name>`.
:::

:::warning[Do not widen the migrations glob]
A follow-up commit excludes migration `*.spec.ts` files from the runtime migrations glob. Reintroducing a bare `*.ts` pattern makes the runner try to execute test files as migrations.
:::

:::danger[`DB_SYNCHRONIZE` defaults to **on** outside production]
Only `NODE_ENV=production` forces auto-sync off unconditionally. Everywhere else an unset `DB_SYNCHRONIZE` means `true`:

```ts title="backend/src/lib/services/typeorm-config.service.ts"
const synchronize =
  nodeEnv === 'production' ? false
  : syncFlag === 'true'    ? true
  : syncFlag === 'false'   ? false
  : true;   // unset → ON
```

`.env.example` ships the key empty, so a staging host running `NODE_ENV=staging` against the shared Supabase database auto-syncs its schema on boot — TypeORM will alter and drop columns to match the entities. **Set `DB_SYNCHRONIZE=false` explicitly in every non-production environment that is not a throwaway local database.**

`migrationsRun` is `false` everywhere — migrations are run explicitly, and they are the schema source of truth.
:::