Ida G. Sprinkhuizen-Kuyper

# APK Feature Analysis — ClickHouse Schema

All tables follow the `ReplacingMergeTree()` pattern (no version column — latest
insert wins on deduplication).

**Companion schema:** `docs/apk-code-schema.md` (code-analysis tables)

---

## Table overview

| # | Table | Engine | Key |
|---|-------|--------|-----|
| 1 | `redb_apk_features` | ReplacingMergeTree | `sha256` |
| 2 | `redb_apk_components` | ReplacingMergeTree | `(sha256, component_type, class_name)` |
| 3 | `redb_apk_manifest` | ReplacingMergeTree | `sha256` |
| 4 | `redb_apk_permissions` | ReplacingMergeTree | `(sha256, permission_name)` |
| 5 | `redb_apk_signature` | ReplacingMergeTree | `sha256` |
| 6 | `redb_apk_certificates` | ReplacingMergeTree | `(sha256, certificate_thumbprint)` |
| 7 | `redb_apk_dex` | ReplacingMergeTree | `(sha256, dex_sha256)` |
| 8 | `redb_apk_dex_api_usage` | ReplacingMergeTree | `(sha256, dex_sha256, api_category)` |
| 9 | `redb_apk_resources` | ReplacingMergeTree | `(sha256, resource_path)` |
| 10 | `redb_apk_native_libs` | ReplacingMergeTree | `(sha256, lib_abi, lib_filename)` |
| 11 | `redb_apk_inconsistency_tests` | ReplacingMergeTree | `sha256` |

---

## Table 1: `redb_apk_features`

Core metadata — one row per APK.

```sql
CREATE TABLE IF NOT EXISTS redb_apk_features (
    sha256              FixedString(64),
    package_name        Nullable(String),
    app_name            Nullable(String),
    version_code        Nullable(UInt32),
    version_name        Nullable(String),
    min_sdk_version     Nullable(UInt16),
    target_sdk_version  Nullable(UInt16),
    compile_sdk_version Nullable(UInt16),
    main_activity       Nullable(String),
    is_debuggable       UInt8,
    allow_backup        UInt8,
    uses_cleartext_traffic UInt8,
    supported_abis      Array(String),
    dex_count           UInt16,
    total_dex_size      UInt64,
    total_file_count    UInt32,
    has_native_code     UInt8,
    has_assets          UInt8,
    uses_libraries      Array(String),
    earliest_content_modification Nullable(String),
    latest_content_modification   Nullable(String),
    contains_embedded_apk UInt8,
    analysis_date       DateTime64(3, 'UTC')
) ENGINE = ReplacingMergeTree()
ORDER BY sha256;
```

## Table 2: `redb_apk_components`

Manifest components — one row per component.

```sql
CREATE TABLE IF NOT EXISTS redb_apk_components (
    sha256              FixedString(64),
    component_type      LowCardinality(String),   -- 'activity', 'service', 'receiver', 'provider'
    class_name          String,
    is_exported         UInt8,
    intent_actions      Array(String),
    intent_categories   Array(String),
    analysis_date       DateTime64(3, 'UTC')
) ENGINE = ReplacingMergeTree()
ORDER BY (sha256, component_type, class_name);
```

## Table 3: `redb_apk_manifest`

Manifest summary — one row per APK.

```sql
CREATE TABLE IF NOT EXISTS redb_apk_manifest (
    sha256              FixedString(64),
    activity_count      UInt16,
    service_count       UInt16,
    receiver_count      UInt16,
    provider_count      UInt16,
    intent_filters_by_action   Array(String),
    intent_filters_by_category Array(String),
    uses_features       Array(String),
    manifest_xml        Nullable(String),
    analysis_date       DateTime64(3, 'UTC')
) ENGINE = ReplacingMergeTree()
ORDER BY sha256;
```

## Table 4: `redb_apk_permissions`

Permissions — one row per permission per APK.

```sql
CREATE TABLE IF NOT EXISTS redb_apk_permissions (
    sha256              FixedString(64),
    permission_name     String,
    protection_level    LowCardinality(String),
    is_custom           UInt8,
    analysis_date       DateTime64(3, 'UTC')
) ENGINE = ReplacingMergeTree()
ORDER BY (sha256, permission_name);
```

## Table 5: `redb_apk_signature`

Signing info — one row per APK. The `apk_signer_*` fields identify the actual
signer certificate using V3→V2→V1 priority (V2/V3: `certificates[0]` per spec;
V1: `SignerInfo.issuerAndSerialNumber` matching). Prefixed with `apk_` to
distinguish from PE/Mach-O signer fields in cross-format queries.

```sql
CREATE TABLE IF NOT EXISTS redb_apk_signature (
    sha256              FixedString(64),
    is_signed           UInt8,
    signature_scheme_versions Array(UInt8),
    number_of_certificates UInt8,
    apk_signer_subject  Nullable(String),
    apk_signer_issuer   Nullable(String),
    analysis_date       DateTime64(3, 'UTC')
) ENGINE = ReplacingMergeTree()
ORDER BY sha256;
```

## Table 6: `redb_apk_certificates`

Certificate details — one row per certificate per APK. The
`certificate_source_scheme` column tracks which signing scheme(s) the
certificate was found in (`'v1'`, `'v2'`, `'v3'`, or `'v1,v2'` when the same
certificate appears across multiple schemes).

```sql
CREATE TABLE IF NOT EXISTS redb_apk_certificates (
    sha256                    FixedString(64),
    certificate_subject       String,
    certificate_issuer        String,
    certificate_serial_number String,
    certificate_valid_from    DateTime64(3, 'UTC'),
    certificate_valid_to      DateTime64(3, 'UTC'),
    certificate_thumbprint    FixedString(40),
    certificate_algorithm     LowCardinality(String),
    key_size                  Nullable(UInt16),
    is_self_signed            UInt8,
    certificate_source_scheme LowCardinality(String),
    analysis_date             DateTime64(3, 'UTC')
) ENGINE = ReplacingMergeTree()
ORDER BY (sha256, certificate_thumbprint);
```

## Table 7: `redb_apk_dex`

DEX file analysis — one row per DEX file.

```sql
CREATE TABLE IF NOT EXISTS redb_apk_dex (
    sha256              FixedString(64),
    dex_sha256          FixedString(64),
    dex_filename        String,
    dex_tlsh            Nullable(String),
    dex_class_count     UInt32,
    dex_method_count    UInt32,
    dex_string_count    UInt32,
    dex_top_packages    String,                    -- JSON
    dex_obfuscation_indicators String,             -- JSON
    analysis_date       DateTime64(3, 'UTC')
) ENGINE = ReplacingMergeTree()
ORDER BY (sha256, dex_sha256);
```

## Table 8: `redb_apk_dex_api_usage`

DEX API usage — one row per category per DEX.

```sql
CREATE TABLE IF NOT EXISTS redb_apk_dex_api_usage (
    sha256              FixedString(64),
    dex_sha256          FixedString(64),
    api_category        LowCardinality(String),
    api_calls           Array(String),
    analysis_date       DateTime64(3, 'UTC')
) ENGINE = ReplacingMergeTree()
ORDER BY (sha256, dex_sha256, api_category);
```

## Table 9: `redb_apk_resources`

Resource inventory — one row per resource.

```sql
CREATE TABLE IF NOT EXISTS redb_apk_resources (
    sha256              FixedString(64),
    resource_path       String,
    resource_size       UInt64,
    resource_sha256     FixedString(64),
    resource_magika     LowCardinality(String),
    resource_entropy             Float32,
    is_suspicious       UInt8,
    analysis_date       DateTime64(3, 'UTC')
) ENGINE = ReplacingMergeTree()
ORDER BY (sha256, resource_path);
```

## Table 10: `redb_apk_native_libs`

Native libraries — one row per `.so` file.

```sql
CREATE TABLE IF NOT EXISTS redb_apk_native_libs (
    sha256              FixedString(64),
    lib_abi             LowCardinality(String),
    lib_filename        String,
    lib_size            UInt64,
    lib_sha256          FixedString(64),
    lib_is_known_packer UInt8,
    analysis_date       DateTime64(3, 'UTC')
) ENGINE = ReplacingMergeTree()
ORDER BY (sha256, lib_abi, lib_filename);
```

## Table 11: `redb_apk_inconsistency_tests`

Inconsistency tests — one row per APK.

```sql
CREATE TABLE IF NOT EXISTS redb_apk_inconsistency_tests (
    sha256              FixedString(64),
    test_zip_bomb                    Nullable(UInt8),
    test_zip_duplicate_entries       Nullable(UInt8),
    test_zip_path_traversal          Nullable(UInt8),
    test_zip_suspicious_timestamps   Nullable(UInt8),
    test_hidden_dex_files            Nullable(UInt8),
    test_manifest_component_mismatch Nullable(UInt8),
    test_debuggable_release          Nullable(UInt8),
    test_emulator_detection_strings  Nullable(UInt8),
    test_debugger_detection          Nullable(UInt8),
    test_root_detection              Nullable(UInt8),
    analysis_date       DateTime64(3, 'UTC')
) ENGINE = ReplacingMergeTree()
ORDER BY sha256;
```