Database Schema
Relevant source files
The following files were used as context for generating this wiki page:The Heyme application utilizes a MySQL database for data storage, which is central to its functionality of sending scheduled video messages. The database is responsible for persisting all information related to the video messages, including their content, recipient details, and delivery schedule.
The primary data entity is the Videos table, which stores all the necessary attributes for a video message. The application interacts with the database through API endpoints using the knex query builder to perform create, read, and update operations. This document outlines the schema of the Videos table and the data flows associated with it.
Sources: README.md, package.json
Database Technology
The project is configured to use a MySQL database. This is managed through the knex SQL query builder and the mysql driver, as specified in the project's dependencies.
Sources: README.md, package.json:42,45
Entity Relationship Diagram
The core of the database schema is the Videos table. The following diagram illustrates its structure and attributes.
This diagram is an aggregation of data fields found across multiple files.
Sources: pages/api/store.ts:9-15, pages/api/dispatch.ts:32-34, pages/watch/[id].tsx:12-17
Schema Details
Videos Table
This table stores every video message record created by users.
| Column | Inferred Type | Description | Constraints |
|---|---|---|---|
id | INT, PRIMARY KEY | Unique identifier for each video record. Used to fetch specific videos for watching. | Not Null, Auto-increment |
video_url | VARCHAR | The URL pointing to the stored video file. | Not Null |
email | VARCHAR | The email address of the person who will receive the message. | Not Null |
send_to | VARCHAR | A string indicating if the message is for 'yourself' or someone else. | Not Null |
send_at | DATE | The date the video message is scheduled to be sent. Used to query for daily dispatches. | Not Null |
created_at | DATETIME | Timestamp when the video record was created. | Not Null |
sent | BOOLEAN / TINYINT(1) | A flag to mark if the video has been dispatched. Defaults to 0 (false). Updated to 1 (true) after sending. | Not Null, Default 0 |
Sources: pages/api/store.ts:9-15, pages/api/dispatch.ts:29,32, pages/watch/[id].tsx:12-17, 60, tests/api/watch.spec.tsx:18-23
Data Flow and Operations
The application interacts with the Videos table through several key API endpoints and server-side functions.
Video Record Creation
New video records are inserted into the Videos table via the /api/store API endpoint. The request body must contain video_url, email, send_at, send_to, and created_at.
The following diagram shows the sequence for storing a new video message.
Sources: pages/api/store.ts:6-26
Video Record Retrieval
Fetching a Single Video by ID
When a user visits a watch page (e.g., /watch/5), the server fetches the corresponding video data from the database using its id. This is handled by getServerSideProps, which calls the fetchVideoById utility function.
Sources: pages/watch/[id].tsx:57-62, tests/api/watch.spec.tsx:40-42
Fetching Videos for Daily Dispatch
A scheduled process calls the /api/dispatch endpoint. This endpoint fetches all video records from the Videos table where the send_at column matches the current date and the sent flag is not true.
Sources: pages/api/dispatch.ts:27-29, 42
Video Record Update
After an email with the video link has been successfully sent, the sent field for that video record is updated to 1 (true). This prevents the same message from being sent again. This operation is performed by the updateVideoToSent function within the /api/dispatch logic.
The following diagram illustrates the email dispatch and database update flow.
Sources: pages/api/dispatch.ts:31-34, 44-93