Ian Postlethwaite

-- JavaScript malware analysis tables
-- Engine: ReplacingMergeTree(analysis_date) — latest analysis wins on re-processing
--
-- File order:
--   1. redb_js_features
--   2. redb_js_suspicious_apis
--   3. redb_js_deobfuscation
--   4. code_text_content              (generic text-content table; JS today,
--                                      PowerShell / Python / email / extracted
--                                      PDF / Office text in the future)
--   5. redb_iocs source_type ALTER    (extends Enum8 with text_raw/text_normalized
--                                      so JS — and any future text-based pipeline —
--                                      can distinguish IOCs found in the raw vs
--                                      normalised surface)
--   6. redb_iocs ioc_type ALTER       (adds registry_key=42 so HKLM/HKCU/HKEY_*
--                                      keys are extracted alongside file paths)
--
-- Decoded strings from JS still go into the shared code_binja_strings_raw
-- table (same schema used by DecompileBinja and DecompileAPK). JS
-- string_encoding values: hex, unicode, charcode, base64, concat. Plain long
-- literals are not extracted here — they're already in code_text_content and
-- scraped by the IOC pipeline over text_raw/text_normalized.
-- string_offset is the line number in the source file.
--
-- redb_js_features.script_type values (file format / container, first match):
--   jse, wsf, hta, embedded_html, wscript, esm, node_module, standalone, unknown
-- redb_js_features.detected_environment values (runtime by API surface, first
-- match):
--   wscript, browser_extension, service_worker, deno, node, browser, unknown

-- 1. Core features & obfuscation metrics (1 row per sample)
CREATE TABLE IF NOT EXISTS redb_js_features (
    sha256 FixedString(64),
    line_count UInt32,
    char_count UInt64,
    text_entropy Float64,
    max_line_length UInt32,
    avg_line_length Float64,
    is_minified UInt8,
    is_likely_obfuscated UInt8,
    obfuscator_name LowCardinality(String),
    obfuscation_score UInt8,
    obfuscation_techniques Array(String),
    eval_count UInt32,
    function_constructor_count UInt32,
    settimeout_setinterval_count UInt32,
    document_write_count UInt32,
    innerhtml_count UInt32,
    unescape_count UInt32,
    fromcharcode_count UInt32,
    atob_count UInt32,
    decodeuri_count UInt32,
    total_function_count UInt32,
    total_variable_count UInt32,
    max_nesting_depth UInt16,
    avg_identifier_length Float64,
    hex_string_count UInt32,
    unicode_escape_count UInt32,
    long_string_count UInt32,
    base64_string_count UInt32,
    comment_ratio Float64,
    script_type LowCardinality(String),
    detected_environment LowCardinality(String),
    analysis_date DateTime64(3, 'UTC')
) ENGINE = ReplacingMergeTree(analysis_date)
ORDER BY sha256;

-- 2. Suspicious API calls (multi-row per sample)
--
-- `revealed_by_deobf` is 1 when the API only appears after the deobfuscation
-- pass (i.e. the call site is hidden in the raw artefact and surfaces only in
-- text_normalized). Useful for filtering "what did normalisation actually
-- buy us" without re-running the diff.
CREATE TABLE IF NOT EXISTS redb_js_suspicious_apis (
    sha256 FixedString(64),
    api_name String,
    api_category LowCardinality(String),
    call_count UInt32,
    line_numbers Array(UInt32),
    context_snippet String,
    revealed_by_deobf UInt8,
    analysis_date DateTime64(3, 'UTC')
) ENGINE = ReplacingMergeTree(analysis_date)
ORDER BY (sha256, api_name);

-- 3. Deobfuscation results (1 row per sample)
CREATE TABLE IF NOT EXISTS redb_js_deobfuscation (
    sha256 FixedString(64),
    deobfuscator_used LowCardinality(String),
    deobfuscation_successful UInt8,
    original_size UInt64,
    deobfuscated_size UInt64,
    size_change_ratio Float64,
    original_entropy Float64,
    deobfuscated_entropy Float64,
    new_strings_found UInt32,
    new_apis_found UInt32,
    deobfuscated_sha256 FixedString(64),
    analysis_date DateTime64(3, 'UTC')
) ENGINE = ReplacingMergeTree(analysis_date)
ORDER BY sha256;

-- 4. Generic text-content table for any text-based artefact (JS today;
--    PowerShell, Python, plain text, email bodies, extracted PDF/Office text
--    in the future). One row per sha256. content_type carries the magika
--    label so callers can filter without joining other tables.
CREATE TABLE IF NOT EXISTS code_text_content (
    sha256 FixedString(64),
    content_type LowCardinality(String),
    text_raw String CODEC(ZSTD(3)),
    text_normalized Nullable(String) CODEC(ZSTD(3)),
    normalizer_used Nullable(String),
    analysis_date DateTime64(3, 'UTC')
) ENGINE = ReplacingMergeTree(analysis_date)
ORDER BY sha256;

-- 5. Extend redb_iocs.source_type Enum8 with two universal text-content
--    surfaces: text_raw (the artefact's original text) and text_normalized
--    (a deobfuscated/canonicalised form). Used by the JS IOC extraction
--    pipeline today; any future text-based pipeline (PowerShell, PDF, etc.)
--    plugs into the same two values.
--
-- Existing rows keep their stored integer values; only newly-inserted rows
-- can use 4/5. The MODIFY COLUMN must list the full final enum, including
-- the existing values (1/2/3) — ClickHouse rejects partial alters.
ALTER TABLE redb_iocs
    MODIFY COLUMN source_type
    Enum8('decompiled_function'=1, 'disassembled_function'=2, 'string'=3,
          'text_raw'=4, 'text_normalized'=5);

-- 6. Extend redb_iocs.ioc_type Enum8 with registry_key=42. Windows registry
--    paths (HKLM\..., HKCU\..., HKEY_LOCAL_MACHINE\...) are a distinct class
--    of IOC from filesystem paths and were previously extracted by nothing.
--    Same MODIFY COLUMN constraint as the source_type alter — the full final
--    enum must be listed.
ALTER TABLE redb_iocs
    MODIFY COLUMN ioc_type
    Enum8('ipv4'=1, 'ipv6'=2, 'fqdn'=3, 'url'=4, 'email'=5, 'server'=6,
          'hash_md5'=10, 'hash_sha1'=11, 'hash_sha256'=12,
          'cve'=20, 'cwe'=21, 'cpe'=22,
          'crypto_btc'=30, 'crypto_eth'=31, 'crypto_xrp'=32, 'crypto_bch'=33,
          'crypto_ada'=34, 'crypto_substrate'=35,
          'path_linux'=40, 'path_windows'=41, 'registry_key'=42,
          'onion'=50);

-- 7. Migrate redb_js_features to the two-tier obfuscation verdict.
--    `is_obfuscated` (binary heuristic at score >=40) is renamed to
--    `is_likely_obfuscated` (heuristic at >=60 + ≥1 strong signal, OR
--    js-x-ray flagged the obfuscator family). `obfuscator_name` is the
--    family name reported by @nodesecure/js-x-ray (jsfuck, obfuscator.io,
--    morse, jjencode, freejsobfuscator, ...) or empty when not detected.
--
--    Run once against an existing deployment. The CREATE TABLE above
--    already reflects the post-migration shape, so fresh installs skip this.
ALTER TABLE redb_js_features
    RENAME COLUMN is_obfuscated TO is_likely_obfuscated;
ALTER TABLE redb_js_features
    ADD COLUMN IF NOT EXISTS obfuscator_name LowCardinality(String) AFTER is_likely_obfuscated;

-- 8. Harmonise code_text_content column names with redb_iocs.source_type
--    enum values. The enum already uses `text_raw` / `text_normalized` for
--    the surface labels; the table previously stored the same data under
--    `content_raw` / `content_normalized`, forcing every join across the two
--    to translate names. Renaming the columns produces a self-documenting
--    schema where `redb_iocs.source_type='text_raw'` points directly at
--    `code_text_content.text_raw`.
--
--    Run once against an existing deployment. The CREATE TABLE above
--    already reflects the post-migration shape, so fresh installs skip this.
ALTER TABLE code_text_content
    RENAME COLUMN content_raw TO text_raw;
ALTER TABLE code_text_content
    RENAME COLUMN content_normalized TO text_normalized;

-- 9. Add revealed_by_deobf flag to redb_js_suspicious_apis. The strings/APIs
--    extractors now scan both the raw source and the deobfuscated text so APIs
--    hidden behind one obfuscation layer (Vjw0rm-style array.join + eval,
--    Dean-Edwards packers, ...) surface in the table. The flag is 1 only when
--    the API was *not* found in the raw source — querying for it isolates
--    "deobf-only" findings without joining redb_js_deobfuscation.
--
--    Run once against an existing deployment. The CREATE TABLE above
--    already reflects the post-migration shape, so fresh installs skip this.
ALTER TABLE redb_js_suspicious_apis
    ADD COLUMN IF NOT EXISTS revealed_by_deobf UInt8 AFTER context_snippet;