Cataldo Basile

# APK Code Analysis — ClickHouse Schema

All tables follow the `ReplacingMergeTree(analysis_date)` pattern from
`docs/new-code-binja-schema.md`, except `code_apk_analysis_errors` which
uses `MergeTree()`.

**Companion PDD:** `APK_CODE_ANALYSIS_PDD-Tech_Annex.md` §7

---

## Table overview

| # | Table | Analog (Binja) | Engine | Key |
|---|-------|----------------|--------|-----|
| 1 | `code_apk_decompiled_methods_content` | `code_binja_decompiled_functions_content` | ReplacingMergeTree | `decompiled_method_hash` |
| 2 | `code_apk_decompiled_methods_references` | `code_binja_decompiled_functions_references` | ReplacingMergeTree | `(sha256, decompiled_method_hash)` |
| 3 | `code_apk_smali_methods_content` | `code_binja_disassembled_functions_content` | ReplacingMergeTree | `smali_method_hash` |
| 4 | `code_apk_smali_methods_references` | `code_binja_disassembled_functions_references` | ReplacingMergeTree | `(sha256, smali_method_hash)` |
| 5 | `code_apk_method_similarity_metrics` | `code_binja_function_similarity_metrics` | ReplacingMergeTree | `smali_method_hash` |
| 6 | `code_apk_cfg_methods` | `code_binja_cfg_functions` | ReplacingMergeTree | `smali_method_hash` |
| 7 | `code_binja_strings_raw` *(shared)* | — | Null (→ MV) | — |
| 8 | `code_apk_analysis_errors` | `function_analysis_errors_binja` | MergeTree | `(sha256, error_location, error_hash)` |

---

## Table 1: `code_apk_decompiled_methods_content`

**Analog:** `code_binja_decompiled_functions_content`

```sql
CREATE TABLE IF NOT EXISTS code_apk_decompiled_methods_content (
    decompiled_method_hash FixedString(64),          -- SHA-256 of normalized Java source
    decompiled_method String CODEC(ZSTD(3)),          -- Full Java method source
    decompiled_method_type Enum8('USER'=1, 'LIBRARY'=2, 'UNKNOWN'=5) DEFAULT 'UNKNOWN',
    decompiled_has_string_encryption UInt8 DEFAULT 0,
    decompiled_has_reflection_calls UInt8 DEFAULT 0,
    decompiled_excessive_goto_count UInt8 DEFAULT 0,
    analysis_date DateTime64(3, 'UTC'),

    INDEX idx_method_content_token lower(decompiled_method) TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 1
) ENGINE = ReplacingMergeTree(analysis_date)
ORDER BY decompiled_method_hash;
```

## Table 2: `code_apk_decompiled_methods_references`

**Analog:** `code_binja_decompiled_functions_references`

```sql
CREATE TABLE IF NOT EXISTS code_apk_decompiled_methods_references (
    sha256 FixedString(64),
    decompiled_method_hash FixedString(64),
    smali_method_hash Nullable(FixedString(64)),
    decompiled_class_name LowCardinality(String),
    decompiled_method_name LowCardinality(String),
    decompiled_method_signature String,               -- Dalvik descriptor: (Landroid/os/Bundle;)V
    decompiled_method_prototype String,               -- Java-style: void onCreate(Bundle)
    functions_caller Array(String),
    functions_call Array(String),
    analysis_date DateTime64(3, 'UTC'),

    INDEX idx_decompiled_class_name decompiled_class_name TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 1,
    INDEX idx_decompiled_method_name decompiled_method_name TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 1,
    INDEX idx_functions_caller functions_caller TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 1,
    INDEX idx_functions_call functions_call TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 1
) ENGINE = ReplacingMergeTree(analysis_date)
ORDER BY (sha256, decompiled_method_hash);
```

## Table 3: `code_apk_smali_methods_content`

**Analog:** `code_binja_disassembled_functions_content`

```sql
CREATE TABLE IF NOT EXISTS code_apk_smali_methods_content (
    smali_method_hash FixedString(64),                -- SHA-256 of normalized smali body
    smali_method String CODEC(ZSTD(3)),               -- Full smali method body
    smali_method_type Enum8('USER'=1, 'LIBRARY'=2, 'UNKNOWN'=5) DEFAULT 'UNKNOWN',
    smali_instructions_count UInt32,
    smali_register_count UInt16,
    smali_has_string_encryption UInt8 DEFAULT 0,
    smali_has_reflection_calls UInt8 DEFAULT 0,
    smali_excessive_goto_count UInt8 DEFAULT 0,
    smali_flattened_score Float64 DEFAULT 0.0,       -- Control flow flattening score (0.0–1.0)
    smali_mba_score Float64 DEFAULT 0.0,             -- Mixed boolean-arithmetic score (0.0–1.0)
    analysis_date DateTime64(3, 'UTC'),

    INDEX idx_smali_ngram smali_method TYPE ngrambf_v1(3, 32768, 3, 0) GRANULARITY 1,
    INDEX idx_smali_method_type smali_method_type TYPE set(3) GRANULARITY 1,
    INDEX idx_smali_instr_count smali_instructions_count TYPE minmax GRANULARITY 4,
    INDEX idx_smali_reg_count smali_register_count TYPE minmax GRANULARITY 4,
    INDEX idx_smali_flattened smali_flattened_score TYPE minmax GRANULARITY 4,
    INDEX idx_smali_mba smali_mba_score TYPE minmax GRANULARITY 4
) ENGINE = ReplacingMergeTree(analysis_date)
ORDER BY smali_method_hash;
```

## Table 4: `code_apk_smali_methods_references`

**Analog:** `code_binja_disassembled_functions_references`

```sql
CREATE TABLE IF NOT EXISTS code_apk_smali_methods_references (
    sha256 FixedString(64),
    smali_method_hash FixedString(64),
    decompiled_method_hash Nullable(FixedString(64)),
    smali_class_name LowCardinality(String),
    smali_method_name LowCardinality(String),
    smali_method_signature String,
    analysis_date DateTime64(3, 'UTC'),

    INDEX idx_smali_class_name smali_class_name TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 1,
    INDEX idx_smali_method_name smali_method_name TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 1
) ENGINE = ReplacingMergeTree(analysis_date)
ORDER BY (sha256, smali_method_hash);
```

## Table 5: `code_apk_method_similarity_metrics`

**Analog:** `code_binja_function_similarity_metrics`

Content-based fuzzy matching table. Structural/CFG scalars (block_count, edge_count, etc.)
are in `code_apk_cfg_methods` (Table 6) — this table holds only fuzzy hashes and MinHash.

```sql
CREATE TABLE IF NOT EXISTS code_apk_method_similarity_metrics (
    smali_method_hash FixedString(64),
    -- Raw smali fuzzy hashes (analog: ssdeep_disassembly / tlsh_disassembly)
    ssdeep_smali Nullable(String),
    tlsh_smali Nullable(FixedString(72)),
    -- Semantically normalized smali fuzzy hashes (analog: ssdeep_llil / tlsh_llil)
    ssdeep_smali_normalized Nullable(String),
    tlsh_smali_normalized Nullable(FixedString(72)),
    minhash Array(UInt8),
    analysis_date DateTime64(3, 'UTC'),

    INDEX idx_ssdeep ssdeep_smali TYPE bloom_filter GRANULARITY 1,
    INDEX idx_tlsh tlsh_smali TYPE bloom_filter GRANULARITY 1,
    INDEX idx_ssdeep_norm ssdeep_smali_normalized TYPE bloom_filter GRANULARITY 1,
    INDEX idx_tlsh_norm tlsh_smali_normalized TYPE bloom_filter GRANULARITY 1
) ENGINE = ReplacingMergeTree(analysis_date)
ORDER BY smali_method_hash;
```

## Table 6: `code_apk_cfg_methods`

**Analog:** `code_binja_cfg_functions` (see `db_migration/cfg_functions_ddl.sql`)

Structural and topological features computed from smali CFG. All fields mirror
the Binja table with APK-appropriate naming.

```sql
CREATE TABLE IF NOT EXISTS code_apk_cfg_methods (
    -- Identity
    smali_method_hash FixedString(64),

    -- Tier 0: Exact structural match
    cfg_topology_hash FixedString(16),

    -- Tier 1: Structural pre-filtering
    block_count UInt16,
    edge_count UInt16,
    cfg_instructions_count UInt32,            -- Dalvik instruction count (analog: llil_total_operations)
    call_count UInt16,
    cyclomatic_complexity UInt16,
    loop_count UInt16,
    max_depth UInt16,
    max_fan_out UInt16,
    md_index_topdown UInt64,
    md_index_bottomup UInt64,
    prime_product_smali UInt64,              -- Dalvik semantic primes (analog: prime_product_llil)

    -- Tier 2: Fuzzy matching
    cfg_feature_tlsh Nullable(FixedString(72)),
    wl_minhash Array(UInt8),

    -- Embedding-ready storage
    bb_features Array(Array(UInt16)),        -- ACFG block feature vectors
    cfg_adjacency Array(UInt32),             -- Packed (src << 16 | tgt)

    analysis_date DateTime64(3, 'UTC'),

    -- Indexes
    INDEX idx_topology cfg_topology_hash TYPE bloom_filter GRANULARITY 1,
    INDEX idx_complexity cyclomatic_complexity TYPE minmax GRANULARITY 4,
    INDEX idx_block_count block_count TYPE minmax GRANULARITY 4,
    INDEX idx_edge_count edge_count TYPE minmax GRANULARITY 4,
    INDEX idx_call_count call_count TYPE minmax GRANULARITY 4,
    INDEX idx_cfg_instr_count cfg_instructions_count TYPE minmax GRANULARITY 4,
    INDEX idx_loop_count loop_count TYPE minmax GRANULARITY 4,
    INDEX idx_max_depth max_depth TYPE minmax GRANULARITY 4,
    INDEX idx_max_fan_out max_fan_out TYPE minmax GRANULARITY 4,
    INDEX idx_md_topdown md_index_topdown TYPE minmax GRANULARITY 4,
    INDEX idx_md_bottomup md_index_bottomup TYPE minmax GRANULARITY 4,
    INDEX idx_prime prime_product_smali TYPE bloom_filter GRANULARITY 1,
    INDEX idx_cfg_tlsh cfg_feature_tlsh TYPE bloom_filter GRANULARITY 1
) ENGINE = ReplacingMergeTree(analysis_date)
ORDER BY smali_method_hash;
```

**Column mapping (APK → Binja):**

| APK column | Binja column | Notes |
|------------|-------------|-------|
| `smali_method_hash` | `disassembled_function_hash` | Both SHA-256 of normalized code |
| `cfg_instructions_count` | `llil_total_operations` | Dalvik instructions vs LLIL operations |
| `prime_product_smali` | `prime_product_llil` | Same prime algorithm, Dalvik semantic categories |
| All others | Same name | Identical algorithms from shared `cfg_features.py` |

## Table 7: `code_binja_strings_raw` (shared)

APK strings are inserted into the existing `code_binja_strings_raw` Null-engine table,
which feeds materialized views (`code_binja_strings_by_binary`, `mv_string_popularity_public`).
This enables cross-format string correlation between PE/ELF/Mach-O/APK samples.

No new table creation needed — see `docs/new-code-binja-schema.md` for the existing DDL.

## Table 8: `code_apk_analysis_errors`

**Analog:** `function_analysis_errors_binja`

```sql
CREATE TABLE IF NOT EXISTS code_apk_analysis_errors (
    sha256 FixedString(64),
    class_name Nullable(String) CODEC(ZSTD(3)),
    method_name Nullable(String) CODEC(ZSTD(3)),
    error_location LowCardinality(String) CODEC(ZSTD(3)),   -- 'jadx', 'apktool', 'androguard', 'analysis'
    error_message Nullable(String) CODEC(ZSTD(3)),
    error_type Nullable(String) CODEC(ZSTD(3)),
    error_hash FixedString(32),                               -- MD5 for dedup
    status Enum8('new' = 1, 'investigating' = 2, 'fixed' = 3, 'wontfix' = 4) DEFAULT 'new',
    analysis_date DateTime64(3, 'UTC')
) ENGINE = MergeTree()
ORDER BY (sha256, error_location, error_hash);
```