Data model
ER Diagram
Schema Explanations
documents
Table
id (uuid, PK, default gen_random_uuid())
Unique identifier for each document.
filename (varchar(255), NOT NULL)
Original filename of the uploaded document.
file_type (varchar(10), NOT NULL, CHECK in ['pdf','docx'])
File extension/type (must be either pdf or docx).
upload_timestamp (timestamp, default now())
Timestamp when the file was uploaded.
title (text)
Parsed title from the document’s front matter.
document_type (varchar(100))
Type of document (e.g., “Diplomsko delo,” “Magistrsko delo”).
student_name (varchar(255))
Name of the student (extracted from the document).
study_program (text)
Program of study (extracted from the document).
study_direction (varchar(255))
Study direction or field (extracted from the document).
mentor (varchar(255))
Name of the primary mentor.
co_mentor (varchar(255))
Name of the co-mentor (if any).
lecturer (varchar(255))
Name of the reviewer/editor (lektor).
overall_score (integer)
Computed structure suitability score (0–100).
total_sections (integer)
Total number of required sections (front matter + body).
found_sections (integer)
Count of sections actually found in the document.
missing_critical_count (integer)
Number of missing “critical” sections (e.g., abstract, introduction, conclusion).
uvod_quality (integer)
Quality metric for the “Uvod” section (e.g., number of subsections found).
front_matter_analysis (jsonb)
JSON object mapping each front‐matter element → true/false.
body_sections_analysis (jsonb)
JSON object mapping each body section (e.g., “Uvod,” “Metodologija”) → true/false.
missing_sections (text[])
Array of front‐matter section names that were not found.
missing_body_sections (text[])
Array of body section names that were not found.
recommendations (text[])
List of suggested improvements based on analysis.
table_of_contents (jsonb)
Parsed table of contents entries (number, title, level).
uvod_content (text[])
Array of all paragraphs under the “Uvod” section.
created_at (timestamp, default now())
When the record was created.
updated_at (timestamp, default now())
When the record was last updated.
document_paragraphs
Table
id (uuid, PK, default gen_random_uuid())
Unique identifier for each paragraph record.
document_id (uuid, NOT NULL, FK → documents.id)
References the parent document. On delete cascade ensures paragraphs are removed if the document is deleted.
paragraph_order (integer, NOT NULL)
The zero‐based index/order of this paragraph within the document.
paragraph_id (varchar(255), NOT NULL)
A UUID string (from the document parser) uniquely identifying this paragraph.
paragraph_style (varchar(100))
Style name (e.g., “Heading 1,” “Normal”) from Word/PDF conversion.
content (text, NOT NULL)
The actual paragraph text.
created_at (timestamp, default now())
When the paragraph record was created.
Indexes:
Index on (document_id) for quickly finding all paragraphs of a document.
Composite index on (document_id, paragraph_order) to preserve ordering.
profiles
Table
id (uuid, PK, FK → auth.users.id)
Matches the Supabase Auth user ID. On delete cascade removes profile if the Auth user is deleted.
name (text)
User’s full name (optional).
email (text)
Email address (optional; may duplicate auth.users.email).
image (text)
URL to user’s avatar or profile picture (optional).
customer_id (text)
Identifier for a billing customer (if applicable).
price_id (text)
Identifier for a subscription or pricing plan (if applicable).
has_access (boolean, default false)
Indicates whether this user can upload and analyze documents.
created_at (timestamp, default now() AT TIME ZONE 'UTC')
When the profile was created.
updated_at (timestamp, default now() AT TIME ZONE 'UTC')
When the profile was last updated.
Triggers:
update_profiles_updated_at automatically sets updated_at on each update.
Last updated