Database

Overview

PostgreSQL 16 with SQLAlchemy 2.x ORM and Alembic for migrations.

All primary keys are UUID v4. All tables (except pivot tables) have created_at, updated_at, and deleted_at columns.

Entity Relationship Diagram

users                    user_roles              roles
─────────────────        ──────────────          ─────────────────
id (UUID, PK)    ◄──────  user_id (FK)           id (UUID, PK)
email                     role_id (FK)  ────────► name
password_hash                                     description
first_name                                        created_at
last_name                                         updated_at
is_active                role_permissions         deleted_at
last_login               ────────────────
token_version            role_id (FK)   ────────► roles.id
created_at               permission_id (FK) ────► permissions.id
updated_at
deleted_at               permissions
                         ─────────────────
refresh_tokens           id (UUID, PK)
──────────────           name
id (UUID, PK)            description
user_id (FK) ──────────► users.id
token                    created_at
expires_at               updated_at
revoked                  deleted_at
created_at

Tables

users

Column	Type	Description
`id`	UUID	Primary key
`email`	varchar(255)	Unique, indexed
`password_hash`	varchar(255)	bcrypt hash
`first_name`	varchar(100)	Optional
`last_name`	varchar(100)	Optional
`is_active`	bool	Soft disable without deleting
`last_login`	timestamptz	Updated on every successful login
`token_version`	int	Incremented on logout-all to invalidate all tokens
`created_at`	timestamptz	Auto-set on insert
`updated_at`	timestamptz	Auto-set on update
`deleted_at`	timestamptz	Soft delete — null means active

roles

Column	Type	Description
`id`	UUID	Primary key
`name`	varchar(50)	Unique, e.g. `admin`
`description`	varchar(255)	Optional
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz	Soft delete

permissions

Column	Type	Description
`id`	UUID	Primary key
`name`	varchar(100)	Unique, e.g. `users:read`
`description`	varchar(255)	Optional
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz	Soft delete

refresh_tokens

Column	Type	Description
`id`	UUID	Primary key
`user_id`	UUID	FK → users.id, CASCADE DELETE
`token`	varchar(512)	JWT string, unique, indexed
`expires_at`	timestamptz	Token expiry
`revoked`	bool	Manually revoked flag
`created_at`	timestamptz

user_roles (pivot)

Column	Type	Description
`user_id`	UUID	FK → users.id
`role_id`	UUID	FK → roles.id
`created_at`	timestamptz

role_permissions (pivot)

Column	Type	Description
`role_id`	UUID	FK → roles.id
`permission_id`	UUID	FK → permissions.id
`created_at`	timestamptz

Soft Delete

Records are never physically deleted. Instead, deleted_at is set to the current timestamp. All repository queries filter WHERE deleted_at IS NULL by default.

To permanently remove data, hard_delete() is available in BaseRepository but should be used sparingly.

Mixins

Shared columns are defined as mixins in app/models/base.py and composed into models:

class User(UUIDMixin, TimestampMixin, SoftDeleteMixin, Base):
    ...

Mixin	Columns added
`UUIDMixin`	`id`
`TimestampMixin`	`created_at`, `updated_at`
`SoftDeleteMixin`	`deleted_at`