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: 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: data_load.sql​

Relational Diagram

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

Attached: relational_schema.jpg​