docs/schema.dbml - master - tsps/core

docs/schema.dbml
146 строк4 KB
Василий Петров
Добавлено поле для summary в аудиозапись
02 май 2026, 22:31
02 май 2026, 22:312957903
// Основная таблица документов
Table document {
  id serial [pk]
  title varchar(500) [null]
  date varchar(10) [null]
  type_id integer [not null]
  case_entity_id integer [not null]
  judge_id integer [not null]
  court_id integer [not null]
  text text [null]
  text_extracted_at timestamp [null]
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]
  
  indexes {
    case_entity_id
    type_id
    judge_id
    court_id
    created_at
  }
}

Table document_file {
  id serial [pk]
  document_id integer [not null, unique]
  file_data bytea [not null]
  file_size integer [not null]
  mime_type varchar(100) [default: 'application/pdf']
  uploaded_at timestamp [default: `now()`]
  
  indexes {
    document_id
    file_size
  }
}

Table document_type {
  id serial [pk]
  name varchar(100) [not null, unique]
}

Table court {
  id serial [pk]
  name varchar(500) [not null, unique]
}

Table judge {
  id serial [pk]
  last_name varchar(100) [not null]
  first_name varchar(100) [not null]  
  middle_name varchar(100) [null]
  current_court_id integer [null]
  auto_load_enabled boolean [not null, default: false]
}

Table case_entity {
  id serial [pk]
  number varchar(100) [not null, unique]
}

Table admin {
  id serial [pk]
  email varchar(255) [not null, unique]
  first_name varchar(100) [not null]
  hashed_password varchar(255) [not null]
  last_name varchar(100) [not null]
  role varchar(50) [not null, default: 'superadmin']
  
  indexes {
    email
  }
}

Table document_import_job {
  id serial [pk]
  admin_id integer [not null]
  status varchar(50) [not null, note: "'created' | 'in_progress' | 'completed' | 'failed'"]
  search_params jsonb [not null]
  result_summary jsonb [null]
  error_message text [null]
  created_at timestamp with time zone [default: `now()`]
  updated_at timestamp with time zone [default: `now()`]

  indexes {
    admin_id
    status
    created_at
  }
}

Table judge_account {
  id serial [pk]
  judge_id integer [not null, note: 'Связь 1:M: у одного судьи может быть несколько аккаунтов']
  email varchar(255) [not null, unique]
  hashed_password varchar(255) [not null]
  is_active boolean [not null, default: true]

  indexes {
    judge_id
    email
  }
}

Table audio_recording {
  id serial [pk]
  case_entity_id integer [not null]
  judge_id integer [not null]
  court_id integer [not null]

  // Ссылка на S3
  s3_key varchar(500) [not null, unique]
  file_name varchar(255) [not null]
  mime_type varchar(50) [not null, default: 'audio/mpeg']
  file_size integer [not null]
  duration_seconds integer [null]

  // Расшифровка
  transcription text [null]
  transcript_summary text [null]
  transcription_status varchar(20) [not null, default: 'created', note: "'created' | 'in_progress' | 'completed' | 'failed'"]
  transcribed_at timestamp [null]
  error_message text [null]

  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]

  indexes {
    case_entity_id
    court_id
    judge_id
    transcription_status
  }
}

Ref: document.type_id > document_type.id
Ref: document.case_entity_id > case_entity.id
Ref: document.judge_id > judge.id
Ref: document.court_id > court.id
Ref: document_file.document_id > document.id [delete: cascade]
Ref: judge.current_court_id > court.id
Ref: document_import_job.admin_id > admin.id
Ref: judge_account.judge_id > judge.id [delete: cascade]
Ref: audio_recording.case_entity_id > case_entity.id
Ref: audio_recording.court_id > court.id
Ref: audio_recording.judge_id > judge.id