Create a simplified prisma schema for Postgres database with only the tables necessary to store measurements

[mikepsinn]

We may want to update the Prisma schema of the NextJS app with the tables necessary to store measurements, variables, units, reminders, etc. instead of solely depending on the API to store this data.

Schema to Update

Here's the NextJS Prisma Postgres schema to update
https://github.com/FDA-AI/FDAi/blob/eb480b9e50318efeebd30bff6a81b984adace994/apps/nextjs/prisma/schema.prisma

Reference Table Definitions

For reference to the tables we need to add, there's a MySQL Prisma Schema and some SQL files

1. MySQL Prisma with the measurements table and relations
   

   FDAi/apps/api-gateway/prisma/schema.prisma

   Lines 1396 to 1449 in d441d67

   	model measurements {
   	id BigInt @id @default(autoincrement())
   	user_id BigInt @db.UnsignedBigInt
   	client_id String @db.VarChar(80)
   	connector_id Int? @db.UnsignedInt
   	variable_id Int @db.UnsignedInt
   	start_time Int @db.UnsignedInt
   	value Float
   	unit_id Int @db.UnsignedSmallInt
   	original_value Float
   	original_unit_id Int @db.UnsignedSmallInt
   	duration Int?
   	note String? @db.Text
   	latitude Float?
   	longitude Float?
   	location String? @db.VarChar(255)
   	created_at DateTime @default(now()) @db.Timestamp(0)
   	updated_at DateTime @default(now()) @db.Timestamp(0)
   	error String? @db.Text
   	variable_category_id Int @db.UnsignedTinyInt
   	deleted_at DateTime? @db.DateTime(0)
   	source_name String? @db.VarChar(80)
   	user_variable_id Int @db.UnsignedInt
   	start_at DateTime @db.Timestamp(0)
   	connection_id Int? @db.UnsignedInt
   	connector_import_id Int? @db.UnsignedInt
   	deletion_reason String? @db.VarChar(280)
   	original_start_at DateTime @db.Timestamp(0)
   	oa_clients oa_clients @relation(fields: [client_id], references: [client_id], onUpdate: Restrict, map: "measurements_client_id_fk")
   	connections connections? @relation(fields: [connection_id], references: [id], onDelete: Restrict, onUpdate: Restrict, map: "measurements_connections_id_fk")
   	connector_imports connector_imports? @relation(fields: [connector_import_id], references: [id], onDelete: Restrict, onUpdate: Restrict, map: "measurements_connector_imports_id_fk")
   	connectors connectors? @relation(fields: [connector_id], references: [id], onDelete: Restrict, onUpdate: Restrict, map: "measurements_connectors_id_fk")
   	units_measurements_original_unit_idTounits units @relation("measurements_original_unit_idTounits", fields: [original_unit_id], references: [id], onUpdate: Restrict, map: "measurements_original_unit_id_fk")
   	units_measurements_unit_idTounits units @relation("measurements_unit_idTounits", fields: [unit_id], references: [id], onUpdate: Restrict, map: "measurements_unit_id_fk")
   	wp_users wp_users @relation(fields: [user_id], references: [ID], onUpdate: Restrict, map: "measurements_user_id_fk")
   	user_variables user_variables @relation(fields: [user_variable_id], references: [id], onUpdate: Restrict, map: "measurements_user_variables_user_variable_id_fk")
   	variable_categories variable_categories @relation(fields: [variable_category_id], references: [id], onUpdate: Restrict, map: "measurements_variable_category_id_fk")
   	variables variables @relation(fields: [variable_id], references: [id], onUpdate: Restrict, map: "measurements_variables_id_fk")
   	@@unique([user_id, variable_id, start_time], map: "measurements_pk")
   	@@index([client_id], map: "measurements_client_id_fk")
   	@@index([connection_id], map: "measurements_connections_id_fk")
   	@@index([connector_import_id], map: "measurements_connector_imports_id_fk")
   	@@index([connector_id], map: "measurements_connectors_id_fk")
   	@@index([original_unit_id], map: "measurements_original_unit_id_fk")
   	@@index([start_time], map: "measurements_start_time_index")
   	@@index([unit_id], map: "measurements_unit_id_fk")
   	@@index([user_id, variable_category_id, start_time], map: "measurements_user_id_variable_category_id_start_time_index")
   	@@index([user_variable_id], map: "measurements_user_variables_user_variable_id_fk")
   	@@index([variable_id, user_id], map: "measurements_user_variables_variable_id_user_id_fk")
   	@@index([variable_category_id], map: "measurements_variable_category_id_fk")
   	@@index([variable_id, start_time], map: "measurements_variable_id_start_time_index")
   	@@index([variable_id, value, start_time], map: "measurements_variable_id_value_start_time_index")
   	}

The problem with that schema is that there are a lot of tables that aren't absolutely necessary and complexity to the project

2. This folder contains the SQL files and documentation
   

   FDAi/apps/dfda-1/database/ddl/quantimodo_test/measurements.sql

   Lines 3 to 55 in 6055dce

   	id bigint auto_increment
   	primary key,
   	user_id bigint unsigned not null,
   	client_id varchar(80) null,
   	connector_id int unsigned null comment 'The id for the connector data source from which the measurement was obtained',
   	variable_id int unsigned not null comment 'ID of the variable for which we are creating the measurement records',
   	start_time int unsigned not null comment 'Start time for the measurement event in ISO 8601',
   	value double not null comment 'The value of the measurement after conversion to the default unit for that variable',
   	unit_id smallint unsigned not null comment 'The default unit for the variable',
   	original_value double not null comment 'Value of measurement as originally posted (before conversion to default unit)',
   	original_unit_id smallint unsigned not null comment 'Unit id of measurement as originally submitted',
   	duration int null comment 'Duration of the event being measurement in seconds',
   	note text null comment 'An optional note the user may include with their measurement',
   	latitude double null comment 'Latitude at which the measurement was taken',
   	longitude double null comment 'Longitude at which the measurement was taken',
   	location varchar(255) null comment 'location',
   	created_at timestamp default '0000-00-00 00:00:00' not null comment 'Time at which this measurement was originally stored',
   	updated_at timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment 'Time at which this measurement was last updated',
   	error text null comment 'An error message if there is a problem with the measurement',
   	variable_category_id tinyint unsigned not null comment 'Variable category ID',
   	deleted_at datetime null,
   	source_name varchar(80) null comment 'Name of the application or device',
   	user_variable_id int unsigned not null,
   	start_at timestamp default '0000-00-00 00:00:00' not null,
   	connection_id int unsigned null,
   	connector_import_id int unsigned null,
   	deletion_reason varchar(280) null comment 'The reason the variable was deleted.',
   	original_start_at timestamp default '0000-00-00 00:00:00' not null,
   	constraint measurements_pk
   	unique (user_id, variable_id, start_time),
   	constraint measurements_client_id_fk
   	foreign key (client_id) references quantimodo_test.oa_clients (client_id),
   	constraint measurements_connections_id_fk
   	foreign key (connection_id) references quantimodo_test.connections (id),
   	constraint measurements_connector_imports_id_fk
   	foreign key (connector_import_id) references quantimodo_test.connector_imports (id),
   	constraint measurements_connectors_id_fk
   	foreign key (connector_id) references quantimodo_test.connectors (id),
   	constraint measurements_original_unit_id_fk
   	foreign key (original_unit_id) references quantimodo_test.units (id),
   	constraint measurements_unit_id_fk
   	foreign key (unit_id) references quantimodo_test.units (id),
   	constraint measurements_user_id_fk
   	foreign key (user_id) references quantimodo_test.wp_users (ID),
   	constraint measurements_user_variables_user_variable_id_fk
   	foreign key (user_variable_id) references quantimodo_test.user_variables (id),
   	constraint measurements_variable_category_id_fk
   	foreign key (variable_category_id) references quantimodo_test.variable_categories (id),
   	constraint measurements_variables_id_fk
   	foreign key (variable_id) references quantimodo_test.variables (id)
   	)
   	comment 'Measurements are any value that can be recorded like daily steps, a mood rating, or apples eaten.'
   	charset = utf8mb3;

Comments

It would probably be nice to have comments from those SQL files in the Prisma schema
https://www.prisma.io/docs/orm/prisma-schema/overview

Approach

You can probably paste the existing necessary tables from the SQL files into Claude and have it convert the table and its comments to a Postgres Prisma-compatible version and then paste it in
https://github.com/FDA-AI/FDAi/blob/eb480b9e50318efeebd30bff6a81b984adace994/apps/nextjs/prisma/schema.prisma