= Relational Design =

== Transformation Method ==

The relational schema below was derived from the P1 Entity-Relationship model using the '''partial transformation''' method. Each entity set became a relation; each 1:N relationship was represented by placing a foreign key on the "many" side; the single M:N relationship (Task ↔ User) was represented by a separate associative relation (task_workers). This method was chosen because the model is dominated by 1:N relationships with few optional participations, making partial transformation the most direct and readable mapping.

== Relational Schema ==

Primary keys are __underlined__; foreign keys are marked ''(FK)''.

 * '''users'''(__id__, email, password, role, name, created_at, company, logo, color_hex, address, postal_address, phone_number, extra_emails, brand_pattern, short_info, nickname, avatar_emoji, skills, profile_status, invite_token, invite_expires, referred_by_id ''(FK→users)'', company_owner_id ''(FK→users)'')
 * '''projects'''(__id__, name, description, client_id ''(FK→users)'', status, service_type, metadata, start_date, end_date, created_at, updated_at)
 * '''domains'''(__id__, client_id ''(FK→users)'', domain_name, is_primary, is_active, notes, activation_date, expiration_date, lifespan_years, hosting_provider, hosting_plan, hosting_expiry, status, activation_email_sent_at, renewal_reminder_sent_at, created_at, updated_at)
 * '''tasks'''(__id__, title, description, status, due_date, created_at, updated_at, client_id ''(FK→users)'', project_id ''(FK→projects)'')
 * '''milestones'''(__id__, task_id ''(FK→tasks)'', title, description, image_url, image_urls, pushed_to_github, deployed, is_done, done_at, done_by, created_by_id, order, created_at)
 * '''task_workers'''(__id__, task_id ''(FK→tasks)'', user_id ''(FK→users)'') — unique(task_id, user_id)
 * '''task_files'''(__id__, task_id ''(FK→tasks)'', file_name, file_url, file_type, caption, section, is_completed, completed_at, completed_by, uploaded_at, uploaded_by, version, review_status, review_comment, visible_to_client)
 * '''file_comments'''(__id__, file_id ''(FK→task_files)'', user_id ''(FK→users)'', content, visible_to_client, created_at)
 * '''task_comments'''(__id__, task_id ''(FK→tasks)'', user_id ''(FK→users)'', content, visible_to_client, created_at)
 * '''invoices'''(__id__, invoice_number, amount, due_date, status, description, paid_at, created_at, file_url, payment_link, client_id ''(FK→users)'', issue_date, payment_terms, notes, subtotal, tax_rate, tax_amount)
 * '''payments'''(__id__, invoice_id ''(FK→invoices)'', amount, paid_at, note, created_at)
 * '''invoice_line_items'''(__id__, invoice_id ''(FK→invoices)'', name, description, quantity, unit_price, sort_order)
 * '''timesheet_projects'''(__id__, project_name, client_id ''(FK→users)'', description, is_paid, paid_at, created_at, updated_at)
 * '''timesheet_entries'''(__id__, project_id ''(FK→timesheet_projects)'', date, hours_worked, hourly_rate, total_pay, notes, created_at, updated_at)
 * '''notifications'''(__id__, user_id ''(FK→users)'', type, title, message, read, link, source_role, task_id, thread_type, created_at)
 * '''invites'''(__id__, email, role, token_hash, name, company, company_owner_id, domain_name, domain_expiry, hosting_plan, hosting_expiry, invited_by_id ''(FK→users)'', used, cancelled, used_at, created_at, expires_at)
 * '''notification_preferences'''(__id__, user_id ''(FK→users)'', event_type, in_app_enabled, email_enabled) — unique(user_id, event_type)

== DDL Script (schema creation) ==

The script creates the `project` schema, all 17 tables, the file_review_status enum type, all primary keys, foreign keys, unique constraints, and indexes. It is fully re-runnable — it drops and recreates the schema at the top, so it works both on an empty database and on one where the objects already exist.

Attached: [attachment:schema_creation.sql]

== DML Script (data load) ==

The script loads realistic, logically-connected sample data into all tables. It is re-runnable — it truncates all tables (with RESTART IDENTITY CASCADE) before inserting, and re-syncs the identity sequences at the end, so it works both on empty tables and on tables that already contain data.

Attached: [attachment:data_load.sql]

== Relational Diagram ==

The relational schema diagram below was generated in DBeaver from the live `project` schema, using Crow's Foot notation.

[[Image(relational_schema.jpg, width=100%)]]

Attached: [attachment:relational_schema.jpg]