SQL Lite GORM Schema - RodrigoMattosoSilveira/EnterpriseRemoteSystems GitHub Wiki

7. SQLite / GORM Schema Proposal

7.1 Tables

  1. users
  2. roles
  3. user_roles
  4. people
  5. collaborator_journeys
  6. reference_data
  7. work_periods
  8. work_plan_items
  9. mine_well_productions
  10. gold_prices
  11. price_list_items
  12. expense_transactions
  13. expense_items
  14. earning_accrual_batches
  15. earning_accrual_items
  16. current_account_entries
  17. audit_logs
  18. attachments (optional future)

7.2 Example table definitions

people

  • id TEXT PK
  • name TEXT NOT NULL UNIQUE
  • address TEXT
  • phone TEXT UNIQUE
  • email TEXT UNIQUE
  • cpf TEXT UNIQUE
  • bank_data TEXT
  • pix_key TEXT UNIQUE
  • emergency_contact_name TEXT
  • emergency_contact_phone TEXT
  • emergency_contact_notes TEXT
  • status_id TEXT NOT NULL
  • created_at DATETIME NOT NULL
  • updated_at DATETIME NOT NULL

collaborator_journeys

  • id TEXT PK
  • person_id TEXT NOT NULL FK people(id)
  • journey_start_date DATE NOT NULL
  • default_end_date DATE NOT NULL
  • extension_days INTEGER NOT NULL DEFAULT 0
  • projected_end_date DATE NOT NULL
  • payment_method_id TEXT NOT NULL FK reference_data(id)
  • payment_value NUMERIC NOT NULL
  • sector_id TEXT NOT NULL FK reference_data(id)
  • location_id TEXT NOT NULL FK reference_data(id)
  • task_id TEXT NOT NULL FK reference_data(id)
  • status_id TEXT NOT NULL FK reference_data(id)
  • notes TEXT
  • closed_at DATETIME NULL
  • created_at DATETIME NOT NULL
  • updated_at DATETIME NOT NULL

work_periods

  • id TEXT PK
  • work_date DATE NOT NULL
  • period_code TEXT NOT NULL
  • starts_at DATETIME NULL
  • ends_at DATETIME NULL
  • status TEXT NOT NULL
  • created_at DATETIME NOT NULL
  • updated_at DATETIME NOT NULL
  • UNIQUE(work_date, period_code)

work_plan_items

  • id TEXT PK
  • work_period_id TEXT NOT NULL FK work_periods(id)
  • collaborator_id TEXT NOT NULL FK collaborator_journeys(id)
  • include_flag BOOLEAN NOT NULL DEFAULT true
  • sector_id TEXT NOT NULL
  • location_id TEXT NOT NULL
  • task_id TEXT NOT NULL
  • method_id TEXT NOT NULL
  • payment_value_snapshot NUMERIC NOT NULL
  • assignment_status TEXT NOT NULL
  • substituted_for_collaborator_id TEXT NULL
  • exception_type TEXT NULL
  • comments TEXT
  • created_at DATETIME NOT NULL
  • updated_at DATETIME NOT NULL

mine_well_productions

  • id TEXT PK
  • work_date DATE NOT NULL
  • period_code TEXT NOT NULL
  • location_id TEXT NOT NULL
  • grams_produced NUMERIC NOT NULL
  • comments TEXT
  • created_by TEXT NOT NULL
  • created_at DATETIME NOT NULL
  • updated_at DATETIME NOT NULL
  • UNIQUE(work_date, period_code, location_id)

gold_prices

  • id TEXT PK
  • quote_date DATE NOT NULL UNIQUE
  • quoted_at_time TEXT
  • price_brl_per_gram NUMERIC NOT NULL
  • source_name TEXT
  • comments TEXT
  • created_by TEXT NOT NULL
  • created_at DATETIME NOT NULL
  • updated_at DATETIME NOT NULL

price_list_items

  • id TEXT PK
  • code TEXT UNIQUE
  • name TEXT NOT NULL UNIQUE
  • description TEXT
  • category TEXT NOT NULL
  • price_brl NUMERIC NOT NULL
  • active BOOLEAN NOT NULL DEFAULT true
  • created_at DATETIME NOT NULL
  • updated_at DATETIME NOT NULL

expense_transactions

  • id TEXT PK
  • collaborator_id TEXT NOT NULL FK collaborator_journeys(id)
  • expense_date DATE NOT NULL
  • currency_code TEXT NOT NULL
  • total_amount NUMERIC NOT NULL
  • category TEXT NOT NULL
  • collaborator_agreement BOOLEAN NOT NULL DEFAULT false
  • comments TEXT
  • created_by TEXT NOT NULL
  • created_at DATETIME NOT NULL
  • updated_at DATETIME NOT NULL

expense_items

  • id TEXT PK
  • expense_id TEXT NOT NULL FK expense_transactions(id)
  • price_list_item_id TEXT NULL FK price_list_items(id)
  • item_name_snapshot TEXT NOT NULL
  • quantity NUMERIC NOT NULL
  • unit_price NUMERIC NOT NULL
  • total_price NUMERIC NOT NULL
  • comments TEXT
  • created_at DATETIME NOT NULL

earning_accrual_batches

  • id TEXT PK
  • work_period_id TEXT NOT NULL FK work_periods(id)
  • accrual_status TEXT NOT NULL
  • comments TEXT
  • created_by TEXT NOT NULL
  • created_at DATETIME NOT NULL
  • updated_at DATETIME NOT NULL

earning_accrual_items

  • id TEXT PK
  • accrual_batch_id TEXT NOT NULL FK earning_accrual_batches(id)
  • collaborator_id TEXT NOT NULL FK collaborator_journeys(id)
  • method_id TEXT NOT NULL
  • currency_code TEXT NOT NULL
  • calculation_basis_json TEXT NOT NULL
  • gross_amount NUMERIC NOT NULL
  • transfer_amount NUMERIC NOT NULL DEFAULT 0
  • net_amount NUMERIC NOT NULL
  • ledger_entry_id TEXT NULL
  • status TEXT NOT NULL
  • comments TEXT
  • created_at DATETIME NOT NULL

current_account_entries

  • id TEXT PK
  • reverted_entry_id TEXT NULL FK current_account_entries(id)
  • collaborator_id TEXT NOT NULL FK collaborator_journeys(id)
  • entry_date DATE NOT NULL
  • source_type TEXT NOT NULL
  • source_id TEXT NULL
  • method_id TEXT NULL
  • currency_code TEXT NOT NULL
  • cd_flag TEXT NOT NULL
  • item_description TEXT NOT NULL
  • quantity NUMERIC NOT NULL DEFAULT 1
  • unit_price NUMERIC NOT NULL
  • total_price NUMERIC NOT NULL
  • comments TEXT
  • status TEXT NOT NULL DEFAULT 'active'
  • created_by TEXT NOT NULL
  • created_at DATETIME NOT NULL

audit_logs

  • id TEXT PK
  • actor_user_id TEXT NOT NULL
  • action TEXT NOT NULL
  • entity_type TEXT NOT NULL
  • entity_id TEXT NOT NULL
  • before_json TEXT
  • after_json TEXT
  • created_at DATETIME NOT NULL