| 19 | | * '''task_comments'''(__id__, task_id ''(FK→tasks)'', user_id ''(FK→users)'', |
| | 19 | * '''task_comments'''(__id__, task_id ''(FK→tasks)'', user_id ''(FK→users)'', content, visible_to_client, created_at) |
| | 20 | * '''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) |
| | 21 | * '''payments'''(__id__, invoice_id ''(FK→invoices)'', amount, paid_at, note, created_at) |
| | 22 | * '''invoice_line_items'''(__id__, invoice_id ''(FK→invoices)'', name, description, quantity, unit_price, sort_order) |
| | 23 | * '''timesheet_projects'''(__id__, project_name, client_id ''(FK→users)'', description, is_paid, paid_at, created_at, updated_at) |
| | 24 | * '''timesheet_entries'''(__id__, project_id ''(FK→timesheet_projects)'', date, hours_worked, hourly_rate, total_pay, notes, created_at, updated_at) |
| | 25 | * '''notifications'''(__id__, user_id ''(FK→users)'', type, title, message, read, link, source_role, task_id, thread_type, created_at) |
| | 26 | * '''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) |
| | 27 | * '''notification_preferences'''(__id__, user_id ''(FK→users)'', event_type, in_app_enabled, email_enabled) — unique(user_id, event_type) |
| | 28 | |
| | 29 | == DDL Script (schema creation) == |
| | 30 | |
| | 31 | 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. |
| | 32 | |
| | 33 | Attached: [attachment:schema_creation.sql] |
| | 34 | |
| | 35 | == DML Script (data load) == |
| | 36 | |
| | 37 | 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. |
| | 38 | |
| | 39 | Attached: [attachment:data_load.sql] |
| | 40 | |
| | 41 | == Relational Diagram == |
| | 42 | |
| | 43 | The relational schema diagram below was generated in DBeaver from the live `project` schema, using Crow's Foot notation. |
| | 44 | |
| | 45 | [[Image(relational_schema.jpg, width=100%)]] |
| | 46 | |
| | 47 | Attached: [attachment:relational_schema.jpg] |
