Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/ricardomb-tech/auth-service/llms.txt

Use this file to discover all available pages before exploring further.

Auth Service treats its PostgreSQL schema as a first-class versioned artifact. Flyway owns every DDL change: tables, indexes, extensions, and constraints are all defined in versioned SQL migration files. Hibernate is configured with spring.jpa.hibernate.ddl-auto=validate — it reads the existing schema at startup and verifies that the database structure matches the JPA entity mappings, but it never creates, alters, or drops anything. This separation of concerns (AD-7) means the schema is always traceable through source control and the exact same SQL runs in every environment, from a developer’s laptop to production.

​
Migration History

The migration files live in src/main/resources/db/migration/ and are applied in version order by Flyway on every startup.

​
V1__init.sql — Extensions

Enables the pgcrypto PostgreSQL extension, which provides the gen_random_uuid() function used by JPA entities to generate UUID primary keys server-side. 
-- Habilita gen_random_uuid(), necesaria para la generación de UUID de las
-- entidades que introduce la Story 1.2 en adelante (AD-14).
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

​
V2__accounts.sql — Core Account Schema

Introduces the three tables that form the foundation of the identity model: accounts, account_roles, and verification_tokens. 
CREATE TABLE accounts (
    id uuid PRIMARY KEY,
    email text NOT NULL UNIQUE,
    password_hash text,
    status text NOT NULL,
    failed_attempts int NOT NULL DEFAULT 0,
    locked_until timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE account_roles (
    account_id uuid NOT NULL REFERENCES accounts (id),
    role text NOT NULL,
    PRIMARY KEY (account_id, role)
);

CREATE TABLE verification_tokens (
    id uuid PRIMARY KEY,
    account_id uuid NOT NULL REFERENCES accounts (id),
    token_hash text NOT NULL UNIQUE,
    purpose text NOT NULL,
    expires_at timestamptz NOT NULL,
    consumed_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

​
V3__refresh_tokens.sql — Refresh Token Family Tracking

Adds the refresh_tokens table, which tracks the full lifecycle of every issued refresh token. The family_id column groups tokens that share a rotation lineage — if a token from a family is presented after it has already been used, the entire family is revoked to prevent replay attacks. Two indexes accelerate the most common lookups (by family and by account). 
CREATE TABLE refresh_tokens (
    id uuid PRIMARY KEY,
    account_id uuid NOT NULL REFERENCES accounts (id),
    token_hash text NOT NULL UNIQUE,
    family_id uuid NOT NULL,
    expires_at timestamptz NOT NULL,
    used_at timestamptz,
    revoked_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_refresh_tokens_family_id ON refresh_tokens (family_id);
CREATE INDEX idx_refresh_tokens_account_id ON refresh_tokens (account_id);

​
V4__federated_identities.sql — OAuth2 Provider Links

Adds the federated_identities table, which links an Auth Service account to one or more external OAuth2 provider identities (currently Google and GitHub). The (provider, provider_user_id) unique constraint prevents the same external identity from being attached to multiple accounts. 
CREATE TABLE federated_identities (
    id uuid PRIMARY KEY,
    account_id uuid NOT NULL REFERENCES accounts (id),
    provider text NOT NULL,
    provider_user_id text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (provider, provider_user_id)
);

CREATE INDEX idx_federated_identities_account_id ON federated_identities (account_id);

​
Key Schema Design Choices

UUID primary keys via pgcrypto All tables use uuid as the primary key type. UUIDs are generated in application code (JPA entities) using gen_random_uuid() from the pgcrypto extension enabled in V1. This avoids sequential integer IDs that leak record counts and simplifies distributed ID generation. Hashed tokens, never plaintext The token_hash column in both verification_tokens and refresh_tokens stores a secure hash of the actual token value, never the raw token. This means a database breach does not expose usable tokens. The same principle applies to password_hash in accounts. status as a text enum The accounts.status column uses text NOT NULL rather than a PostgreSQL ENUM type. This keeps DDL migrations simpler — adding a new status value (e.g. LOCKED) is a no-op at the database level and only requires updating the application domain model. All timestamps as timestamptz Every timestamp column uses timestamptz (timestamp with time zone) rather than timestamp. PostgreSQL stores timestamptz values in UTC internally and converts to the session time zone on read, making the schema unambiguous across deployments in different time zones. Nullable lifecycle columns Columns like used_at, revoked_at, consumed_at, and locked_until are nullable by design — NULL means the event has not yet occurred. A non-null value records precisely when it did.

​
Adding a New Migration

1

Create a new versioned SQL file

Place the file in src/main/resources/db/migration/ following the Flyway naming convention:
V<version>__<short_description>.sql
The version must be strictly greater than the highest existing version. Descriptions use underscores as word separators. For example, to add a sessions table after V4:
src/main/resources/db/migration/V5__sessions.sql
2

Write your SQL

Write plain, idiomatic PostgreSQL DDL. Reference existing tables with foreign keys where appropriate:
CREATE TABLE sessions (
    id uuid PRIMARY KEY,
    account_id uuid NOT NULL REFERENCES accounts (id),
    created_at timestamptz NOT NULL DEFAULT now()
);
3

Update the corresponding JPA entity

After Flyway applies the migration and creates the table, update or create the matching JPA entity so that Hibernate’s schema validation (ddl-auto=validate) passes. If the entity and the table structure do not match, the application will refuse to start.
4

Verify locally

Start the service locally (see Local Setup). Flyway logs each applied migration:
Flyway Community Edition ... by Redgate
Database: jdbc:postgresql://localhost:5432/auth_service (PostgreSQL 15.x)
Successfully applied 1 migration to schema "public" (execution time 00:00.042s)
If there is a checksum mismatch or a version conflict, Flyway will throw FlywayException at startup with a descriptive message.
Never edit an existing migration file. Once a Flyway migration has been applied to any environment — including your local database — its checksum is recorded in the flyway_schema_history table. Modifying the file will cause Flyway to detect a checksum mismatch and refuse to start the application with a FlywayException. If you need to correct a mistake in a previous migration, always create a new versioned file that applies the corrective change.
Auth Service sets spring.jpa.hibernate.ddl-auto=validate in application.properties. On every startup, Hibernate compares the live database schema against the JPA entity mappings. If a table, column, or constraint that an entity expects is absent — for example, because a migration was skipped or rolled back — the application throws a SchemaManagementException and exits. This acts as an automatic consistency check between your migration files and your entity code.

Build docs developers (and LLMs) love

Get started for freeTalk to us