Global Schema Overview

​

Core Application Domain

This domain manages static application data, categorization, and the configuration of intervention limits.

​

Usage & Event Domain

This domain handles high-volume time-series data, raw event logging, and aggregated daily statistics.

​

Table Reference

​

Core Tables

​

app_metadata

The source of truth for all installed applications.

• Primary Key: package_name
• Purpose: Caches package manager info to avoid expensive system calls.
• Integrity: is_user_visible determines if the app appears in the launcher/lists.

​

limit_groups

Defines rulesets for blocking apps (e.g., “Social Media”, “Games”).

• Primary Key: id (Auto-generated)
• Index: Unique index on name.

​

limited_apps

Mapping table assigning apps to limit groups.

• Primary Key: package_name (Implies an app can belong to only one group).
• Foreign Key: group_id -> limit_groups.id with OnDelete.CASCADE.

​

Usage & Analytics Tables

​

daily_app_usage

Aggregated usage stats per app, per day.

• Primary Key: id
• Index: Unique composite index on (package_name, date_string). Ensures only one record per app/day.

​

raw_app_events

The append-only log of all system events (screen on/off, app changes).

• Primary Key: id
• Performance: Heavily indexed on event_timestamp and event_date_string for range queries.
• Volume: High write frequency.

​

user_journey

A Materialized View optimized for UI rendering of the daily timeline.

• Primary Key: id
• Partitioning: Logically partitioned by date_string.
• Lifecycle: Regenerated from unlock_sessions and raw_app_events.

​

notifications

Logs incoming notifications for analysis and batching.

• Indices: Heavily indexed for filtering by impact, type, and date_string.

​

scroll_sessions

Tracks vertical distance scrolled in apps.

• Unit: Can be PIXELS or MICROMETERS.

​

Performance & Integrity Analysis

​

1. Referential Integrity

• Cascading Deletes: The schema uses ForeignKey.CASCADE strictly for the LimitGroup -> LimitedApp and LimitGroup -> SnoozeHistoryEntity relationships. This ensures that deleting a user-defined limit group cleans up all associated configuration and history immediately.
• Logical Keys: Most other relationships (e.g., AppMetadata -> DailyAppUsageRecord) rely on logical string keys (package_name) rather than strict SQL constraints. This allows usage data to persist even if an app is temporarily uninstalled (AppMetadata removed).

​

2. Indexing Strategy

• Time-Series Optimization: Almost every table includes a date_string column (“YYYY-MM-DD”) with an associated @Index. This allows for efficient WHERE date_string = :today queries, which power 90% of the UI.
• Uniqueness:
  • daily_app_usage: (package_name, date_string) is unique. Upsert strategies are used to update usage counters.
  • daily_insights: (date_string, insight_key) is unique.

​

3. Data Partitioning & Pruning

• Raw Data: Tables like raw_app_events and notifications grow indefinitely. The Dao includes methods like deleteEventsOlderThan to prune data based on timestamps, preventing database bloat on mobile devices.
• Materialized Views: user_journey acts as a cache. It can be completely wiped and rebuilt from raw_app_events and unlock_sessions if the schema changes or data corruption occurs.

​

4. Concurrency Control

• Upserts: The schema heavily utilizes @Upsert (or OnConflictStrategy.REPLACE) for statistical tables (daily_app_usage, app_categories). This ensures that background sync jobs and foreground UI updates do not cause Primary Key collisions.