Continue with FastAPI Stack for Web Application Backend

[ksmuczynski]

Status: Accepted

Date: 2025-08-08

Context and Problem Statement

Following the outcome of the Water Data Discovery Report and its recommendation to adopt a product-mode monolithic architecture, NMBGMR needs to commit to a backend technology stack. Previous work was developed using FastAPI and SQLAlchemy. However, a formal evaluation was necessary to ensure this stack is the correct long-term choice compared to a more established, all-in-one framework like Django. The decision hinges on which stack best balances development velocity, performance, scalability, and long-term maintainability for our API-centric application.

Decision Drivers

• Development Speed: The chosen stack should enable rapid development of new API endpoints with concise, readable code.
• Existing Work: A non-trivial amount of foundational code has already been written using FastAPI.
• Flexibility: The ability for developers to select and integrate components is valued.
• Querying Ability and Complexity: The stack's ORM must be powerful enough for complex queries while remaining manageable for developers.
• System-Wide Audit Trail Implementation: The framework must support a robust and maintainable way to track data changes across the system.
• Database Management: The process for defining models and managing schema migrations must be robust.
• Developer Experience: Features like automatic documentation and modern language features (e.g., async/await, type hints) are highly desirable.

Considered Options

1. FastAPI Stack: Continue development using the combination of FastAPI (web framework) and SQLAlchemy (ORM). Begin adoption of Alembic (migrations).
2. Django Stack: Refactor the existing code and switch to the Django framework, using the Django ORM and Django REST Framework (DRF).

Decision Outcome

Chosen option: "1. FastAPI Stack", because it provides superior performance, a modern and efficient developer experience, and leverages the work already completed. The benefits of its async architecture, Pydantic-based data validation, and the powerful SQLAlchemy ORM outweigh the convenience of Django's "batteries-included" approach for our specific needs.

Pros and Cons of the Options

1. FastAPI Stack

• Pros:
  • Performance: Exceptionally high speed due to its native asyncio support.
  • Powerful Querying: SQLAlchemy provides extremely powerful and flexible querying capabilities. Its query syntax is also closely related to raw SQL.
  • Flexible Auditing: SQLAlchemy's event system allows for a highly flexible, custom-built audit trail with fine-grained control.
  • Concise Code: Less boilerplate for API creation thanks to Pydantic and type hints.
  • Auto-documentation: Automatically generates interactive API docs (Swagger UI/ReDoc).
• Cons:
  • Manual Assembly: Requires integrating and managing separate libraries for the ORM, migrations, etc.
  • DIY Audit Trail: Requires manual implementation of the audit trail logic using SQLAlchemy Continuum; no out-of-the-box solution.
  • No Built-in Admin: An admin interface is not included and must be built or added via a third-party library.
  • DIY Authentication: Requires manual implementation of user management and security logic.

2. Django Stack

• Pros:
  • Effortless Auditing: The django-simple-history package provides a powerful, out-of-the-box audit trail solution with full admin integration.
  • All-in-One: A mature, integrated solution that reduces setup time.
  • Simple Querying for Common Cases: The Django ORM has an intuitive syntax for most common queries.
  • Django Admin: Includes a powerful, production-ready admin panel.
  • Battle-Tested Security: Comes with a built-in, robust authentication and authorization system.
• Cons:
  • Slower Performance: Primarily synchronous, making it inherently slower than native async frameworks.
  • Less Flexible Querying: Can be less intuitive for highly complex SQL queries compared to SQLAlchemy.
  • Monolithic Design: Can be rigid and less flexible than a micro-framework approach.
  • Technical debt: Would require abandoning the existing FastAPI codebase.

Consequences

Positive

• We will retain the high-performance foundation required for our application's API.
• The development team can continue to leverage modern Python features, leading to clean, maintainable code.
• The automatic API documentation will streamline collaboration with front-end developers and external consumers.
• We have access to the full power of the SQLAlchemy toolkit, ensuring we will not be limited by our ORM as data-access patterns grow in complexity.
• SQLAlchemy pairs well with Pydantic to easily create functions for updating data.
• We can easily reuse any built-in functions from the database (like SUM() to add numbers or NOW() to get the current time) using SQLAlchemy's func object.

Negative

• The team is responsible for implementing and securing the authentication system from scratch. Using a hosted service is also an option.
• The team must design, build, and maintain a custom audit trail system using SQLAlchemy Continuum.
• A solution for an admin interface must be researched, integrated, and maintained separately.
• The team bears the responsibility of ensuring the chosen components (FastAPI, SQLAlchemy, etc.) work together seamlessly through version upgrades.
• SQLAlchemy documentation is difficult to understand.

[jacob-a-brown]

I agree that many of the negatives come from us having to implement a number of features ourselves, like audits. To note on the enumerated Negatives

The team is responsible for implementing and securing the authentication system from scratch.

• I agree that implementing and securing the authentication system ourselves is quite a heavy (and important!) lift. That'll take some time and careful consideration to implement. It may also cost us some resources if we use a hosted service (which would likely be more secure).

The team must design, build, and maintain a custom audit trail system using SQLAlchemy events.

• We are using SQLAlchemy-Continuum for such audits. It seems pretty easy to implement, but we need to make sure it works correctly with Alembic. We also need to make sure that the correct tables are being audited (should all of them?)

A solution for an admin interface must be researched, integrated, and maintained separately.

• Will the admin interface be part of DataManager, or will we have a separate one? It'd be nice for it to handle authentication/users/persmissions.

For the Positives I would add:

• SQL Alchemy engenders easily maintainable and reusable functions for basic interactions with the database, such as getting records by their IDs and adding records to the database. It also couples well with Pydantic for reusable update functions.
• SQL Alchemy enables easy access and use of database-defined functions through its func object that are not available from SQL Alchemy directly .

For the Negatives I would add:

• SQL Alchemy's documentation is arcane and difficult to comprehend.

[ksmuczynski]

Will the admin interface be part of DataManager, or will we have a separate one? It'd be nice for it to handle authentication/users/persmissions.

I think there is still some discussion on this. Is the admin interface the same as the authentication system? Are there existing services that could provide us with both?

I've incorporated your comments and suggestions into the ADR.