DynamoDB and Plant Tracer¶

The Plant Tracer webapp uses AWS DynamoDB to store:

The user list
The course list
The enrollment join table (which users are in which courses)
The movie list
The per-frame trackpoint annotations
API keys and audit logs

Originally this was stored in a MySQL database. We migrated to DynamoDB for cost — most uses of Plant Tracer can fit within the DynamoDB free tier, while the cost for running MySQL is upwards of $50/month on AWS.

Each DynamoDB table is identified by an account and a table name. All table names share a common prefix controlled by the DYNAMODB_TABLE_PREFIX environment variable (e.g. demo-). For local development you can use the AWS DynamoDB local (downloadable version). We recommend using the version downloaded as a JAR file.

The canonical table model is split between the application item schemas and the table creation contract. Item fields are defined in src/app/schema.py, and attribute-name constants are defined at the top of src/app/odb.py. DynamoDB table definitions are in etc/dynamodb_tables.json. poetry run dbutil createdb creates the tables from that JSON file and populates the demo course; odbmaint.drop_tables() drops the configured tables for local test and reset workflows.

Use poetry run dbutil list-prefixes to list complete Plant Tracer table prefixes available through the current DynamoDB connection settings. Other dbutil commands require DYNAMODB_TABLE_PREFIX; when it is missing, they print the active AWS_REGION and the same available-prefix list before exiting.

The SAM/CloudFormation stack does not own DynamoDB tables. For an existing stack that still owns tables from an older template, retain or otherwise detach those table resources before deploying a template that removes them; a normal CloudFormation update treats removed resources as deleted resources.

Table Summary¶

All table names below are shown without the prefix. With DYNAMODB_TABLE_PREFIX=demo- the users table is named demo-users, etc.

Table	Purpose	Partition Key	Sort Key
`users`	One record per registered user	`user_id`	—
`unique_emails`	Enforces email address uniqueness across users	`email`	—
`api_keys`	One record per issued API key (a user may have several)	`api_key`	—
`courses`	One record per course	`course_id`	—
`course_users`	Enrollment join table — which users are in which courses	`course_id`	`user_id`
`movies`	One record per uploaded movie	`movie_id`	—
`movie_frames`	Per-frame trackpoint annotations and movie-scoped marker metadata	`movie_id`	`frame_number`
`logs`	Audit log entries	`log_id`	—

Table Details¶

users¶

One record per registered user.

Attribute	Type	Description
`user_id`	String (PK)	Unique identifier, prefixed `u` for regular users, `ud` for admins
`email`	String	User’s email address (unique, enforced via `unique_emails`)
`user_name`	String	Display name; may be blank
`created`	Integer	Unix epoch seconds at registration
`enabled`	Integer (0/1)	Whether the account is active
`super_role`	String enum	Cross-course admin role: `none`, `superauditor` for read-only admin access, or `superadmin` for read/write admin access. Legacy `super_auditor` and `super_admin` values are normalized at read time; rows without the field are treated as `none`.
`default_course_id`	String	Profile default used only when a request has no valid course context
`default_course_name`	String	Denormalized name of the default course
`courses`	List of strings	All courses the user is enrolled in
`admin_for_courses`	List of strings	Courses for which the user has admin privileges

Legacy primary_course_id and primary_course_name attributes are read only for migration compatibility. Run dbutil.py migrate-default-course-fields to preview the migration and repeat with --commit to write the default names and remove the legacy attributes.

unique_emails¶

The table normally contains one key-only record per canonical user email. It also contains the reserved planttracer-system:super-role-state singleton. That record stores a version and the sorted set of current superadmin user IDs; dbutil updates it transactionally with user role changes so concurrent operator commands cannot remove the final superadmin. The CLI reconciles the record from a consistent users-table scan before each role mutation.

api_keys¶

One record per issued API key. A user may hold multiple keys (e.g. after re-sending a login link). The key is sent as a cookie or POST parameter; the server validates it on every request.

Attribute	Type	Description
`api_key`	String (PK)	The key value, a random hex string
`user_id`	String	Owner of the key
`first_used_at`	Integer	Unix epoch seconds of first use (i.e. first login)
`last_used_at`	Integer	Unix epoch seconds of most recent use
`enabled`	Integer (0/1)	Whether the key is still valid

GSI: user_id_idx on user_id. Used by DDBO.get_user_login_times() to aggregate first/last login times across all of a user’s keys without a table scan.

courses¶

Attribute	Type	Description
`course_id`	String (PK)	Unique identifier for the course
`course_name`	String	Human-readable course name
`course_key`	String	Registration passphrase that students use to self-enroll
`admins_for_course`	List of strings	`user_id` values of all admins for this course
`max_enrollment`	Integer	Maximum number of students allowed to self-register (default 50)

course_users¶

A lightweight join table that records which users are enrolled in which courses. Each item has only the two key attributes: course_id (partition key) and user_id (sort key).

Querying course_users by course_id returns all enrolled user IDs efficiently — this is used by course_enrollments(course_id) in odb.py.

delete_user() removes the user’s course_users rows for every course listed on the user record. list_users_courses() still handles unknown course_users rows defensively because old or manually edited tables may contain stale enrollment records.

movies¶

One record per created movie, including rows whose direct upload is still pending. Key attributes:

movie_id, title, description, user_id, course_id, published (0/1; defaults to 1 on creation; 0 means hidden), deleted (0/1), status, total_frames, fps, width, height, upload_staging_urn (temporary upload S3 URN), movie_data_urn (durable S3 URN of the MP4), movie_zipfile_urn, movie_traced_urn, first_frame_urn, last_frame_tracked, research_use (0/1/None; None = not yet answered), credit_by_name (0/1/None; None = not yet answered), attribution_name, rotation (0/90/180/270 degrees), needs_retracing (0/1; traced MP4 may be stale after marker edits).

Lifecycle fields are created_at (row allocation), uploaded_at (set only after staging is verified and copied to the durable S3 key), last_activity_at (latest movie write, excluding reads/downloads), upload_bytes_expected (the exact byte count signed into the direct-upload policy), upload_event_id, resize_queued_at, resize_started_at, and resized_at. date_uploaded is accepted only when reading legacy rows. DynamoDB is schemaless, so deploying these fields requires no table rebuild or backfill.

The logs table records movie.upload.completed, movie.resize.started, and movie.resize.completed. Entries identify the movie, user, course, and event time. Upload records may include EventBridge and S3 details; resize completion includes elapsed seconds.

See src/app/schema.py Movie class for the full schema and constraints.

movie_frames¶

Per-frame trackpoint storage. Keyed by (movie_id, frame_number). Real movie frames use non-negative frame_number values. Range queries and single-frame reads treat negative frame numbers as internal metadata, not as user-visible frames.

Each record’s trackpoints attribute is a list of objects with fields x, y, label, marker_id, frame_number, status, and err (all defined in the Trackpoint class in schema.py).

The marker lookup table is stored as a metadata item in movie_frames with frame_number=-100. It stores markers (marker_id to marker metadata), marker_labels (current label to marker_id), and marker_aliases (stored or legacy label to marker_id). Frame trackpoints may store marker_id; API responses resolve the current label through this marker map.

Data Consistency Notes¶

Email uniqueness is enforced by a transactional write to both users and unique_emails at registration time.
Course admin list (admins_for_course on the course record) is kept in sync with admin_for_courses on the user record by add_course_admin() and remove_course_admin().
Enrollment (course_users rows) is added by register_email() and removed by delete_user() for the courses listed on the user record.
Login times (first_used_at, last_used_at) live on api_keys, not users. list_users_courses() aggregates them per user via the user_id_idx GSI.

Schema and Naming Changes¶

Some naming changes were made for clarity or to avoid conflicts with DynamoDB’s reserved words.

Old name	New name	Reason
`name`	`user_name`	`name` is a DynamoDB reserved word