---
title: "Database and Migrations"
description: "The relational database architecture relies on SQLAlchemy Object-Relational Mapping (ORM) and Alembic migrations to manage persistence, connection pooling, and schema evolution for user authenticat..."
last_updated: "2026-10-11T17:37:10.257685+00:00"
canonical_url: "https://www.doc0.dev/docs/faa36707-7c28-4f69-a18f-700ff61c704e/technical/server-dashboard/database-and-migrations"
---

<details>
<summary>Relevant source files</summary>

The following files were used as context for generating this wiki page:

- [server/models.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/models.py)
- [integrations/mem0-agent-plugin/core/memory_core.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/integrations/mem0-agent-plugin/core/memory_core.py)
- [server/alembic/versions/001_create_users_and_api_keys.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/001_create_users_and_api_keys.py)
- [integrations/agent-plugin-core/python/memory_core.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/integrations/agent-plugin-core/python/memory_core.py)
- [integrations/claude-code-plugin/core/memory_core.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/integrations/claude-code-plugin/core/memory_core.py)
- [integrations/antigravity-plugin/core/memory_core.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/integrations/antigravity-plugin/core/memory_core.py)
- [integrations/kimi-plugin/core/memory_core.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/integrations/kimi-plugin/core/memory_core.py)
- [server/alembic/env.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/env.py)
- [integrations/codex-plugin/core/memory_core.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/integrations/codex-plugin/core/memory_core.py)
- [server/routers/auth.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/routers/auth.py)
- [server/main.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/main.py)
- [server/alembic/versions/002_create_request_logs.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/002_create_request_logs.py)
- [integrations/cursor-plugin/core/memory_core.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/integrations/cursor-plugin/core/memory_core.py)
- [server/routers/api_keys.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/routers/api_keys.py)
- [server/db.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/db.py)
- [server/alembic/versions/003_create_settings.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/003_create_settings.py)
- [server/auth.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/auth.py)
- [server/alembic.ini](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic.ini)
- [mem0/memory/storage.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/mem0/memory/storage.py)
- [server/alembic/versions/005_refresh_token_jtis.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/005_refresh_token_jtis.py)
- [server/routers/requests.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/routers/requests.py)
- [server/server_state.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/server_state.py)
- [server/alembic/versions/006_request_logs_brin.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/006_request_logs_brin.py)
- [server/scripts/prune_request_logs.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/scripts/prune_request_logs.py)
- [mem0/configs/vector_stores/oracledb.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/mem0/configs/vector_stores/oracledb.py)
- [mem0/configs/base.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/mem0/configs/base.py)
- [server/init-db.sh](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/init-db.sh)
- [server/alembic/versions/004_unique_admin_role.py](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/004_unique_admin_role.py)
</details>

## Overview

The relational database architecture relies on SQLAlchemy Object-Relational Mapping (ORM) and Alembic migrations to manage persistence, connection pooling, and schema evolution for user authentication, API keys, audit logs, and system settings. Using declarative base classes and dynamic connection string resolution, the engine connects to PostgreSQL instances while maintaining session lifecycles through FastAPI dependency injection.

Sources: [server/db.py:1-31](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/db.py#L1-L31)

Alembic coordinates schema versioning through structured migration scripts that track incremental database modifications, supporting both offline SQL generation and online transactional execution against target metadata.

Sources: [server/alembic/env.py:1-44](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/env.py#L1-L44), [server/alembic/versions/001_create_users_and_api_keys.py:1-49](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/001_create_users_and_api_keys.py#L1-L49)

## Database Engine Configuration and Session Lifecycle

### Dynamic URL Resolution and Engine Initialization

The database engine constructs its connection string dynamically by reading environment variables with default fallback values. The `_build_database_url()` function inspects `POSTGRES_HOST`, `POSTGRES_PORT`, `POSTGRES_USER`, `POSTGRES_PASSWORD`, and `APP_DB_NAME`, falling back respectively to `postgres`, `5432`, `postgres`, `postgres`, and `mem0_app`. It formats these components into a PostgreSQL connection URI using the `postgresql+psycopg` driver scheme.

```python
def _build_database_url() -> str:
    host = os.environ.get("POSTGRES_HOST", "postgres")
    port = os.environ.get("POSTGRES_PORT", "5432")
    user = os.environ.get("POSTGRES_USER", "postgres")
    password = os.environ.get("POSTGRES_PASSWORD", "postgres")
    db = os.environ.get("APP_DB_NAME", "mem0_app")
    return f"postgresql+psycopg://{user}:{password}@{host}:{port}/{db}"
```
Sources: [server/db.py:7-13](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/db.py#L7-L13)

The global `engine` instance is created by passing the generated database URL to `create_engine()` with `pool_pre_ping=True` enabled to verify connection health before checkout.

```python
engine = create_engine(_build_database_url(), pool_pre_ping=True)
```
Sources: [server/db.py:16-16](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/db.py#L16-L16)

> [!NOTE]
> The database initialization script (`init-db.sh`) independently provisions the `mem0_app` application database using `psql` conditional execution (`WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mem0_app')`), while the default `postgres` database is reserved for pgvector memory storage.
> Sources: [server/init-db.sh:4-9](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/init-db.sh#L4-L9)

### Session Factory and Dependency Injection

Session management relies on `SessionLocal`, configured as a sessionmaker bound to the global engine with `autoflush=False` and `expire_on_commit=False`.

```python
SessionLocal = sessionmaker(bind=engine, autoflush=False, expire_on_commit=False)
```
Sources: [server/db.py:18-18](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/db.py#L18-L18)

Models inherit from a declarative base class `Base` constructed using `DeclarativeBase`.

```python
class Base(DeclarativeBase):
    pass
```
Sources: [server/db.py:21-23](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/db.py#L21-L23)

The `get_db()` FastAPI dependency yields a transactional `Session` instance and guarantees closure in a `finally` block regardless of request outcomes.

```python
def get_db():
    """FastAPI dependency that yields a SQLAlchemy session."""
    db: Session = SessionLocal()
    try:
        yield db
    finally:
        db.close()
```
Sources: [server/db.py:25-31](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/db.py#L25-L31)

> [!WARNING]
> Failing to consume `get_db()` within a FastAPI route or background task dependency injection context leaves connections unreturned to the pool, risking connection exhaustion under concurrent request load.
> Sources: [server/db.py:25-31](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/db.py#L25-L31)

## Identity and Authentication Models

### User Accounts and Role Constraints

The `User` model defines the primary authentication entity managed within the application database (`server/models.py`). Each user record utilizes a UUID primary key, stores a string representation of their name and email, and retains a hashed password string alongside role permissions.

```python
class User(Base):
    __tablename__ = "users"

    id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=_new_uuid)
    name: Mapped[str] = mapped_column(String(255))
    email: Mapped[str] = mapped_column(String(255), unique=True, index=True)
    password_hash: Mapped[str] = mapped_column(Text)
    role: Mapped[str] = mapped_column(String(20), default="admin")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_utcnow)
    last_login_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
```
Sources: [server/models.py:18-28](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/models.py#L18-L28)

Database schema evolution supports administrative scoping via migration `004_unique_admin_role.py`, which applies a partial unique index on the `role` column where the role equals `'admin'`. This ensures at most one administrator account can exist concurrently.

```python
def upgrade() -> None:
    op.create_index(
        "ix_users_only_one_admin",
        "users",
        ["role"],
        unique=True,
        postgresql_where=sa.text("role = 'admin'"),
    )
```
Sources: [server/alembic/versions/004_unique_admin_role.py:20-27](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/004_unique_admin_role.py#L20-L27)

> [!WARNING]
> Attempting to insert a second user with the `admin` role directly into PostgreSQL violates the `ix_users_only_one_admin` partial unique index constraint, triggering a database integrity error.
> Sources: [server/alembic/versions/004_unique_admin_role.py:20-27](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/004_unique_admin_role.py#L20-L27)

### API Keys and Relational Mappings

The `APIKey` model manages programmatic access credentials linked to specific user accounts. Each key entry stores a short prefix for identification, a secure cryptographic hash of the full key value, an administrative label, and timestamps tracking usage and revocation.

```python
class APIKey(Base):
    __tablename__ = "api_keys"

    id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=_new_uuid)
    key_prefix: Mapped[str] = mapped_column(String(12))
    key_hash: Mapped[str] = mapped_column(Text)
    label: Mapped[str] = mapped_column(String(255))
    created_by: Mapped[uuid.UUID] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))
    last_used_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_utcnow)
```
Sources: [server/models.py:30-41](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/models.py#L30-L41)

The initial schema migration establishes both the `users` and `api_keys` tables concurrently, enforcing foreign key cascades on deletion.

```python
    op.create_table(
        "api_keys",
        sa.Column("id", sa.Uuid(), primary_key=True),
        sa.Column("key_prefix", sa.String(12), nullable=False),
        sa.Column("key_hash", sa.Text(), nullable=False),
        sa.Column("label", sa.String(255), nullable=False),
        sa.Column("created_by", sa.Uuid(), sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False),
        sa.Column("last_used_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("revoked_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
    )
```
Sources: [server/alembic/versions/001_create_users_and_api_keys.py:34-44](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/001_create_users_and_api_keys.py#L34-L44)

### Single-Use Refresh Token JTIs

The `RefreshTokenJti` model implements storage for JWT JWT-ID (`jti`) claims to enforce single-use refresh token semantics. 

```python
class RefreshTokenJti(Base):
    __tablename__ = "refresh_token_jtis"

    jti: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=_new_uuid)
    user_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    used_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_utcnow)
```
Sources: [server/models.py:55-63](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/models.py#L55-L63)

Migration `005_refresh_token_jtis.py` adds this table along with an expiration index to accelerate token cleanups and validation lookups.

```python
def upgrade() -> None:
    op.create_table(
        "refresh_token_jtis",
        sa.Column("jti", sa.Uuid(), primary_key=True),
        sa.Column(
            "user_id",
            sa.Uuid(),
            sa.ForeignKey("users.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("used_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
    )
    op.create_index("ix_refresh_token_jtis_expires_at", "refresh_token_jtis", ["expires_at"])
```
Sources: [server/alembic/versions/005_refresh_token_jtis.py:20-34](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/005_refresh_token_jtis.py#L20-L34)

> [!IMPORTANT]
> When a refresh token is successfully exchanged, its corresponding `used_at` timestamp is populated, preventing any subsequent reuse attempt even if the token remains cryptographically valid before its `expires_at` threshold.
> Sources: [server/models.py:55-63](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/models.py#L55-L63), [server/alembic/versions/005_refresh_token_jtis.py:20-34](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/005_refresh_token_jtis.py#L20-L34)

## Request Logging and Settings Models

### Request Logging Model and Migration

The `RequestLog` SQLAlchemy model captures telemetry for incoming HTTP requests [Server API Endpoints](https://www.doc0.dev/docs/faa36707-7c28-4f69-a18f-700ff61c704e/technical/server-dashboard/server-api-endpoints). Each log record tracks the HTTP method, request path, response status code, latency in milliseconds, authentication mechanism used, and creation timestamp.

```python
class RequestLog(Base):
    __tablename__ = "request_logs"

    id: Mapped[uuid.UUID] = mapped_column(primary_key=True, default=_new_uuid)
    method: Mapped[str] = mapped_column(String(16))
    path: Mapped[str] = mapped_column(String(512))
    status_code: Mapped[int] = mapped_column(Integer)
    latency_ms: Mapped[float] = mapped_column(Float)
    auth_type: Mapped[str] = mapped_column(String(32), default="none")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_utcnow)
```
Sources: [server/models.py:43-53](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/models.py#L43-L53)

Migration `002_create_request_logs.py` provisions the `request_logs` relation and creates a B-tree index on `created_at` to support time-range queries and pruning operations.

```python
def upgrade() -> None:
    op.create_table(
        "request_logs",
        sa.Column("id", sa.Uuid(), primary_key=True),
        sa.Column("method", sa.String(16), nullable=False),
        sa.Column("path", sa.String(512), nullable=False),
        sa.Column("status_code", sa.Integer(), nullable=False),
        sa.Column("latency_ms", sa.Float(), nullable=False),
        sa.Column("auth_type", sa.String(32), nullable=False, server_default="none"),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
    )
    op.create_index("ix_request_logs_created_at", "request_logs", ["created_at"])
```
Sources: [server/alembic/versions/002_create_request_logs.py:20-31](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/002_create_request_logs.py#L20-L31)

> [!NOTE]
> The `auth_type` column defaults to `"none"` at the database schema level via a server default expression, ensuring unauthenticated or public routes are explicitly labeled even when no credential extractor populates the context.
> Sources: [server/models.py:43-53](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/models.py#L43-L53), [server/alembic/versions/002_create_request_logs.py:28-28](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/002_create_request_logs.py#L28-L28)

### Settings Model and Migration

The `Settings` model defines a key-value store used to persist dynamic application configurations [Dashboard Views](https://www.doc0.dev/docs/faa36707-7c28-4f69-a18f-700ff61c704e/technical/server-dashboard/dashboard-views). The model uses `key` as the primary key string and updates the `updated_at` timestamp automatically via SQLAlchemy's `onupdate` hook.

```python
class Settings(Base):
    __tablename__ = "settings"

    key: Mapped[str] = mapped_column(String(255), primary_key=True)
    value: Mapped[str] = mapped_column(Text)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=_utcnow,
        onupdate=_utcnow,
    )
```
Sources: [server/models.py:65-74](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/models.py#L65-L74)

Migration `003_create_settings.py` creates the `settings` table with a corresponding `updated_at` server timestamp default.

```python
def upgrade() -> None:
    op.create_table(
        "settings",
        sa.Column("key", sa.String(255), primary_key=True),
        sa.Column("value", sa.Text(), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
    )
```
Sources: [server/alembic/versions/003_create_settings.py:20-26](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/003_create_settings.py#L20-L26)

## Alembic Migration Environment and Versioning

### Configuration and Metadata Initialization

The Alembic migration environment is configured via `server/alembic.ini` and orchestrated by `server/alembic/env.py`. The `alembic.ini` file sets the default script location to `alembic`, prepends the current working directory to `sys.path`, and defines a default PostgreSQL connection string pointing to `postgresql+psycopg://postgres:postgres@postgres:5432/mem0_app`.
Sources: [server/alembic.ini:1-4](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic.ini#L1-L4)

```ini
[alembic]
script_location = alembic
prepend_sys_path = .
sqlalchemy.url = postgresql+psycopg://postgres:postgres@postgres:5432/mem0_app
```
Sources: [server/alembic.ini:1-4](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic.ini#L1-L4)

The `env.py` script imports `Base` and `_build_database_url` from `db`, along with the `models` module to ensure `Base.metadata` registers all relational table definitions. It configures logging handlers from `alembic.ini` and dynamically overrides `sqlalchemy.url` using `_build_database_url()`.
Sources: [server/alembic/env.py:1-20](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/env.py#L1-L20)

```python
from db import Base, _build_database_url
import models  # noqa: F401

config = context.config

if config.config_file_name is not None:
    fileConfig(config.config_file_name)

target_metadata = Base.metadata
config.set_main_option("sqlalchemy.url", _build_database_url())
```
Sources: [server/alembic/env.py:6-20](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/env.py#L6-L20)

### Offline and Online Runner Execution

Alembic execution branches depending on whether `context.is_offline_mode()` evaluates to true. In offline mode, migrations generate SQL scripts without establishing a live database connection, configuring `context` with `literal_binds=True`.
Sources: [server/alembic/env.py:22-27](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/env.py#L22-L27)

```python
def run_migrations_offline() -> None:
    url = config.get_main_option("sqlalchemy.url")
    context.configure(url=url, target_metadata=target_metadata, literal_binds=True)
    with context.begin_transaction():
        context.run_migrations()
```
Sources: [server/alembic/env.py:22-27](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/env.py#L22-L27)

In online mode, Alembic builds an engine using `engine_from_config` with `poolclass=pool.NullPool`, acquires a live connection, and executes migration operations within a transactional block.
Sources: [server/alembic/env.py:29-39](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/env.py#L29-L39)

```python
def run_migrations_online() -> None:
    connectable = engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )
    with connectable.connect() as connection:
        context.configure(connection=connection, target_metadata=target_metadata)
        with context.begin_transaction():
            context.run_migrations()
```
Sources: [server/alembic/env.py:29-39](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/env.py#L29-L39)

> [!NOTE]
> Database initialization is bootstrapped prior to migration runs using the `server/init-db.sh` script, which executes an idempotent `psql` command to create the `mem0_app` database if it does not already exist.
> Sources: [server/init-db.sh:1-9](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/init-db.sh#L1-L9)

## Schema Evolution and Index Optimization

### Migration History Across Releases

The database schema evolves through six sequential Alembic migration revisions that progressively establish core application tables, audit logs, dynamic settings, authentication components, administrative constraints, and storage optimizations. 
Sources: [server/alembic/versions/001_create_users_and_api_keys.py:1-49](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/001_create_users_and_api_keys.py#L1-L49), [server/alembic/versions/002_create_request_logs.py:1-36](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/002_create_request_logs.py#L1-L36), [server/alembic/versions/003_create_settings.py:1-30](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/003_create_settings.py#L1-L30), [server/alembic/versions/004_unique_admin_role.py:1-31](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/004_unique_admin_role.py#L1-L31), [server/alembic/versions/005_refresh_token_jtis.py:1-39](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/005_refresh_token_jtis.py#L1-L39), [server/alembic/versions/006_request_logs_brin.py:1-26](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/006_request_logs_brin.py#L1-L26)

| Revision ID | Revises | Description | Core Tables / Operations |
| :--- | :--- | :--- | :--- |
| `001` | `None` | Create users and api_keys tables | `users`, `api_keys`, `ix_users_email` |
| `002` | `001` | Create request_logs table | `request_logs`, `ix_request_logs_created_at` |
| `003` | `002` | Create settings table | `settings` |
| `004` | `003` | Enforce at-most-one admin via partial unique index | `ix_users_only_one_admin` (`postgresql_where=sa.text("role = 'admin'")`) |
| `005` | `004` | Create refresh_token_jtis table for single-use refresh tokens | `refresh_token_jtis`, `ix_refresh_token_jtis_expires_at` |
| `006` | `005` | Swap request_logs.created_at btree index for a BRIN index | Drop `ix_request_logs_created_at`, create BRIN index on `request_logs(created_at)` |

Sources: [server/alembic/versions/001_create_users_and_api_keys.py:1-49](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/001_create_users_and_api_keys.py#L1-L49), [server/alembic/versions/002_create_request_logs.py:1-36](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/002_create_request_logs.py#L1-L36), [server/alembic/versions/003_create_settings.py:1-30](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/003_create_settings.py#L1-L30), [server/alembic/versions/004_unique_admin_role.py:1-31](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/004_unique_admin_role.py#L1-L31), [server/alembic/versions/005_refresh_token_jtis.py:1-39](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/005_refresh_token_jtis.py#L1-L39), [server/alembic/versions/006_request_logs_brin.py:1-26](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/006_request_logs_brin.py#L1-L26)

### Role Constraints and Partial Indexing

Revision `004` introduces a restrictive uniqueness invariant on user roles to ensure that at most one administrator account can exist within the system simultaneously. This is implemented via a PostgreSQL partial unique index named `ix_users_only_one_admin` scoped exclusively to rows where `role = 'admin'`.
Sources: [server/alembic/versions/004_unique_admin_role.py:20-27](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/004_unique_admin_role.py#L20-L27)

```python
def upgrade() -> None:
    op.create_index(
        "ix_users_only_one_admin",
        "users",
        ["role"],
        unique=True,
        postgresql_where=sa.text("role = 'admin'"),
    )
```
Sources: [server/alembic/versions/004_unique_admin_role.py:20-27](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/004_unique_admin_role.py#L20-L27)

> [!IMPORTANT]
> The partial unique index `ix_users_only_one_admin` relies on PostgreSQL-specific `postgresql_where` syntax. Attempting to run migration `004` on a non-PostgreSQL backend will fail because standard SQL does not support filtered unique indexes in the same manner.
> Sources: [server/alembic/versions/004_unique_admin_role.py:20-27](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/004_unique_admin_role.py#L20-L27)

### Swapping B-tree for BRIN Indexes on Time-Series Logs

The `request_logs` table accumulates high-volume append-only telemetry data ordered naturally by insertion timestamp. Initially created with a standard B-tree index on `created_at` in revision `002`, migration `006` explicitly drops `ix_request_logs_created_at` and replaces it with a Block Range Index (BRIN) via raw SQL execution.
Sources: [server/alembic/versions/002_create_request_logs.py:31-31](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/002_create_request_logs.py#L31-L31), [server/alembic/versions/006_request_logs_brin.py:19-21](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/006_request_logs_brin.py#L19-L21)

```python
def upgrade() -> None:
    op.drop_index("ix_request_logs_created_at", table_name="request_logs")
    op.execute("CREATE INDEX ix_request_logs_created_at ON request_logs USING BRIN (created_at)")
```
Sources: [server/alembic/versions/006_request_logs_brin.py:19-21](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/006_request_logs_brin.py#L19-L21)

> [!TIP]
> BRIN indexes consume a fraction of the disk space required by B-tree indexes on large append-only time-series tables like `request_logs`, because they store summary metadata per physical page range instead of maintaining individual keys per row.
> Sources: [server/alembic/versions/006_request_logs_brin.py:19-21](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/alembic/versions/006_request_logs_brin.py#L19-L21)

## Log Retention and Maintenance Automation

### Retention Window Policies and Execution

The server maintains audit telemetry through a dedicated pruning utility designed to purge old records from the `request_logs` table based on a configurable age threshold. The retention duration is controlled by the `REQUEST_LOG_RETENTION_DAYS` environment variable, which defaults to 30 days if left unspecified or empty.
Sources: [server/scripts/prune_request_logs.py:1-17](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/scripts/prune_request_logs.py#L1-L17)

```python
def main() -> int:
    raw = os.environ.get("REQUEST_LOG_RETENTION_DAYS", "").strip() or "30"
    try:
        retention_days = int(raw)
    except ValueError:
        sys.stderr.write("REQUEST_LOG_RETENTION_DAYS must be an integer.\n")
        return 1
    if retention_days < 1:
        sys.stderr.write("REQUEST_LOG_RETENTION_DAYS must be >= 1.\n")
        return 1

    cutoff = datetime.now(timezone.utc) - timedelta(days=retention_days)
    with SessionLocal() as session:
        result = session.execute(delete(RequestLog).where(RequestLog.created_at < cutoff))
        session.commit()
        sys.stdout.write(f"Deleted {result.rowcount or 0} request_logs rows older than {cutoff.isoformat()}.\n")
    return 0
```
Sources: [server/scripts/prune_request_logs.py:16-32](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/scripts/prune_request_logs.py#L16-L32)

> [!CAUTION]
> The pruning script strictly validates that `REQUEST_LOG_RETENTION_DAYS` parses to an integer greater than or equal to 1. Providing a non-integer value or a number less than 1 prints an error message to standard error and terminates execution with exit code 1.
> Sources: [server/scripts/prune_request_logs.py:18-25](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/scripts/prune_request_logs.py#L18-L25)

The maintenance script can be executed directly inside the container or integrated into external scheduling facilities such as cron or systemd timers on the host system.
Sources: [server/scripts/prune_request_logs.py:3-3](https://github.com/blade47/mem0/blob/b7ad69afda6b6ed030347c66d48a13e4de9dec08/server/scripts/prune_request_logs.py#L3-L3)

## Related

- [FastAPI Application](https://www.doc0.dev/docs/faa36707-7c28-4f69-a18f-700ff61c704e/technical/server-dashboard/fastapi-application)
- [Server Authentication](https://www.doc0.dev/docs/faa36707-7c28-4f69-a18f-700ff61c704e/technical/server-dashboard/server-authentication)


## Sitemap

See the full [sitemap](https://www.doc0.dev/docs/faa36707-7c28-4f69-a18f-700ff61c704e/llms.txt) for all pages in this wiki.