SCRAP backend ERD

Tables (14)

companies

  • id — uuid — Primary key — NOT NULL
  • user_id — uuid — Foreign key → users.id — NOT NULL
  • company_name — varchar(255) — NOT NULL
  • contact_email — varchar(255) — NOT NULL
  • industry — varchar(255) — NOT NULL
  • main_products — json — NOT NULL
  • main_waste_types — json
  • site_name — varchar(255) — NOT NULL
  • site_address — text — NOT NULL
  • site_facilities — json — NOT NULL
  • profile_version — integer — NOT NULL
  • created_at — timestamp
  • updated_at — timestamp

company_profile_history

  • id — uuid — Primary key — NOT NULL
  • company_id — uuid — Foreign key → companies.id — NOT NULL
  • profile_version — integer — NOT NULL
  • changed_fields — json
  • created_at — timestamp

emission_site_stats

  • id — uuid — Primary key — NOT NULL
  • region — varchar(255)
  • waste_code — varchar(30)
  • latitude — float — NOT NULL
  • longitude — float — NOT NULL
  • site_count — integer
  • source — varchar(255)
  • as_of — date

facilities

  • id — uuid — Primary key — NOT NULL
  • name — varchar(255)
  • business_type — varchar(255)
  • permit_no — varchar(255)
  • address — text
  • latitude — float
  • longitude — float
  • materials — json
  • acceptance_conditions — json
  • source — varchar(255)
  • source_date — date
  • version — integer
  • import_row_id — varchar(255) — Foreign key → facility_import_rows.listing_id

facility_import_rows

  • listing_id — varchar(255) — Primary key — NOT NULL
  • source_row — integer
  • is_recycling — boolean
  • is_treatment — boolean
  • record — json

facility_waste_codes

  • waste_code — varchar(30) — Primary key — NOT NULL
  • facility_id — uuid — Foreign key → facilities.id — NOT NULL
  • handling — varchar(100)

facility_waste_r_types

  • r_type_code — varchar(30) — Primary key — NOT NULL
  • facility_id — uuid — Foreign key → facility_waste_codes.facility_id — NOT NULL
  • waste_code — varchar(30) — Foreign key → facility_waste_codes.waste_code — NOT NULL

qr_field_checks

  • id — uuid — Primary key — NOT NULL
  • waste_id — uuid — Foreign key → wastes.id — NOT NULL
  • route_step_id — uuid — Foreign key → route_steps.id
  • role — varchar(30)
  • actor_name — varchar(255)
  • checked_at — timestamp

refresh_tokens

  • id — uuid — Primary key — NOT NULL
  • user_id — uuid — Foreign key → users.id — NOT NULL
  • expires_at — timestamp — NOT NULL
  • revoked_at — timestamp

route_steps

  • id — uuid — Primary key — NOT NULL
  • waste_id — uuid — Foreign key → wastes.id — NOT NULL
  • seq — integer — NOT NULL
  • stage — varchar(100)
  • process — varchar(255)
  • output — varchar(255)
  • kind — varchar(50)
  • facility_id — uuid — Foreign key → facilities.id
  • facility_name — varchar(255)
  • r_type — json
  • method — varchar(255)
  • status — varchar(50)
  • planned_date — date
  • actual_date — date
  • manifest_no — varchar(255)

treatment_snapshots

  • kind — varchar(255) — Primary key — NOT NULL
  • checksum — varchar(255)
  • payload — json

users

  • id — uuid — Primary key — NOT NULL
  • provider — varchar(30) — NOT NULL
  • provider_subject — varchar(255) — NOT NULL
  • email — varchar(255)
  • name — varchar(255)
  • withdrawn_at — timestamp
  • deletion_scheduled_at — timestamp
  • created_at — timestamp
  • updated_at — timestamp

waste_completion_snapshots

  • waste_id — uuid — Foreign key → wastes.id — NOT NULL
  • company_name — varchar(255) — NOT NULL
  • site_name — varchar(255) — NOT NULL
  • site_address — text — NOT NULL
  • captured_at — timestamp — NOT NULL

wastes

  • id — uuid — Primary key — NOT NULL
  • company_id — uuid — Foreign key → companies.id — NOT NULL
  • name — varchar(255)
  • status — varchar(50)
  • qr_token_hash — varchar(255)
  • qr_issued_at — timestamp
  • qr_expires_at — timestamp
  • waste_code — varchar(30)
  • code_name — varchar(255)
  • category — varchar(255)
  • waste_type — varchar(50)
  • r_type — json
  • clause_refs — json
  • law_version_id — varchar(255)
  • classified_at — timestamp
  • quantity_value — float
  • quantity_unit — varchar(30)
  • completed_at — timestamp
  • share_token_hash — varchar(255)
  • share_expires_at — timestamp
  • created_at — timestamp
  • updated_at — timestamp