Database

The full tc-forensic SQL: 14 tc_forensic_* tables.

#Installation

Import the file once into the database used by oxmysql. It is idempotent. Tables are also created automatically when the resource starts.

Download install.sql

#Tables

Table	Content
`tc_forensic_profiles`	DNA database: DNA / fingerprint codes per character
`tc_forensic_photos`	Camera photos
`tc_forensic_folders`	Photo folders
`tc_forensic_analyses`	Laboratory analyses
`tc_forensic_cases`	Investigation cases
`tc_forensic_case_people`	People linked to a case
`tc_forensic_case_vehicles`	Vehicles linked to a case
`tc_forensic_case_items`	Evidence attached to a case
`tc_forensic_case_notes`	Notes of a case
`tc_forensic_case_links`	Investigation board links
`tc_forensic_case_officers`	Officers assigned to a case
`tc_forensic_settings`	In-game settings (e.g. laboratories)
`tc_forensic_evidence`	Internal evidence (outside Vision)
`tc_forensic_custody`	Chain of custody (outside Vision)

On Vision, evidence stays in the core's evidence* tables; tc_forensic_evidence and tc_forensic_custody stay empty.

#Full SQL

Show install.sql (187 lines)

install.sqlDownload

-- ============================================================================
-- tc-forensic : toutes les tables du script (idempotent, utf8mb4).
-- Les tables sont aussi créées automatiquement au démarrage de la ressource.
-- Base Vision existante (anciennes tables ofr_*) : exécuter migrate_from_oria.sql AVANT le premier démarrage.
-- ============================================================================

CREATE TABLE IF NOT EXISTS tc_forensic_profiles (
    identifier   VARCHAR(64)  NOT NULL PRIMARY KEY,
    name         VARCHAR(96)  NOT NULL,
    dob          VARCHAR(32)  DEFAULT NULL,
    sex          VARCHAR(8)   DEFAULT NULL,
    dna_hash     VARCHAR(24)  DEFAULT NULL,
    fp_hash      VARCHAR(24)  DEFAULT NULL,
    dna_at       INT          DEFAULT NULL,
    fp_at        INT          DEFAULT NULL,
    sampled_by   VARCHAR(96)  DEFAULT NULL,
    notes        TEXT         DEFAULT NULL,
    created_at   INT          NOT NULL,
    INDEX idx_tcf_dna (dna_hash),
    INDEX idx_tcf_fp (fp_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tc_forensic_photos (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    data         MEDIUMTEXT   NOT NULL,
    thumb        MEDIUMTEXT   DEFAULT NULL,
    icon         TEXT         DEFAULT NULL,
    caption     VARCHAR(160) DEFAULT NULL,
    author_ident VARCHAR(64)  DEFAULT NULL,
    author_name  VARCHAR(96)  DEFAULT NULL,
    x FLOAT DEFAULT NULL, y FLOAT DEFAULT NULL, z FLOAT DEFAULT NULL,
    street       VARCHAR(96)  DEFAULT NULL,
    traces       INT          NOT NULL DEFAULT 0,
    created_at   INT          NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tc_forensic_folders (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    label        VARCHAR(64)  NOT NULL,
    photos       TEXT         NOT NULL,
    created_by   VARCHAR(96)  DEFAULT NULL,
    created_at   INT          NOT NULL,
    updated_at   INT          NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tc_forensic_analyses (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    kind           VARCHAR(16)  NOT NULL,
    source_kind    VARCHAR(16)  NOT NULL,
    source_label   VARCHAR(128) DEFAULT NULL,
    evidence_id    INT          DEFAULT NULL,
    payload        TEXT         DEFAULT NULL,
    result         MEDIUMTEXT   DEFAULT NULL,
    status         VARCHAR(12)  NOT NULL DEFAULT 'pending',
    requester_ident VARCHAR(64) DEFAULT NULL,
    requester_name VARCHAR(96)  DEFAULT NULL,
    job            VARCHAR(32)  DEFAULT NULL,
    case_id        INT          DEFAULT NULL,
    created_at     INT          NOT NULL,
    ready_at       INT          NOT NULL,
    INDEX idx_tcf_an_status (status),
    INDEX idx_tcf_an_case (case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tc_forensic_cases (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    title        VARCHAR(120) NOT NULL,
    category     VARCHAR(48)  DEFAULT NULL,
    status       VARCHAR(16)  NOT NULL DEFAULT 'open',
    priority     TINYINT      NOT NULL DEFAULT 2,
    summary      TEXT         DEFAULT NULL,
    location     VARCHAR(120) DEFAULT NULL,
    x FLOAT DEFAULT NULL, y FLOAT DEFAULT NULL, z FLOAT DEFAULT NULL,
    lead_ident   VARCHAR(64)  DEFAULT NULL,
    lead_name    VARCHAR(96)  DEFAULT NULL,
    job          VARCHAR(32)  DEFAULT NULL,
    created_at   INT          NOT NULL,
    updated_at   INT          NOT NULL,
    INDEX idx_tcf_case_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tc_forensic_case_people (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    case_id      INT          NOT NULL,
    identifier   VARCHAR(64)  NOT NULL,
    name         VARCHAR(96)  NOT NULL,
    role         VARCHAR(32)  NOT NULL,
    notes        VARCHAR(500) DEFAULT NULL,
    added_by     VARCHAR(96)  DEFAULT NULL,
    created_at   INT          NOT NULL,
    UNIQUE KEY uniq_tcf_cp (case_id, identifier),
    INDEX idx_tcf_cp_ident (identifier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tc_forensic_case_vehicles (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    case_id      INT          NOT NULL,
    plate        VARCHAR(12)  NOT NULL,
    model        VARCHAR(48)  DEFAULT NULL,
    notes        VARCHAR(500) DEFAULT NULL,
    added_by     VARCHAR(96)  DEFAULT NULL,
    created_at   INT          NOT NULL,
    UNIQUE KEY uniq_tcf_cv (case_id, plate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tc_forensic_case_items (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    case_id      INT          NOT NULL,
    kind         VARCHAR(16)  NOT NULL,
    ref_id       INT          NOT NULL,
    label        VARCHAR(128) DEFAULT NULL,
    notes        VARCHAR(500) DEFAULT NULL,
    added_by     VARCHAR(96)  DEFAULT NULL,
    created_at   INT          NOT NULL,
    UNIQUE KEY uniq_tcf_ci (case_id, kind, ref_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tc_forensic_case_notes (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    case_id      INT          NOT NULL,
    kind         VARCHAR(12)  NOT NULL DEFAULT 'note',
    body         TEXT         NOT NULL,
    author_ident VARCHAR(64)  DEFAULT NULL,
    author_name  VARCHAR(96)  DEFAULT NULL,
    created_at   INT          NOT NULL,
    INDEX idx_tcf_cn_case (case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tc_forensic_case_links (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    case_id      INT          NOT NULL,
    a_kind       VARCHAR(16)  NOT NULL,
    a_ref        VARCHAR(64)  NOT NULL,
    b_kind       VARCHAR(16)  NOT NULL,
    b_ref        VARCHAR(64)  NOT NULL,
    label        VARCHAR(64)  DEFAULT NULL,
    created_at   INT          NOT NULL,
    INDEX idx_tcf_cl_case (case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tc_forensic_case_officers (
    case_id      INT          NOT NULL,
    identifier   VARCHAR(64)  NOT NULL,
    name         VARCHAR(96)  NOT NULL,
    PRIMARY KEY (case_id, identifier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tc_forensic_settings (
    name         VARCHAR(32)  NOT NULL PRIMARY KEY,
    data         LONGTEXT     NOT NULL,
    updated_by   VARCHAR(96)  DEFAULT NULL,
    updated_at   INT          NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- Preuves internes (utilisées quand le core Vision n'est pas là : ESX, QBCore, Qbox, standalone).
-- Sur Vision, les preuves restent dans les tables `evidence*` du core et ces deux tables restent vides.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tc_forensic_evidence (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    scene_id          VARCHAR(64)  DEFAULT NULL,
    type              VARCHAR(32)  NOT NULL,
    coords_x          DOUBLE       NOT NULL DEFAULT 0,
    coords_y          DOUBLE       NOT NULL DEFAULT 0,
    coords_z          DOUBLE       NOT NULL DEFAULT 0,
    metadata          LONGTEXT     DEFAULT NULL,
    status            VARCHAR(16)  NOT NULL DEFAULT 'on_scene',
    collected_by      VARCHAR(64)  DEFAULT NULL,
    collected_by_name VARCHAR(96)  DEFAULT NULL,
    created_at        DATETIME     NOT NULL,
    collected_at      DATETIME     DEFAULT NULL,
    expires_at        DATETIME     DEFAULT NULL,
    INDEX idx_tcf_ev_scene (status, type, id),
    INDEX idx_tcf_ev_coords (coords_x, coords_y)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tc_forensic_custody (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    evidence_id  INT          NOT NULL,
    action       VARCHAR(32)  NOT NULL,
    actor_id     VARCHAR(64)  DEFAULT NULL,
    actor_name   VARCHAR(96)  DEFAULT NULL,
    notes        TEXT         DEFAULT NULL,
    created_at   DATETIME     NOT NULL,
    INDEX idx_tcf_cu_ev (evidence_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Photos

Photos are stored in the database (MEDIUMTEXT). Keep an eye on the size of tc_forensic_photos on a very active server; Config.Camera.quality and width control each photo's weight.