Database Setup (AWS RDS)

Relevant source files

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

• backend/settings.py
• backend/database.py
• README.md
• backend/main.py
• backend/api/questions.py
• backend/schemas.py

The QA Dashboard application utilizes a PostgreSQL database, intended to be hosted on AWS RDS, as its persistent data store. The backend, built with FastAPI, interacts with the database through the SQLAlchemy ORM for all data operations, including managing users, questions, and answers.

This document outlines the database configuration, connection management, schema, and initialization process. The setup is designed to be configurable through environment variables, facilitating deployment across different environments.

Sources: README.md:10, README.md:25

Configuration

Database connection details are managed externally through environment variables, which are loaded by the application's settings module. This approach avoids hardcoding credentials and allows for flexible deployment.

Sources: backend/settings.py, README.md:65-71

Environment Variables

The following environment variables are required in the .env file for the backend to connect to the PostgreSQL database.

Variable	Description	Example Value
DB_HOST	The hostname of the RDS instance.	your-rds-endpoint.rds.amazonaws.com
DB_PORT	The port number for the database.	5432
DB_NAME	The name of the database.	qa_dashboard
DB_USER	The username for database access.	admin
DB_PASSWORD	The password for the database user.	your-secure-password
DEBUG	Toggles SQLAlchemy's echo mode for SQL logging.	True

Sources: README.md:154-159, backend/settings.py:10-15, backend/database.py:15

Database URL Construction

The settings.py module constructs the full DATABASE_URL string from these environment variables, which is then used by SQLAlchemy to establish a connection.

Sources: backend/settings.py:17-23

Connection and Session Management

The application uses SQLAlchemy's engine and session management tools to handle database connections and transactions.

Sources: backend/database.py

SQLAlchemy Engine

A single SQLAlchemy engine is created using the DATABASE_URL. The pool_pre_ping=True argument helps prevent connection issues by checking if a connection is still alive before using it from the pool.

# backend/database.py:13-17
engine = create_engine(
    settings.DATABASE_URL,
    echo=settings.DEBUG,
    pool_pre_ping=True,
)

Session Factory

A sessionmaker factory named SessionLocal is configured to create new database session objects. These sessions are not autocommitted or autoflushed, providing granular control over transactions.

# backend/database.py:19
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

get_db Dependency

A FastAPI dependency, get_db, is used to provide a database session to API route handlers. This function ensures that a session is created for each request and is properly closed afterward, even if errors occur. This pattern is crucial for efficient connection pooling and resource management.

# backend/database.py:100-105
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

The following diagram illustrates the session lifecycle for an API request.

This diagram shows how the get_db dependency provides a database session to an API endpoint and ensures it is closed after the request is complete.

Sources: backend/database.py:100-105, backend/api/questions.py:31

Database Schema and Models

The database schema consists of three main tables: users, questions, and answers. These are defined as SQLAlchemy declarative models, which map Python classes to database tables.

Sources: backend/database.py:25-90, README.md:128-151

Entity Relationship Diagram

The diagram below shows the relationships between the core entities.

This ER diagram illustrates that a user can have multiple questions and answers. Each question can have multiple answers, and each answer belongs to exactly one question and one user.

Sources: backend/database.py:36-90

Table: users

Stores user account information, including credentials and roles.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY, default=uuid4	Unique identifier for the user.
username	String(255)	UNIQUE, NOT NULL	User's chosen username.
email	String(255)	UNIQUE, NOT NULL	User's email address, used for login.
password	String(255)	NOT NULL	Hashed password.
is_admin	Boolean	default=False	Flag indicating if the user is an admin.
created_at	DateTime	default=datetime.utcnow	Timestamp of user creation.

Sources: backend/database.py:36-46

Table: questions

Stores the questions posted by users.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY, default=uuid4	Unique identifier for the question.
user_id	UUID	FOREIGN KEY (users.id), NOT NULL	The user who posted the question.
message	Text	NOT NULL	The content of the question.
status	Enum	default='Pending'	The current status (Pending, Escalated, Answered).
created_at	DateTime	default=datetime.utcnow, INDEX	Timestamp of question creation.
answered_at	DateTime	NULLABLE	Timestamp when the question was marked as answered.
created_by	Text	NOT NULL, default='Anonymous'	The username of the creator.

Sources: backend/database.py:49-63, backend/database.py:25-29

Table: answers

Stores answers related to a specific question.

Column	Type	Constraints	Description
id	UUID	PRIMARY KEY, default=uuid4	Unique identifier for the answer.
question_id	UUID	FOREIGN KEY (questions.id), NOT NULL	The question this answer belongs to.
user_id	UUID	FOREIGN KEY (users.id), NOT NULL	The user who posted the answer.
message	Text	NOT NULL	The content of the answer.
created_at	DateTime	default=datetime.utcnow	Timestamp of answer creation.
created_by	Text	NOT NULL, default='Anonymous'	The username of the creator.

Sources: backend/database.py:69-79

Database Initialization

The database tables are created automatically when the FastAPI application starts up. This is handled within the lifespan asynchronous context manager.

init_db Function

The init_db function calls Base.metadata.create_all(bind=engine), which instructs SQLAlchemy to create all tables defined as subclasses of Base (the declarative base) if they do not already exist.

# backend/database.py:93-94
def init_db():
    Base.metadata.create_all(bind=engine)

Application Startup Flow

The lifespan manager in main.py ensures that init_db() is called once at startup. It also creates a default "Anonymous" user if one does not exist, which is used for guest actions.

This flowchart shows the sequence of events during application startup, from database initialization to the creation of the default user.

Sources: backend/main.py:13-41, backend/database.py:93-94