EMmind MTL data and integrations
This page describes MTL data and integration contracts. Public EMmind uses separate AWS resources and data stores. The imported public snapshot had the same main PostgreSQL entities except for the later MTL Google verification fields on users.
PostgreSQL model
cbt-core uses TypeORM with snake-case naming, migrations, and a configured schema. Runtime schema synchronization and automatic migration execution are disabled.
Entity directory
| Entity | Important data | Notes |
|---|---|---|
users | UUID, profile, role/status, consent, Google verification, primary concern, referral source, counters, reminder and streak state | Password is excluded from ordinary selects. Email uses PostgreSQL citext. |
line_account | LINE ID, account UUID, display profile, optional access token, user ID | LINE ID, UUID, and user ID are unique. |
path_skill | Nine completion timestamps for the three CBT paths | One-to-one with user. |
path_skill_activity | Progress type, optional object ID, creation time | Progress-event history created for selected path updates; account deletion removes these rows. |
journal | Journal timestamp/date, summaries, generated image keys, user ID | The (date, user_id) pair is unique. |
sentiment | Name, intensity level, reasons JSON, other text, story, image, journal ID | Multiple emotions can belong to one journal. |
plan_activity | Status, activity, type, time, ratings, reminder flag, journal ID | Soft deletes through deleted_at. |
survey_form | Type, question IDs/data, answers JSON, event memo, journal ID | Used for scenario and emotion-event exercises. |
survey_quiz | Answers JSON, total points, status, user ID | Stores 9Q submissions. |
safety_plan | Six JSON arrays and user ID | One plan per user. |
satisfaction | Feature type, comment, rating, rate type, user ID | Feedback records. |
notification_activity | Type, status, message, channel, due time, user and activity IDs | Drives quiz and planned-activity reminders. |
question | UUID, title, expression, correct choice, type, image | Reusable emotion and situation questions. |
Deletion behavior
The employee account-delete endpoint performs a transaction that explicitly deletes related activity notifications, planned activities, sentiments, survey forms, path history, LINE account, safety plan, path progress, journals, quizzes, feedback, notifications, and finally the user.
This is a hard delete for most records. Planned activities normally support soft deletion, but account deletion removes them directly as part of the employee-account lifecycle.
Redis
Core services and employee web sessions use Redis for different purposes.
| Pattern or use | Owner | Lifetime |
|---|---|---|
user:state:<LINE user ID> | Core | Default 15 minutes; supports multi-message onboarding and concern capture. |
Rate-limiter records with login_limit prefix | Core | 15-minute window and block after five email/password login attempts. |
sess:<random base64> | Employee web app | 30 days; stores Remix session data including access and refresh JWTs and deep-link return state. |
The CMS does not use Redis in its current session implementation. It stores the session in a signed HTTP-only cookie with a one-day maximum age.
No EMmind MTL ElastiCache or MemoryDB resource was identified in the verified AWS account. Redis remains a required source-level dependency, but its live host and ownership were not established.
S3 user content
The core service generates five-minute presigned PUT URLs for the configured user-content bucket. Keys use the pattern media/<three digits>/<three digits>/<random 64 hex characters>.<extension>.
The employee application uses these URLs for profile and safety-plan images. The core sentiment service also renders and uploads journal emotion images. The API returns both the signed upload URL and the final S3 object URL/key.
AWS_STORAGE_SYSTEM_BUCKET is configured but has no current caller. User content uses AWS_STORAGE_USERCONTENT_BUCKET.
The EC2 instance roles establish these environment mappings:
| Environment | System/assets bucket | User-content bucket |
|---|---|---|
| Development | emmind-mtl-assets-dev | emmind-mtl-usercontent-dev |
| Production | emmind-mtl-assets | emmind-mtl-usercontent |
The role policies verify that the application hosts may use these buckets. They do not reveal which live environment variables are supplied to each container.
LINE integration
| API or channel | Direction | Use |
|---|---|---|
| LINE ID token verification | Core -> LINE | Confirms the LIFF token and reads LINE subject, name, and picture. |
| LINE deauthorization | Core -> LINE | Core exposes a deauthorization endpoint. The current employee logout route performs LIFF SDK logout without calling it. |
| Webhook callback | LINE -> POST /api/line/callback | Handles follow and text events. |
| Reply API | Core -> LINE | Responds within webhook conversations. |
| Push API | Core -> LINE | Sends onboarding and concern prompts, post-9Q follow-up, planned-activity and quiz reminders, sentiment reminders, and red-case follow-up. |
| LIFF deep link | LINE message -> web app | Opens quiz and quiz-help screens, learning paths, emotion journals, urgent support, and planned-activity summaries. |
Google account verification
The core service creates an OAuth authorization URL with the openid email profile scope and access_type=online. It exchanges the returned authorization code at Google's token endpoint, decodes the email field from the returned ID-token payload, and stores google_email plus google_verified_at.
GoogleAuthGuard checks whether the stored verification exists and is younger than the configured expiry. The default verification lifetime is 24 hours.
Authentication contracts
LINE employee tokens
The core signs RS256 access tokens for six hours and refresh tokens for 120 days. The payload identifies the LINE account with channel=line and its account UUID. LineAuthGuard then loads the current LINE account, including its joined user, by account UUID.
Employee endpoints generally require three guards:
- a valid access JWT;
- a matching current LINE account; and
- a non-expired Google verification.
Profile, consent, and Google exchange endpoints intentionally stop before the third guard so onboarding can finish.
CMS tokens
CMS email/password login uses bcrypt and a Redis rate limit. It receives the same six-hour access and 120-day refresh lifetimes. The CMS stores the tokens and /users/me result in a signed HTTP-only cookie session, checks the decoded access-token expiry, and relies on the core API to verify the JWT on API calls. The CMS admits the admin and staff roles to its data views.
Other integrations
| Integration | Use |
|---|---|
| DMIND prescreening | The LINE follow response links users who are not ready for EMmind to the DMIND participant prescreening site. |
| External feedback form | A LINE rich-message branch links to a Google Form. |
| Google Analytics | The employee web app loads GTAG_ID when configured. |
| Sentry | The employee web server exports Sentry's Remix error handler, while employee browser initialization is commented out. Core contains a debug-error route, but its instrumentation file is empty. CMS initializes browser Sentry only when VITE_NODE_ENV is production. |
Sensitive-data handling
The database contains employee identity, LINE identifiers, Google email, mental-health questionnaire answers, journal text, emotional states, safety-plan content, and support contacts. Treat exports and logs as sensitive health and employment-linked data.
Do not place record values, tokens, signed S3 URLs, secret files, or screenshots containing user data in this library. Use synthetic fixtures for troubleshooting documentation.