Database Schema
Translato uses PostgreSQL as its primary data store. This document provides a detailed overview of the database models, relationships, and key architectural decisions.
1. Overview
- Technology: PostgreSQL 16
- ORM: Prisma
- Models: 24
- Indexes: 55
- Enums: 6
2. Enums
| Enum | Values |
|---|---|
UserRole | ADMIN · PROJECT_OWNER · TRANSLATOR |
ProjectVisibility | PRIVATE · PUBLIC |
InviteStatus | PENDING · ACCEPTED · EXPIRED · REVOKED |
OrgRole | OWNER · ADMIN · MEMBER |
TeamRole | ADMIN · MEMBER |
BackupStatus | PENDING · IN_PROGRESS · COMPLETED · FAILED |
BackupFrequency | MANUAL · DAILY · WEEKLY · MONTHLY |
VcsProvider | GITHUB · GITLAB · BITBUCKET |
VcsSyncStatus | SUCCESS · FAILED · IN_PROGRESS · SKIPPED |
3. Entity Relationship Map
User Domain
- User: Core user profile with 2FA and global role.
- →
ApiToken: (1:N) Secure access for CLI/SDKs. - →
ProjectMember: (1:N) Project-level access and language restrictions. - →
OrganizationMember: (1:N) Organization-level access. - →
Activity: (1:N) Audit trail tracking.
- →
Organization Domain
- Organization: Grouping for teams and projects.
- →
Team: (1:N) Sub-groups within an organization. - →
Project: (1:N) Projects owned by the organization.
- →
Project Domain
- Project: The central entity for translation management.
- →
Term: (1:N) Unique translation keys. - →
ProjectLanguage: (1:N) Languages enabled for the project. - →
VcsIntegration: (1:1) Repository sync configuration. - →
Backup: (1:N) Snapshot history.
- →
Translation Domain
- Term: A unique identifier (e.g.,
login.title).- →
Translation: (1:N) Localized values per language.
- →
- Language: ISO 639-1 metadata and plural rules.
- TranslationMemory: Global cross-project suggestion database.
4. Key Symbols & Constraints
*= Unique constraint (automatically indexed).SHA-256= Used for API tokens and Translation Memory source hashing.pg_trgm= Used for fuzzy matching in Translation Memory.
For the full schema definition, see packages/database/prisma/schema.prisma.