Skip to content

Database Schema

The STELLA App uses a relational database schema to track:

  • sessions – Tracks user sessions and interactions during experiments.
  • results – Stores search or recommendation results returned to users.
  • feedbacks – Records user feedback on results.
  • systems – Contains metadata about the experimental systems.

Accessing the Database

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

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

Then connect to PostgreSQL: 

>> psql -h localhost -p 5430 -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 session result.

Attributes:

Name Type Description
id int

Unique identifier of the feedback.
start datetime

Timestamp when the feedback started.
end datetime

Timestamp when the feedback ended.
session_id str

Foreign key linking to the session.
interleave bool

Whether interleaving was applied during evaluation.
results list[Result]

Relationship to results associated with this feedback.
clicks JSON

Click information or user interactions captured during the feedback.
Source code in app/web/app/models.py 
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
class Feedback(db.Model):
    """
    Represents user feedback for a session result.

    Attributes:
        id (int): Unique identifier of the feedback.
        start (datetime): Timestamp when the feedback started.
        end (datetime): Timestamp when the feedback ended.
        session_id (str): Foreign key linking to the session.
        interleave (bool): Whether interleaving was applied during evaluation.
        results (list[Result]): Relationship to results associated with this feedback.
        clicks (JSON): Click information or user interactions captured during the feedback.
    """
    __tablename__ = "feedbacks"
    id = db.Column(db.Integer, primary_key=True)
    start = db.Column(db.DateTime(timezone=True), nullable=True)
    end = db.Column(db.DateTime(timezone=True), nullable=True)
    session_id = db.Column(db.String(64), db.ForeignKey("sessions.id"))
    interleave = db.Column(db.Boolean)
    results = db.relationship("Result", backref="feedback", lazy="dynamic")
    clicks = db.Column(db.JSON)

Result

Bases: Model

Represents the result of a user query or interaction in a session.

Attributes:

Name Type Description
id int

Unique identifier for the result.
session_id str

Foreign key linking to the session.
system_id int

Foreign key linking to the system.
feedback_id int

Foreign key linking to feedback.
type str

Type of the result (e.g., ranker/recommender).
q str

Query string submitted by the user.
q_date datetime

Date of the query.
q_time int

Time of the query in seconds or milliseconds.
num_found int

Number of results found by the system.
page int

Page number in paginated results.
rpp int

Results per page.
hits int

Total hits returned.
items JSON

Raw items returned by the system.
tdi int

Foreign key to another result for tracking dependencies.
custom_response JSON

Optional custom response for the result.
Source code in app/web/app/models.py 
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
class Result(db.Model):
    """
    Represents the result of a user query or interaction in a session.

    Attributes:
        id (int): Unique identifier for the result.
        session_id (str): Foreign key linking to the session.
        system_id (int): Foreign key linking to the system.
        feedback_id (int): Foreign key linking to feedback.
        type (str): Type of the result (e.g., ranker/recommender).
        q (str): Query string submitted by the user.
        q_date (datetime): Date of the query.
        q_time (int): Time of the query in seconds or milliseconds.
        num_found (int): Number of results found by the system.
        page (int): Page number in paginated results.
        rpp (int): Results per page.
        hits (int): Total hits returned.
        items (JSON): Raw items returned by the system.
        tdi (int): Foreign key to another result for tracking dependencies.
        custom_response (JSON): Optional custom response for the result.
    """

    __tablename__ = "results"
    id = db.Column(db.Integer, primary_key=True)
    session_id = db.Column(db.String(64), db.ForeignKey("sessions.id"))
    system_id = db.Column(db.Integer, db.ForeignKey("systems.id"))
    feedback_id = db.Column(db.Integer, db.ForeignKey("feedbacks.id"))
    type = db.Column(db.String(64), index=True)
    q = db.Column(db.String(2048), index=True)
    q_date = db.Column(db.DateTime, nullable=True)
    q_time = db.Column(db.Integer)
    num_found = db.Column(db.Integer)
    page = db.Column(db.Integer)
    rpp = db.Column(db.Integer)
    hits = db.Column(db.Integer)
    items = db.Column(db.JSON)
    tdi = db.Column(db.Integer, db.ForeignKey("results.id"))
    custom_response = db.Column(db.JSON, nullable=True)

    @property
    def serialize(self):
        """
        Serialize the result object into a dictionary.
        """
        return {
            "rid": self.id,
            "session_id": self.session_id,
            "system_id": self.system_id,
            "feedback_id": self.feedback_id,
            "type": self.type,
            "q": self.q,
            "q_date": self.q_date,
            "q_time": self.q_time,
            "num_found": self.num_found,
            "page": self.page,
            "rpp": self.rpp,
            "hits": self.hits,
            "items": self.items,
            "tdi": self.tdi,
            "custom_response": self.custom_response,
        }

serialize property

Serialize the result object into a dictionary.

Session

Bases: Model

Represents a user session for STELLA experiments.

Attributes:

Name Type Description
id str

Unique identifier of the session.
start datetime

Timestamp when the session started.
end datetime

Timestamp when the session ended.
site_user str

Identifier of the user at the site.
system_ranking int

Foreign key to the system used for ranking.
system_recommendation int

Foreign key to the system used for recommendations.
feedbacks list[Feedback]

Relationship to feedback objects associated with the session.
exit bool

Whether the session was exited by the user.
sent bool

Whether the session data has been sent to STELLA server.
Source code in app/web/app/models.py 
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
class Session(db.Model):
    """
    Represents a user session for STELLA experiments.

    Attributes:
        id (str): Unique identifier of the session.
        start (datetime): Timestamp when the session started.
        end (datetime): Timestamp when the session ended.
        site_user (str): Identifier of the user at the site.
        system_ranking (int): Foreign key to the system used for ranking.
        system_recommendation (int): Foreign key to the system used for recommendations.
        feedbacks (list[Feedback]): Relationship to feedback objects associated with the session.
        exit (bool): Whether the session was exited by the user.
        sent (bool): Whether the session data has been sent to STELLA server.
    """
    __tablename__ = "sessions"
    id = db.Column(db.String(64), primary_key=True)
    start = db.Column(db.DateTime(timezone=True), nullable=True)
    end = db.Column(db.DateTime(timezone=True), nullable=True)
    site_user = db.Column(db.String(64), index=True)
    system_ranking = db.Column(db.Integer, db.ForeignKey("systems.id"))
    system_recommendation = db.Column(db.Integer, db.ForeignKey("systems.id"))
    feedbacks = db.relationship("Feedback", backref="session", lazy="dynamic")
    exit = db.Column(db.Boolean)
    sent = db.Column(db.Boolean)

System

Bases: Model

Represents an experimental system (ranker or recommender) in STELLA.

Attributes:

Name Type Description
id int

Unique identifier for the system.
name str

Name of the system.
type str

Type of the system, e.g., 'ranker' or 'recommender'.
results list[Result]

Relationship to results generated by this system.
num_requests int

Number of requests processed by this system.
system_type str

Optional additional type information for the system.
num_requests_no_head int

Number of requests excluding head queries.
Source code in app/web/app/models.py 
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
class System(db.Model):
    """
    Represents an experimental system (ranker or recommender) in STELLA.

    Attributes:
        id (int): Unique identifier for the system.
        name (str): Name of the system.
        type (str): Type of the system, e.g., 'ranker' or 'recommender'.
        results (list[Result]): Relationship to results generated by this system.
        num_requests (int): Number of requests processed by this system.
        system_type (str): Optional additional type information for the system.
        num_requests_no_head (int): Number of requests excluding head queries.
    """
    __tablename__ = "systems"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), index=True)
    type = db.Column(db.String(64), index=True)
    results = db.relationship("Result", backref="system", lazy="dynamic")
    num_requests = db.Column(db.Integer)
    system_type = db.Column(db.String(64), index=True)
    num_requests_no_head = db.Column(db.Integer)