Database Schema

Relevant source files

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

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

The database is the persistence layer for the Q&A Dashboard application, responsible for storing all user-generated content, including user accounts, questions, and answers. The application uses a PostgreSQL relational database, with SQLAlchemy as the Object-Relational Mapper (ORM) to define and interact with the data models in Python.

The schema is designed around three core entities: User, Question, and Answer. These entities are linked through foreign key relationships to maintain data integrity and represent the connections between users, their questions, and the answers provided. Pydantic schemas are used at the API layer to validate incoming data and serialize outgoing data, ensuring a clear contract between the frontend and backend.

Database Setup and Initialization

The database connection is managed in backend/database.py. An engine is created using the DATABASE_URL specified in the environment settings. A SessionLocal class is configured to provide database sessions for transactions.

A get_db dependency function is provided to inject a database session into API route handlers, ensuring that the session is properly opened and closed for each request.

Sources: backend/database.py:15-21, backend/database.py:91-96

Initialization Flow

The database tables are created by the init_db() function, which calls Base.metadata.create_all(bind=engine). This function is invoked once at application startup within the lifespan async context manager. During this startup process, the application also checks for and creates a default "Anonymous" user if one does not already exist.

Sources: backend/database.py:88-89, backend/main.py:11-34

The following diagram illustrates the database initialization process at application startup.

Entity-Relationship Diagram

The diagram below shows the entities in the database and their relationships. A user can have multiple questions and multiple answers. A question can have multiple answers.

Data Models (SQLAlchemy)

The data models are defined as Python classes using SQLAlchemy's declarative base in backend/database.py.

User Model

Represents a user account in the system.

Sources: backend/database.py:38-48

Column	Type	Constraints	Description
id	UUID	Primary Key, Index	Unique identifier for the user.
username	String(255)	Unique, Index, Not Null	The user's chosen username.
email	String(255)	Unique, Index, Not Null	The user's email address, used for login.
password	String(255)	Not Null	The user's hashed password.
is_admin	Boolean	Default: False	Flag indicating if the user has admin privileges.
created_at	DateTime	Default: utcnow	Timestamp of when the user account was created.

Relationships:

• questions: One-to-many relationship with the Question model.
• answers: One-to-many relationship with the Answer model.

Sources: backend/database.py:47-48

Question Model

Represents a question posted by a user.

Sources: backend/database.py:51-66

Column	Type	Constraints	Description
id	UUID	Primary Key, Index	Unique identifier for the question.
user_id	UUID	Foreign Key (users.id), Not Null	The ID of the user who posted the question.
message	Text	Not Null	The content of the question.
status	Enum(QuestionStatus)	Default: Pending	The current status of the question.
created_at	DateTime	Default: utcnow, Index	Timestamp of when the question was posted.
answered_at	DateTime	Nullable	Timestamp of when the question was marked as answered.
created_by	Text	Not Null, Default: Anonymous	The username of the creator.

Relationships:

• user: Many-to-one relationship back to the User model.
• answers: One-to-many relationship with the Answer model. If a question is deleted, all its answers are also deleted (cascade="all, delete-orphan").

Sources: backend/database.py:63-66

QuestionStatus Enum

The status column in the questions table uses an enumeration to represent the state of a question.

Value	Description
Pending	The default status for a new question.
Escalated	The question has been marked for priority attention.
Answered	The question has been resolved.

Sources: backend/database.py:24-27

Answer Model

Represents an answer to a specific question, posted by a user.

Sources: backend/database.py:69-80

Column	Type	Constraints	Description
id	UUID	Primary Key, Index	Unique identifier for the answer.
question_id	UUID	Foreign Key (questions.id), Not Null	The ID of the question this answer belongs to.
user_id	UUID	Foreign Key (users.id), Not Null	The ID of the user who posted the answer.
message	Text	Not Null	The content of the answer.
created_at	DateTime	Default: utcnow	Timestamp of when the answer was posted.
created_by	Text	Not Null, Default: Anonymous	The username of the creator.

Relationships:

• question: Many-to-one relationship back to the Question model.
• user: Many-to-one relationship back to the User model.

Sources: backend/database.py:82-83

Pydantic Schemas

Pydantic models in backend/schemas.py are used for data validation and serialization in the API endpoints. They define the expected shape of request bodies and the structure of response payloads.

The following diagram shows how a request to create a question flows from a Pydantic schema to a SQLAlchemy model.

User Schemas

• UserCreate: For new user registration. Contains username, email, and password.
• UserLogin: For user login. Contains email and password.
• UserResponse: For serializing user data in API responses. Excludes the password.

Sources: backend/schemas.py:6-26

Question Schemas

• QuestionCreate: For creating a new question. Contains message.
• QuestionUpdate: For updating a question's status. Contains an optional status.
• QuestionResponse: For serializing a single question.
• QuestionDetailResponse: Extends QuestionResponse to include a list of AnswerResponse objects.

Sources: backend/schemas.py:29-50

Answer Schemas

• AnswerCreate: For creating a new answer. Contains message.
• AnswerResponse: For serializing an answer, including the nested UserResponse of the author.

Sources: backend/schemas.py:53-68