Database Architecture
AllSafe Fast is designed to integrate into your existing data architecture rather than imposing a separate database or schema. The authentication framework uses four dedicated tables with a clear separation of concerns, allowing you to run auth tables alongside your application's own tables in the same PostgreSQL database.
Design Philosophy
Many authentication frameworks force you to adopt their database schema, often requiring a separate database or a monolithic user table that tries to do everything. AllSafe Fast takes a different approach:
- Integration, not isolation — Auth tables live in your existing PostgreSQL database alongside your application tables.
- Separation of concerns — Authentication data (users, accounts, sessions, verifications) is kept in dedicated tables, separate from your application's domain data.
- Protocol-based storage — The storage layer is defined by protocols, so you can implement your own adapters or use your existing models.
- Async-first — All database operations use async SQLAlchemy with asyncpg, matching modern FastAPI patterns.
Your application models (orders, products, articles, etc.) coexist with AllSafe Fast's auth models in the same database. The auth tables are prefixed with allsafe_ to avoid naming conflicts. You reference users by their UUID, which serves as a foreign key from your application tables to allsafe_users.id.
Data Model Diagram
id (UUID PK), email, email_verified, name, image, is_active, roles, permissions, created_at, updated_at
id (UUID PK), user_id (FK), provider, provider_account_id, password_hash, access_token, refresh_token, scope
id (UUID PK), user_id (FK), refresh_token_hash, expires_at, created_at
id (UUID PK), user_id (FK), token_hash, type, expires_at, created_at
The Four Tables
allsafe_users
The central user table. Stores the user's identity, profile information, and authorization data (roles and permissions).
["admin", "user"]). Embedded in JWT claims.["users:read"]). Embedded in JWT claims.allsafe_accounts
Links users to authentication providers. A user can have multiple accounts (e.g., a password account and a Google OAuth account), enabling account linking.
allsafe_users.id. CASCADE on delete.password, google, github).provider, this is unique.allsafe_sessions
Tracks active refresh token sessions. Enables session revocation, session listing, and theft detection.
allsafe_users.id. CASCADE on delete.allsafe_verifications
Stores verification tokens for email verification, password reset, and magic link flows.
allsafe_users.id. CASCADE on delete.email_verify, password_reset, magic_link). Indexed.Relationships and Foreign Keys
All three child tables (allsafe_accounts, allsafe_sessions, allsafe_verifications) have a foreign key to allsafe_users.id with ON DELETE CASCADE. This means:
- When a user is deleted, all their accounts, sessions, and verification tokens are automatically deleted.
- You never need to manually clean up child records when removing a user.
- The cascade is enforced at the database level, ensuring consistency even if the application crashes mid-operation.
# SQLAlchemy relationship definition (simplified)
class AllSafeUser(Base):
__tablename__ = "allsafe_users"
id = Column(UUID, primary_key=True, default=uuid4)
email = Column(String, unique=True, index=True, nullable=False)
roles = Column(ARRAY(String), default=list)
permissions = Column(ARRAY(String), default=list)
# ... other fields
accounts = relationship("AllSafeAccount", back_populates="user", cascade="all, delete-orphan")
sessions = relationship("AllSafeSession", back_populates="user", cascade="all, delete-orphan")
verifications = relationship("AllSafeVerification", back_populates="user", cascade="all, delete-orphan")Indexing Strategy
AllSafe Fast creates indexes on columns that are frequently queried to ensure fast lookups:
| Table | Indexed Column | Index Type | Query Pattern |
|---|---|---|---|
| allsafe_users | Unique index | Sign-in by email, user lookup | |
| allsafe_accounts | provider + provider_account_id | Unique composite index | OAuth account lookup |
| allsafe_sessions | refresh_token_hash | Unique index | Token validation, session lookup |
| allsafe_sessions | expires_at | Regular index | Session cleanup, expiry queries |
| allsafe_verifications | token_hash | Regular index | Token validation |
| allsafe_verifications | type | Regular index | Filter by verification type |
| allsafe_verifications | expires_at | Regular index | Token cleanup, expiry queries |
Refresh tokens and verification tokens are stored as SHA-256 hashes, not plaintext. This means if the database is compromised, the attacker cannot use the stored tokens directly. The hash is computed when the token is issued, and the same hash is computed when the token is presented for validation.
Connection Management
AllSafe Fast uses async SQLAlchemy with asyncpg for PostgreSQL connections. The connection management is handled through three functions:
from allsafe_fast import create_engine, create_session_factory, get_session
# 1. Create the async engine
# Database URL format: postgresql+asyncpg://user:pass@host:port/dbname
engine = create_engine("postgresql+asyncpg://user:pass@localhost:5432/myapp")
# 2. Create a session factory
create_session_factory(engine) # Returns an async session factory
# 3. Use get_session as a FastAPI dependency
@app.get("/api/data")
async def get_data(session = Depends(get_session(factory))):
# session is an async SQLAlchemy session
result = await session.execute(select(MyModel))
return result.scalars().all()Migrations
AllSafe Fast provides CLI commands for database migrations. The migration workflow uses Alembic under the hood:
Always review generated migration scripts before applying them, especially in production. Some schema changes (like dropping columns) can cause data loss. Test migrations on a staging database first.