Coverage for server / connection / database.py: 87%
115 statements
« prev ^ index » next coverage.py v7.13.4, created at 2026-10-04 09:33 +0000
1import os
2import motor.motor_asyncio
3from pymongo.errors import OperationFailure
4from beanie import init_beanie
5from dotenv import load_dotenv
7# Remove when seed users are managed via Auth0 Management API.
8from server.models.account import Account, SubscriberAccount
9from server.models.assignment import Assignment, Submission
10from server.models.classes import ClassModel
11from server.models.clients import Client, School
12from server.models.themes import UserTheme, Theme
13from server.models.sharerequests import ShareRequest
14from server.models.class_message import ClassMessage
15from server.models.users import User
16from server.utilities.class_dedupe import ensure_class_dedupe_index
17from server.utilities.assignment_dedupe import DEDUPE_INDEX_NAME
20load_dotenv()
23def _tls_params() -> str:
24 """Return the TLS query-string fragment for Mongo URIs, or "".
26 SEC-6. Mongo traffic is currently PLAINTEXT: the driver is given a
27 ``mongodb://`` URI with no TLS options, so credentials and student records
28 cross the network in the clear.
30 This is opt-in and OFF by default, deliberately — and the reason is not
31 caution, it is that turning it on right now would break every connection.
32 Probed 2026-08-25: both mongod instances accept plain TCP and RESET the TLS
33 handshake, i.e. neither is configured for TLS. Enabling it server-side
34 (certificates + ``net.tls`` in mongod.conf) has to happen first; this code
35 exists so that switch is then a config change rather than a code change.
37 Env:
38 MONGODB_TLS "true" to enable (default off)
39 MONGODB_TLS_CA_FILE optional CA bundle for a private/internal CA
41 Note there is intentionally NO tlsAllowInvalidCertificates escape hatch.
42 TLS without certificate validation stops passive sniffing but not an active
43 MITM, and having the flag available is how it ends up switched on during a
44 certificate incident and left on.
45 """
46 if os.getenv("MONGODB_TLS", "false").strip().lower() not in ("1", "true", "yes"):
47 return ""
48 params = "&tls=true"
49 ca_file = os.getenv("MONGODB_TLS_CA_FILE", "").strip()
50 if ca_file:
51 params += f"&tlsCAFile={ca_file}"
52 return params
55def _with_tls(uri: str) -> str:
56 """Apply the TLS params to *uri*, unless it already specifies TLS itself.
58 An operator who sets MONGODB_URL by hand owns the whole string; appending a
59 second, possibly contradictory tls option to it would be worse than leaving
60 it alone.
61 """
62 if not uri or "tls=" in uri or "ssl=" in uri:
63 return uri
64 params = _tls_params()
65 if not params:
66 return uri
67 return uri + params if "?" in uri else uri + "?" + params.lstrip("&")
70# Add settings class
71class Settings:
72 """Database settings configuration"""
74 DB_NAME: str = os.getenv("DB_NAME", "")
75 DB_USER: str = os.getenv("DB_USER", "")
76 DB_PASSWORD: str = os.getenv("DB_PASSWORD", "")
77 DB_HOST: str = os.getenv("DB_HOST", "")
78 DB_AUTH_SOURCE: str = os.getenv("DB_AUTH_SOURCE", "")
79 DB_PORT: str = os.getenv("DB_PORT", "27017") # Default MongoDB port
80 MONGODB_URL: str = _with_tls(
81 os.getenv(
82 "MONGODB_URL",
83 (
84 f"mongodb://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/?authSource={DB_AUTH_SOURCE}&directConnection=true"
85 if DB_USER and DB_PASSWORD and DB_HOST
86 else "mongodb://localhost:27017/"
87 ),
88 )
89 )
91 # Staff Admin MongoDB connection
92 STAFF_ADMIN_DB_NAME: str = os.getenv("STAFF_ADMIN_DB_NAME", "")
93 STAFF_ADMIN_DB_USER: str = os.getenv("STAFF_ADMIN_DB_USER", "")
94 STAFF_ADMIN_DB_PASSWORD: str = os.getenv("STAFF_ADMIN_DB_PASSWORD", "")
95 STAFF_ADMIN_DB_HOST: str = os.getenv("STAFF_ADMIN_DB_HOST", "")
96 STAFF_ADMIN_DB_PORT: str = os.getenv("STAFF_ADMIN_DB_PORT", "27017")
97 STAFF_ADMIN_DB_AUTH_SOURCE: str = os.getenv("STAFF_ADMIN_DB_AUTH_SOURCE", "")
98 STAFF_ADMIN_MONGO_URL: str = _with_tls(
99 os.getenv(
100 "STAFF_ADMIN_MONGO_URL",
101 (
102 f"mongodb://{STAFF_ADMIN_DB_USER}:{STAFF_ADMIN_DB_PASSWORD}@{STAFF_ADMIN_DB_HOST}:{STAFF_ADMIN_DB_PORT}/?authSource={STAFF_ADMIN_DB_AUTH_SOURCE}"
103 if STAFF_ADMIN_DB_USER
104 and STAFF_ADMIN_DB_PASSWORD
105 and STAFF_ADMIN_DB_HOST
106 else "mongodb://localhost:27017/"
107 ),
108 )
109 )
112# Create settings instance
113settings = Settings()
115# Update existing variables to use settings
116# In test mode, conftest.py will monkey-patch the db variable
117# For now, create connection only if we have valid credentials
118if os.getenv("TESTING") == "true" or not settings.DB_USER or not settings.DB_PASSWORD:
119 # Test mode or missing credentials: use local connection as fallback
120 # conftest.py will monkey-patch this for tests
121 TEST_DB_URL = os.getenv("TEST_DB_URL", "mongodb://localhost:27017/")
122 TEST_DB_NAME = os.getenv("TEST_DB_NAME", settings.DB_NAME or "eruditiontx_local")
123 client = motor.motor_asyncio.AsyncIOMotorClient(TEST_DB_URL)
124 db = client[TEST_DB_NAME]
125else:
126 # Production mode: connect to configured database
127 client = motor.motor_asyncio.AsyncIOMotorClient(settings.MONGODB_URL)
128 db = client[settings.DB_NAME]
130# Staff Admin MongoDB connection
131# Create separate client and database for staff admin MongoDB
132staff_admin_client = None
133staff_admin_db = None
135# Connect if staff admin credentials are configured OR a direct URL is provided
136if (
137 settings.STAFF_ADMIN_DB_USER
138 and settings.STAFF_ADMIN_DB_HOST
139 and settings.STAFF_ADMIN_DB_AUTH_SOURCE
140) or os.getenv("STAFF_ADMIN_MONGO_URL"):
141 staff_admin_client = motor.motor_asyncio.AsyncIOMotorClient(
142 settings.STAFF_ADMIN_MONGO_URL
143 )
144 staff_admin_db = staff_admin_client[settings.STAFF_ADMIN_DB_NAME]
147async def init_db():
148 """
149 The `init_db` function initializes the database and sets up the document models for various question
150 types, accounts, activities, and users.
151 """
152 try:
153 await init_beanie(
154 database=client[settings.DB_NAME],
155 document_models=[
156 Account,
157 SubscriberAccount,
158 User,
159 ClassModel,
160 Assignment,
161 Submission,
162 ShareRequest,
163 Client,
164 School,
165 UserTheme,
166 Theme,
167 ClassMessage,
168 ],
169 )
171 # Indexes for the dashboard submissions statistics aggregation pipeline.
172 # Without these every lookup stage does a full collection scan.
173 await db["class_collection"].create_index(
174 [("teacher._id", 1)], background=True, name="idx_class_teacher_id"
175 )
176 await db["assignments_collection"].create_index(
177 [("assigned_class", 1)],
178 background=True,
179 name="idx_assignment_assigned_class",
180 )
181 # Compound index covers both the assignment match and the remarks group in one scan.
182 await db["submission_collection"].create_index(
183 [("assignment_id", 1), ("remarks", 1)],
184 background=True,
185 name="idx_submission_assignment_remarks",
186 )
188 # Duplicate detection for bulk question import. The check runs TWICE per
189 # import (once at review so duplicates are visible before anything is
190 # written, once at commit so they are skipped), and it queries
191 # {createdBy: <owner>, question: {$in: [...up to 500 texts]}}.
192 #
193 # Without this it is a full collection scan every time: measured on the
194 # live banks before adding it, 34,907 docs examined on teacher_questionbank
195 # and 69,361 on the staff bank, to return a handful of rows. The feature
196 # that fills the bank was paying to scan it, and the cost grows with every
197 # import. With the index the same query is an IXSCAN examining single
198 # digits.
199 await db["teacher_questionbank"].create_index(
200 [("createdBy", 1), ("question", 1)],
201 background=True,
202 name="dedup_createdBy_question",
203 )
204 # EI-1195: one submission per (student, assignment). This unique index is
205 # what actually prevents the duplicate documents that made the teacher
206 # gradebook and the student list disagree. It is best-effort at startup:
207 # if legacy duplicates still exist the creation raises, so we log and
208 # continue — run the dedupe migration, then a restart creates it cleanly.
209 try:
210 await db["submission_collection"].create_index(
211 [("student_id", 1), ("assignment_id", 1)],
212 unique=True,
213 name="uniq_submission_student_assignment",
214 )
215 except OperationFailure as index_error:
216 # E11000 = pre-existing duplicates: expected until the dedupe migration
217 # runs; log and continue. Any OTHER OperationFailure is a genuine
218 # problem (bad field, permissions) and gets an ERROR-level log so it
219 # is not silently swallowed. Non-OperationFailure errors (e.g. the DB
220 # is unreachable) still propagate and fail startup, as they should.
221 if index_error.code == 11000 or "duplicate key" in str(index_error).lower():
222 print(
223 "-- WARN [Database] - unique submission index not created yet "
224 "(pre-existing duplicates; run the EI-1195 dedupe migration, then restart): "
225 f"{index_error}"
226 )
227 else:
228 print(
229 "-- ERROR [Database] - unique submission index creation FAILED "
230 f"(not a duplicate-key issue — investigate): {index_error}"
231 )
233 # Staff-assigned catalog id carried by bulk imports (e.g. "MATH-ALG-001").
234 # UNIQUE so two imports cannot claim one catalog entry, and SPARSE because
235 # every question created through the UI has no questionId at all — a plain
236 # unique index would treat all of them as duplicate nulls and refuse to build.
237 #
238 # Per bank, and that is literal: this covers teacher_questionbank in
239 # teacher_student_db. Admin-Staff's global_questionbank is a separate database
240 # on a separate server, so the same id legitimately exists in both.
241 try:
242 await db["teacher_questionbank"].create_index(
243 [("questionId", 1)],
244 unique=True,
245 sparse=True,
246 background=True,
247 name="questionId_unique_sparse",
248 )
249 except OperationFailure as index_error:
250 # Same shape as the submission index above: duplicates are a data problem
251 # to fix, not a reason to refuse to start. Without the index duplicate
252 # catalog ids simply stop being rejected.
253 print(
254 "-- ERROR [Database] - questionId unique index creation FAILED — "
255 f"duplicate catalog ids will NOT be rejected until fixed: {index_error}"
256 )
258 # EI-3450 / EI-3451: one assignment per (created_by, assigned_class, title,
259 # date_open, date_close) create request. This index is the whole guard — a
260 # read-then-write check closes the retry case but NOT the concurrent one, since
261 # both readers see nothing and both insert. Only the database can arbitrate that
262 # race, and putting it here rather than in a service means all three create paths
263 # (common /create, teacher /create, staff) sit underneath the same constraint,
264 # which is what EI-3451 asks for.
265 #
266 # SPARSE, and that is what makes this safe to switch on: `dedupe_key` is absent on
267 # every assignment written before this shipped, so those documents are not indexed
268 # and no backfill or dedupe migration is needed. A plain unique index would read
269 # them all as duplicate nulls and refuse to build.
270 try:
271 await db["assignments_collection"].create_index(
272 [("dedupe_key", 1)],
273 unique=True,
274 sparse=True,
275 background=True,
276 name=DEDUPE_INDEX_NAME,
277 )
278 except OperationFailure as index_error:
279 # Consistent with the two indexes above: a failure here is a data problem to
280 # fix, not a reason to refuse to start. The cost of running without it is that
281 # duplicate creates stop being caught — the behaviour we had before this fix.
282 print(
283 "-- ERROR [Database] - assignment dedupe index creation FAILED — "
284 f"duplicate assignment creates will NOT be rejected until fixed: {index_error}"
285 )
287 # EI-T473: one live class per (teacher, title, section). Same reasoning as the
288 # assignment dedupe index above — the 409 check is read-then-write and loses the
289 # concurrent race. Partial on string keys, so classes without a key (written
290 # before this shipped, or soft-deleted) are not indexed and nothing to backfill.
291 try:
292 await ensure_class_dedupe_index(db["class_collection"])
293 except OperationFailure as index_error:
294 print(
295 "-- ERROR [Database] - class dedupe index creation FAILED — "
296 f"concurrent duplicate class creates will NOT be rejected until fixed: {index_error}"
297 )
299 # Log successful database connections
300 if (
301 os.getenv("TESTING") == "true"
302 or not settings.DB_USER
303 or not settings.DB_PASSWORD
304 ):
305 print(f"-- INFO [Database] - Connected to Test MongoDB: {db.name}")
306 else:
307 print(
308 f"-- INFO [Database] - Connected to Teacher Student MongoDB: {settings.DB_NAME}"
309 )
311 # if staff_admin_db is not None:
312 # print(f"-- INFO [Database] - Connected to Staff Admin MongoDB: {settings.STAFF_ADMIN_DB_NAME}")
313 except Exception as e:
314 print(print(f"\033[31mERROR: {e}\033[0m"))
317async def create_initial_users():
318 """
319 Creates initial admin and staff users in the database using environment variables.
321 This function reads environment variables for two default users (admin and staff)
322 and creates their accounts in the database if they don't already exist. Required
323 environment variables include:
325 Admin user:
326 - ADMIN_FNAME: First name
327 - ADMIN_MNAME: Middle name
328 - ADMIN_LNAME: Last name
329 - ADMIN_ROLE: User role
330 - ADMIN_EMAIL: Email address
331 - ADMIN_CREATEDBY: Creator reference
333 Staff user:
334 - STAFF_FNAME: First name
335 - STAFF_MNAME: Middle name
336 - STAFF_LNAME: Last name
337 - STAFF_ROLE: User role
338 - STAFF_EMAIL: Email address
339 - STAFF_CREATEDBY: Creator reference
340 """
341 ADMIN_FNAME = os.getenv("ADMIN_FNAME")
342 ADMIN_MNAME = os.getenv("ADMIN_MNAME")
343 ADMIN_LNAME = os.getenv("ADMIN_LNAME")
344 ADMIN_ROLE = os.getenv("ADMIN_ROLE")
345 ADMIN_EMAIL = os.getenv("ADMIN_EMAIL")
346 ADMIN_CREATEDBY = os.getenv("ADMIN_CREATEDBY")
348 STAFF_FNAME = os.getenv("STAFF_FNAME")
349 STAFF_MNAME = os.getenv("STAFF_MNAME")
350 STAFF_LNAME = os.getenv("STAFF_LNAME")
351 STAFF_ROLE = os.getenv("STAFF_ROLE")
352 STAFF_EMAIL = os.getenv("STAFF_EMAIL")
353 STAFF_CREATEDBY = os.getenv("STAFF_CREATEDBY")
355 await create_user(
356 email=ADMIN_EMAIL,
357 first_name=ADMIN_FNAME,
358 middle_name=ADMIN_MNAME,
359 last_name=ADMIN_LNAME,
360 role=ADMIN_ROLE,
361 created_by=ADMIN_CREATEDBY,
362 )
364 await create_user(
365 email=STAFF_EMAIL,
366 first_name=STAFF_FNAME,
367 middle_name=STAFF_MNAME,
368 last_name=STAFF_LNAME,
369 role=STAFF_ROLE,
370 created_by=STAFF_CREATEDBY,
371 )
374async def create_user(email, first_name, middle_name, last_name, role, created_by):
375 """
376 Creates a new user account in the database if it doesn't already exist.
378 Args:
379 email (str): User's email address
380 first_name (str): User's first name
381 middle_name (str): User's middle name
382 last_name (str): User's last name
383 role (str): User's role in the system
384 created_by (str): Reference to the user who created this account
386 Notes:
387 - Checks if an account with the given email already exists
388 - Stores NO credential (SEC-4). The Account model requires `password`,
389 so it is set to "" — the same convention Auth0-provisioned accounts
390 already use elsewhere (auth0_bearer, the user-sync webhook, and staff
391 account creation all write password=""). Auth0 is the only
392 authentication authority; a local hash here would be a second,
393 unmonitored credential store that nothing verifies.
394 - Prints status messages for account creation or existing accounts
395 - Handles and prints any exceptions that occur during creation
396 """
397 try:
398 account = await Account.find_one({"email": email})
399 if account:
400 print(
401 "-- INFO [Data seed - Creating initial account] - Account already exists"
402 )
403 else:
404 user_account = Account(
405 first_name=first_name,
406 middle_name=middle_name,
407 last_name=last_name,
408 role=role,
409 email=email,
410 organization="",
411 photo_url="",
412 password="",
413 created_by=created_by,
414 )
415 await user_account.insert()
416 print(
417 f"-- INFO [Data seed - Creating initial account] - Created {role} account success"
418 )
419 except Exception as e:
420 print("An error occured while creating initial account.")
421 print("Error: ", e)