Database Schema

The STELLA Server also uses a relational database schema to track:

• roles – Defines authorization roles that govern access and permissions within the system.

• users – Represents all authenticated actors, including administrators, sites, and participants.

• sessions – Captures individual experimental sessions, linking site users with selected ranking and recommendation systems.

• results – Stores the outputs of ranking and recommendation requests, including returned items and query metadata.

• feedbacks – Records user interaction data and evaluative signals for specific results.

• systems – Contains metadata and configuration details for experimental and baseline systems evaluated in STELLA.

Accessing the Database

To access the STELLA Server database, run the following command:

sudo docker exec -it stella-db-server /bin/bash

Then connect to PostgreSQL:

>> psql -h localhost -p 5432 -U postgres

You can now execute SQL queries directly against the database.

Model Definitions

The model definitions are documented directly from the codebase:

Feedback

Bases: Model

Represents user feedback for a specific ranking or recommendation.

Feedback captures interaction signals such as clicks and timing, optionally supporting interleaved result presentations.

Attributes:

Name	Type	Description
id	int	Primary key of the feedback record.
start	datetime	Timestamp when feedback collection started.
end	datetime	Timestamp when feedback collection ended.
session_id	int	Foreign key referencing the associated session.
site_id	int	Foreign key referencing the site user.
interleave	bool | None	Whether interleaving was used.
results	list[Result]	Results associated with this feedback.
clicks	dict | list | None	User interaction data (e.g., clicks).

Source code in server/web/app/models.py

class Feedback(db.Model):
    """
    Represents user feedback for a specific ranking or recommendation.

    Feedback captures interaction signals such as clicks and timing,
    optionally supporting interleaved result presentations.

    Attributes:
        id (int): Primary key of the feedback record.
        start (datetime): Timestamp when feedback collection started.
        end (datetime): Timestamp when feedback collection ended.
        session_id (int): Foreign key referencing the associated session.
        site_id (int): Foreign key referencing the site user.
        interleave (bool | None): Whether interleaving was used.
        results (list[Result]): Results associated with this feedback.
        clicks (dict | list | None): User interaction data (e.g., clicks).
    """

    __tablename__ = "feedbacks"
    id = db.Column(db.Integer, primary_key=True)
    start = db.Column(db.DateTime, nullable=False)
    end = db.Column(db.DateTime, nullable=False)
    session_id = db.Column(db.Integer, db.ForeignKey("sessions.id"))
    site_id = db.Column(db.Integer, db.ForeignKey("users.id"))
    interleave = db.Column(db.Boolean)
    results = db.relationship("Result", backref="feedback", lazy="dynamic")
    clicks = db.Column(db.JSON)

    @property
    def serialize(self):
        return {
            "feedback_id": self.id,
            "session_id": self.session_id,
            "site_id": self.site_id,
        }

    def update(self, data):
        start = data.get("start")
        end = data.get("end")
        session_id = data.get("session_id")
        site_id = data.get("site_id")
        interleave = data.get("interleave")
        clicks = data.get("clicks")

        if start is not None:
            self.start = datetime.strptime(start, "%Y-%m-%d %H:%M:%S")
        if end is not None:
            self.end = datetime.strptime(end, "%Y-%m-%d %H:%M:%S")
        if session_id is not None:
            self.session_id = session_id
        if site_id is not None:
            self.site_id = site_id
        if interleave is not None:
            self.interleave = bool(interleave)
        if clicks is not None:
            self.clicks = json.loads(clicks)

    @staticmethod
    def from_json(json_feedback):
        start_raw = json_feedback.get("start")
        end_raw = json_feedback.get("end")

        if start_raw is None:
            start = None
        else:
            start = datetime.strptime(start_raw, "%Y-%m-%d %H:%M:%S")

        if end_raw is None:
            end = None
        else:
            end = datetime.strptime(end_raw, "%Y-%m-%d %H:%M:%S")

        interleave = json_feedback.get("interleave", False).lower() == "true"
        clicks_raw = json_feedback.get("clicks")
        clicks = clicks_raw if type(clicks_raw) is dict else json.loads(clicks_raw)

        feedback = Feedback(start=start, end=end, interleave=interleave, clicks=clicks)

        return feedback

    def to_json(self):

        json_feedback = {
            "id": self.id,
            "start": self.start.strftime("%Y-%m-%d %H:%M:%S"),
            "end": self.end.strftime("%Y-%m-%d %H:%M:%S"),
            "session_id": self.session_id,
            "site_id": self.site_id,
            "interleave": self.interleave,
            "clicks": self.clicks,
        }

        return json_feedback

Result

Bases: Model

Stores the output of a ranking or recommendation request.

Results capture query-level information and returned items for both rankings and recommendations and can later be linked to user feedback.

Attributes:

Name	Type	Description
id	int	Primary key of the result.
session_id	int	Foreign key referencing the associated session.
system_id	int	Foreign key referencing the system that produced the result.
feedback_id	int | None	Foreign key referencing associated feedback.
site_id	int	Foreign key referencing the site user.
participant_id	int | None	Foreign key referencing the participant user.
type	str	Type of result (e.g., ranking or recommendation).
q	str	Query string issued by the user.
q_date	datetime | None	Timestamp of when the query was issued.
q_time	int | None	Time taken to process the query.
num_found	int | None	Total number of items found.
page	int | None	Page number of the result.
rpp	int | None	Results per page.
items	dict | list	Returned result items in structured form.

Source code in server/web/app/models.py

class Result(db.Model):
    """
    Stores the output of a ranking or recommendation request.

    Results capture query-level information and returned items for both
    rankings and recommendations and can later be linked to user feedback.

    Attributes:
        id (int): Primary key of the result.
        session_id (int): Foreign key referencing the associated session.
        system_id (int): Foreign key referencing the system that produced the result.
        feedback_id (int | None): Foreign key referencing associated feedback.
        site_id (int): Foreign key referencing the site user.
        participant_id (int | None): Foreign key referencing the participant user.
        type (str): Type of result (e.g., ranking or recommendation).
        q (str): Query string issued by the user.
        q_date (datetime | None): Timestamp of when the query was issued.
        q_time (int | None): Time taken to process the query.
        num_found (int | None): Total number of items found.
        page (int | None): Page number of the result.
        rpp (int | None): Results per page.
        items (dict | list): Returned result items in structured form.
    """

    __tablename__ = "results"
    id = db.Column(db.Integer, primary_key=True)
    session_id = db.Column(db.Integer, db.ForeignKey("sessions.id"))
    system_id = db.Column(db.Integer, db.ForeignKey("systems.id"))
    feedback_id = db.Column(db.Integer, db.ForeignKey("feedbacks.id"))
    site_id = db.Column(db.Integer, db.ForeignKey("users.id"))
    participant_id = db.Column(db.Integer, db.ForeignKey("users.id"))
    type = db.Column(db.String(64), index=True)
    q = db.Column(db.String(512), index=True)
    q_date = db.Column(db.DateTime, nullable=True)
    q_time = db.Column(db.Integer)  # which datatype?
    num_found = db.Column(db.Integer)
    page = db.Column(db.Integer)
    rpp = db.Column(db.Integer)
    items = db.Column(db.JSON)

    @property
    def serialize(self):
        return {
            "ranking_id": self.id,
            "query": self.q,
            "query_date": self.q_date,
            "items": self.items,
        }

    def update(self, data):
        session_id = data.get("session_id")
        system_id = data.get("system_id")
        feedback_id = data.get("feedback_id")
        site_id = data.get("site_id")
        participant_id = data.get("participant_id")
        type = data.get("type")
        q = data.get("q")
        q_time = data.get("q_time")
        q_date = data.get("q_date")
        num_found = data.get("num_found")
        rpp = data.get("rpp")
        page = data.get("page")
        items = data.get("items")

        if session_id is not None:
            self.session_id = session_id
        if system_id is not None:
            self.system_id = system_id
        if feedback_id is not None:
            self.feedback_id = feedback_id
        if site_id is not None:
            self.site_id = site_id
        if participant_id is not None:
            self.participant_id = participant_id
        if type is not None:
            self.type = type
        if q is not None:
            self.q = q
        if q_time is not None:
            self.q_time = q_time
        if q_date is not None:
            self.q_date = datetime.strptime(q_date, "%Y-%m-%d %H:%M:%S")
        if num_found is not None:
            self.num_found = num_found
        if page is not None:
            self.page = page
        if rpp is not None:
            self.rpp = rpp
        if items is not None:
            self.items = items

    def to_json(self):
        result_session = {
            "session_id": self.session_id,
            "system_id": self.system_id,
            "feedback_id": self.feedback_id,
            "site_id": self.site_id,
            "participant_id": self.participant_id,
            "type": self.type,
            "q": self.q,
            "q_date": self.q_date.strftime("%Y-%m-%d %H:%M:%S"),
            "q_time": self.q_time,
            "num_found": self.num_found,
            "page": self.page,
            "rpp": self.rpp,
            "items": self.items,
        }

        return result_session

    @staticmethod
    def from_json(json_result):
        q = json_result.get("q")
        q_date_raw = json_result.get("q_date")

        if q_date_raw:
            q_date = datetime.fromisoformat(q_date_raw)
        else:
            q_date = None

        q_time = json_result.get("q_time")
        num_found = json_result.get("num_found")
        page = json_result.get("page")
        rpp = json_result.get("rpp")
        items_raw = json_result.get("items")
        items = json.loads(items_raw)

        result = Result(
            q=q,
            q_date=q_date,
            q_time=q_time,
            num_found=num_found,
            page=page,
            rpp=rpp,
            items=items,
        )

        return result

Role

Bases: Model

Represents an authorization role within the STELLA system.

Roles define the permissions and responsibilities of users accessing the STELLA Server. Typical roles include:

• Admin: platform administrators
• Site: experimental site owners
• Participant: contributors of experimental systems

Each user is associated with exactly one role.

Attributes:

Name	Type	Description
id	int	Primary key of the role.
name	str	Unique name of the role (e.g., "Admin", "Site", "Participant").
users	list[User]	Users assigned to this role.

Source code in server/web/app/models.py

class Role(db.Model):
    """
    Represents an authorization role within the STELLA system.

    Roles define the permissions and responsibilities of users accessing
    the STELLA Server. Typical roles include:

    - Admin: platform administrators
    - Site: experimental site owners
    - Participant: contributors of experimental systems

    Each user is associated with exactly one role.

    Attributes:
        id (int): Primary key of the role.
        name (str): Unique name of the role (e.g., "Admin", "Site", "Participant").
        users (list[User]): Users assigned to this role.
    """

    __tablename__ = "roles"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    users = db.relationship("User", backref="role", lazy="dynamic")

    def __repr__(self):
        return "<Role %r>" % self.name

System

Bases: Model

Represents a ranking or recommendation system evaluated in STELLA.

Systems can be experimental (submitted by participants) or baseline systems provided by sites and are linked to results generated during sessions.

Attributes:

Name	Type	Description
id	int	Primary key of the system.
name	str	Human-readable system name.
participant_id	int | None	Foreign key referencing the submitting participant.
type	str	System type (e.g., ranking or recommendation).
results	list[Result]	Results generated by this system.
url	str | None	Endpoint or reference URL of the system.
submitted	str | None	Submission identifier or flag.
status	str | None	Current system status.
site	int | None	Site identifier associated with the system.
submission_date	date | None	Date of system submission.

Source code in server/web/app/models.py

class System(db.Model):
    """
    Represents a ranking or recommendation system evaluated in STELLA.

    Systems can be experimental (submitted by participants) or baseline
    systems provided by sites and are linked to results generated during sessions.

    Attributes:
        id (int): Primary key of the system.
        name (str): Human-readable system name.
        participant_id (int | None): Foreign key referencing the submitting participant.
        type (str): System type (e.g., ranking or recommendation).
        results (list[Result]): Results generated by this system.
        url (str | None): Endpoint or reference URL of the system.
        submitted (str | None): Submission identifier or flag.
        status (str | None): Current system status.
        site (int | None): Site identifier associated with the system.
        submission_date (date | None): Date of system submission.
    """

    __tablename__ = "systems"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(512), index=True)
    participant_id = db.Column(db.Integer, db.ForeignKey("users.id"))
    type = db.Column(db.String(64), index=True)
    results = db.relationship("Result", backref="system", lazy="dynamic")
    url = db.Column(db.String(512), index=False)
    submitted = db.Column(db.String(64), index=False)
    status = db.Column(db.String(64), index=False)
    site = db.Column(db.Integer, index=False)
    submission_date = db.Column(db.Date, index=False)

    @property
    def serialize(self):
        return {
            "id": self.id,
            "name": self.name,
            "participant_id": self.participant_id,
            "type": self.type,
            "url": self.url,
        }

User

Bases: UserMixin, Model

In this case the term "user" considers all actors who USE and access the stella-server.

Users include administrators, experimental sites, and participants who submit or evaluate ranking and recommendation systems.

Attributes:

Name	Type	Description
id	int	Primary key of the user.
email	str	Unique email address.
username	str	Unique username.
role_id	int	Foreign key referencing the user's role.
password_hash	str	Secure hash of the user's password.
role	Role	Role associated with the user.

Source code in server/web/app/models.py

class User(UserMixin, db.Model):
    """
    In this case the term "user" considers all actors who USE and access the stella-server.

    Users include administrators, experimental sites, and participants who
    submit or evaluate ranking and recommendation systems.

    Attributes:
        id (int): Primary key of the user.
        email (str): Unique email address.
        username (str): Unique username.
        role_id (int): Foreign key referencing the user's role.
        password_hash (str): Secure hash of the user's password.
        role (Role): Role associated with the user.
    """

    __tablename__ = "users"
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(64), unique=True, index=True)
    username = db.Column(db.String(64), unique=True, index=True)
    role_id = db.Column(db.Integer, db.ForeignKey("roles.id"))
    password_hash = db.Column(db.String(256))

    @property
    def password(self):
        raise AttributeError("password is not a readable attribute")

    @property
    def serialize(self):
        return {"id": self.id, "username": self.username}

    @password.setter
    def password(self, password):
        self.password_hash = generate_password_hash(password)

    def generate_auth_token(self, expiration):
        expiration_timestamp = datetime.now(tz=timezone.utc) + timedelta(
            seconds=expiration
        )
        access_token = jwt.encode(
            {"id": self.id, "exp": expiration_timestamp},
            current_app.config["SECRET_KEY"],
            algorithm="HS256",
        )
        return access_token

    @staticmethod
    def verify_auth_token(token):
        try:
            data = jwt.decode(
                token, current_app.config["SECRET_KEY"], algorithms="HS256"
            )
            # data = s.loads(token)
        except jwt.ExpiredSignatureError:
            print("Token expired.")
            return None
        except jwt.InvalidTokenError:
            print("Invalid Token")
            return None
        return db.session.get(User, data["id"])

    def verify_password(self, password):
        return check_password_hash(self.password_hash, password)

    def __repr__(self):
        return "<User %r>" % self.username