SQLAdmin Migration: From SolidJS Admin to Integrated Backend

The custom admin panel we built in Session 020 worked. It had UUID routes, JWT authentication, and role hierarchy. But it also had a problem: it was an entire separate frontend application that needed to be maintained alongside the main SolidJS dashboard and the marketing site. Three frontend applications for one platform was two too many.

In subsequent sessions, we made the decision to rip out the custom SolidJS admin and replace it with SQLAdmin -- a Python library that generates an admin interface directly from SQLAlchemy ORM models. The migration touched 12 ORM model files, created 16 SQLAlchemy models, built 12 admin views, and eliminated thousands of lines of custom frontend code.

Why SQLAdmin?

The custom admin panel required maintaining:

• A separate SolidJS application with its own build pipeline
• Custom API endpoints for every admin operation
• Frontend state management for admin-specific views
• Separate deployment and routing configuration

SQLAdmin 0.16.0+ offered a compelling alternative:

The trade-off was clear. We lost pixel-perfect design control, but we gained zero maintenance overhead for admin CRUD operations. For an internal tool used by one person (Thales), that trade-off was easy to make.

The 16 SQLAlchemy Models

The migration required converting every database table to a proper SQLAlchemy ORM model. Previously, some routes used raw SQL queries. The ORM conversion touched 12 files and produced 16 models:

python# models/user.py
from sqlalchemy import Column, String, Boolean, DateTime, Integer
from sqlalchemy.orm import relationship
from models.base import Base

class User(Base):
    __tablename__ = "users"

    id = Column(String, primary_key=True)
    email = Column(String, unique=True, nullable=True)
    phone = Column(String, unique=True, nullable=True)
    name = Column(String, nullable=True)
    password_hash = Column(String, nullable=True)
    is_active = Column(Boolean, default=True)
    role = Column(String, default="user")
    google_id = Column(String, nullable=True)
    github_id = Column(String, nullable=True)
    microsoft_id = Column(String, nullable=True)
    apple_id = Column(String, nullable=True)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, onupdate=func.now())

    # Relationships
    apps = relationship("App", back_populates="user")
    transactions = relationship("Transaction", back_populates="user")

The full list of 16 models:

The Reserved Column Name Problem: metadata to meta

One of the first issues we encountered during the migration was a reserved column name. Several of our tables had a column called metadata -- a JSON column storing arbitrary provider-specific data. The problem is that metadata is a reserved attribute in SQLAlchemy's declarative base:

python# This breaks SQLAlchemy
class Transaction(Base):
    __tablename__ = "transactions"
    metadata = Column(JSON)  # CONFLICT: Base.metadata is reserved

SQLAlchemy uses metadata internally to store the MetaData object that tracks table definitions. Defining a column with that name shadows the internal attribute and causes cryptic errors.

The fix was straightforward but touched multiple files:

python# Renamed across all models and queries
class Transaction(Base):
    __tablename__ = "transactions"
    meta = Column(JSON, nullable=True)  # Renamed from 'metadata'

This rename propagated through every model that used the column, every API endpoint that read or wrote it, every SDK that referenced the field, and the database migration itself. A small rename, but a large blast radius.

sql-- Migration script
ALTER TABLE transactions RENAME COLUMN metadata TO meta;
ALTER TABLE providers RENAME COLUMN metadata TO meta;
ALTER TABLE apps RENAME COLUMN metadata TO meta;

The 12 Admin Views

SQLAdmin generates CRUD interfaces from view classes. Each view maps to a model and configures which columns are visible, searchable, and editable:

python# admin/views.py
from sqladmin import ModelView

class UserAdmin(ModelView, model=User):
    column_list = [
        User.id, User.email, User.name,
        User.role, User.is_active, User.created_at
    ]
    column_searchable_list = [User.email, User.name]
    column_sortable_list = [User.created_at, User.email]
    column_default_sort = ("created_at", True)  # Newest first

    # Fields visible in detail view
    column_details_list = [
        User.id, User.email, User.phone, User.name,
        User.role, User.is_active,
        User.google_id, User.github_id,
        User.microsoft_id, User.apple_id,
        User.created_at, User.updated_at,
    ]

    # Hide sensitive fields from edit
    form_excluded_columns = [User.password_hash, User.apps, User.transactions]

class TransactionAdmin(ModelView, model=Transaction): column_list = [ Transaction.id, Transaction.amount, Transaction.currency, Transaction.status, Transaction.provider, Transaction.created_at ] column_searchable_list = [Transaction.id, Transaction.provider] column_sortable_list = [Transaction.created_at, Transaction.amount] column_default_sort = ("created_at", True) BLANK # CRITICAL: Transactions are read-only for financial audit can_create = False can_edit = False can_delete = False ```

The TransactionAdmin view deserves special attention. Notice the three flags set to False: can_create, can_edit, and can_delete. Transaction records are immutable. In a payment platform, you never modify a transaction record after creation. You create new records (refunds, adjustments) that reference the original. This is a fundamental principle of financial auditing -- the ledger is append-only.

The complete list of admin views:

SessionMiddleware for Cookie Authentication

SQLAdmin uses cookie-based sessions for authentication, which requires SessionMiddleware from Starlette:

python# main.py
from starlette.middleware.sessions import SessionMiddleware
from sqladmin import Admin
from sqladmin.authentication import AuthenticationBackend

class AdminAuth(AuthenticationBackend):
    async def login(self, request) -> bool:
        form = await request.form()
        username = form.get("username")
        password = form.get("password")

        if username == ADMIN_USERNAME and verify_password(password, ADMIN_PASSWORD_HASH):
            request.session["authenticated"] = True
            request.session["username"] = username
            return True
        return False

    async def logout(self, request) -> bool:
        request.session.clear()
        return True

    async def authenticate(self, request) -> bool:
        return request.session.get("authenticated", False)

app = FastAPI() app.add_middleware(SessionMiddleware, secret_key=SESSION_SECRET_KEY) BLANK admin = Admin( app=app, engine=engine, authentication_backend=AdminAuth(secret_key=SESSION_SECRET_KEY), base_url="/thales", # Admin route ) ```

The admin panel now lives at /thales instead of the UUID route. This is a meaningful path for Thales (the CEO and sole admin) while still being non-obvious to automated scanners. The SessionMiddleware stores authentication state in an encrypted cookie, so there are no server-side sessions to manage or expire.

The column_filters Compatibility Fix

In Session 038, we encountered 500 errors when trying to use column filters in SQLAdmin. The issue was a compatibility problem between our version of SQLAdmin and the way we defined column_filters:

python# Broken: caused 500 errors in SQLAdmin 0.16.x
class TransactionAdmin(ModelView, model=Transaction):
    column_filters = [
        Transaction.status,
        Transaction.provider,
        Transaction.created_at,
    ]

The fix required specifying filters as strings instead of model attributes:

python# Fixed: string-based column references
class TransactionAdmin(ModelView, model=Transaction):
    column_filters = [
        "status",
        "provider",
        "created_at",
    ]

This was a subtle compatibility issue that produced no useful error message -- just a generic 500 error when clicking the filter button. We traced it through the SQLAdmin source code to a type mismatch in the filter generation logic. The string-based approach works consistently across SQLAdmin versions.

What the Migration Eliminated

The migration to SQLAdmin eliminated:

• ~3,000 lines of SolidJS admin code -- components, routes, state management
• 7 custom admin API endpoints -- replaced by SQLAdmin's built-in CRUD
• A separate build pipeline -- no more Vite build for the admin frontend
• Admin-specific deployment configuration -- the admin is part of the backend process
• Frontend routing for admin -- no more client-side router for admin pages

What it preserved:

• Role-based access control -- enforced through the AuthenticationBackend
• Read-only transaction records -- explicit can_edit = False
• Audit trail immutability -- AuditLog admin is fully read-only
• Search and filter capabilities -- built into SQLAdmin

Lessons From the Migration

Start with SQLAdmin if you are a small team. We built a custom admin first because it felt like the "right" approach. In retrospect, SQLAdmin from day one would have saved weeks of work. Custom admin panels make sense when you have specific UX requirements for a team of admins. For a single-admin fintech, auto-generated CRUD is more than sufficient.

The metadata column name will bite you. This is a well-known SQLAlchemy gotcha, but it caught us because we were not using ORM models initially. The lesson: if you are using raw SQL now but plan to add ORM later, avoid reserved attribute names from the start.

Read-only views are a feature, not a limitation. Making certain models non-editable in the admin panel is not about restricting functionality. It is about encoding business rules into the tools. If a transaction should never be edited, the admin tool should make editing physically impossible -- not just discouraged.

Cookie sessions are fine for single-admin setups. We initially over-engineered with JWT for the admin panel. Cookie sessions via SessionMiddleware are simpler, more secure (no token in localStorage), and perfectly adequate when you have one admin.

This article is part of the "How We Built 0fee.dev" series. 0fee.dev is a payment orchestrator covering 53+ providers across 200+ countries, built by Juste A. GNIMAVO and Claude from Abidjan with zero human engineers. Follow the series for the complete build story.