Building Flutter Apps (4/5) — GrowNote Architecture: SQLite + Riverpod Design

Relational data · data-driven state · periodic reflection — design decision patterns for a growth tracking app

What This Post Covers

  • Storage selection criteria: Key-value (Hive) vs relational (SQLite) — why data structure analysis must precede tool selection, and a decision checklist
  • State management selection criteria: Event-driven (BLoC) vs data-driven (Riverpod) — mapping to app interaction characteristics
  • Reflection system design: Daily/weekly/monthly template structure and the minimum-friction UX principle for sustained input
  • Harness pattern application: What changes when a prompt harness is layered onto a Flutter development workflow

Background — Framing the Problem Correctly

Standard diary apps center on free-form text input. The advantage is low entry barrier; the limitation is that no matter how many entries accumulate, "what has changed" never surfaces. When past records go unread, retention drops.

GrowNote reframes the problem. The unit of record is not "what I did" but "what I grew in." Category-wise accumulation, periodic reflection, and trend visualization are the three axes. This framing cascades into every decision: storage, state management, and UX.

1. Data Model — Relational as a Baseline Requirement

Core entities:

  • GrowthRecord: Individual growth entry. Category, content, optional numeric value, date.
  • Category: User-defined growth domain (e.g., exercise, coding, reading, investing).
  • Tag: Keywords attached to records. Many-to-many relationship.
  • Reflection: Daily/weekly/monthly retrospective. References records within a time range.

Relationship structure: - Category ↔ GrowthRecord: 1:N - GrowthRecord ↔ Tag: N:M - Reflection ↔ GrowthRecord: date-range join

Queries like "last 30 days of records in a specific category" and "numeric average for records with a specific tag" are core functionality. Set operations and joins are the primary workload. That is where the storage decision diverges.

2. SQLite vs Hive — Decision Criteria

The choice between key-value (Hive) and relational (SQLite) storage is determined by data structure.

Hive is appropriate when: single-entity reads and writes dominate with minimal joins. Use cases: settings, simple collections, caches. QuietLeaf (a TXT reader app) is a representative example.

SQLite is appropriate when: many-to-many relationships, range queries, and aggregation are core functionality. GrowNote falls here.

Implementing GrowNote's queries in Hive would require loading all data into memory and filtering/aggregating in Dart. With thousands of records, first-screen load time grows linearly. The equivalent SQLite queries are index-backed native operations.

Implementation uses the sqflite package with version-based migrations. Schema changes execute via ALTER TABLE in the onUpgrade callback.

Decision checklist: - Does the data model contain many-to-many relationships? - Are range queries (by date, by numeric value) a primary feature? - Is real-time aggregation (sum, average, frequency) displayed to the user? - Is data expected to accumulate in the hundreds of records or more?

If two or more answers are yes, relational storage is the default.

3. Riverpod — Declarative Flow for Data-Driven State

State management selection follows the same logic: start with the problem's nature.

Event-driven state (BLoC): apps where explicit user input or game events are the primary trigger. This is why BLoC was used in Minesweeper (hexagonal).

Data-driven state (Riverpod): apps where CRUD dominates, state is shared across screens, and derived state (statistics, charts) depends on source data. GrowNote is in this category.

Core Provider structure:

  • categoryProvider: Full category list. Synced to DB via AsyncNotifier.
  • recordsProvider: Record list by filter condition. Family Provider for per-category and per-period separation.
  • statsProvider: Statistics. Auto-recomputes by watching recordsProvider via ref.watch.

The key advantage is automatic invalidation of derived state. When recordsProvider changes, statsProvider recomputes without any manually dispatched event. "Update statistics" events are eliminated, and the state graph remains declarative.

4. Reflection System — Daily / Weekly / Monthly Hierarchy

This is GrowNote's differentiating feature and the most design-intensive component. The core problem with plain logging is that users have no structural incentive to revisit the past. Reflection templates provide that incentive.

Daily reflection: Groups the day's records by category. Single-line free input: "Today's key learning." Minimizing input fields lowers the friction to open the view.

Weekly reflection: Visualizes activity frequency and numeric trends per category over 7 days. "Most improved area" and "neglected area" auto-highlight. This auto-highlight is handled by statsProvider range filtering.

Monthly reflection: Month-scale patterns. Per-category heatmap, tag frequency, numeric change graph.

The reflection template functions as a feedback loop that generates additional entries. When an empty category is visually present in the weekly view, the awareness of "I didn't exercise this week" translates into input the following week. The reflection UI produces a second-order effect that increases the overall logging rate.

Design principle: Reflection templates work in inverse proportion to input field count. The initial version had high input overhead. After reducing to essential fields and demoting the rest to optional, sustained input became achievable. Minimum input → automatic aggregation → visual feedback is the general principle for reflection UI design.

5. Harness Pattern and Data Export

Layering a prompt harness onto the Flutter development workflow changes two things. First, documenting the data model and SQLite schema in CLAUDE.md preserves context across sessions. Second, feature additions are forced through a plan → execute → verify cycle. Ad-hoc code additions decrease; schema migration errors decrease.

Data export and import are foundational to user data sovereignty. The app exports all data as JSON and accepts the same format for import. The reason for converting to JSON rather than exporting the raw SQLite DB file is portability (readable by external tools) and legibility (users can inspect their own data). For apps where device migration and backup scenarios are real use cases, this layer is substantially cheaper to design from the start than to retrofit later.

Limitations and Improvement Directions

The cost of starting with Hive: An early phase began with key-value storage. As relationship complexity grew, migration to SQLite became necessary. The lesson: data structure analysis must precede storage selection. Locking in a tool before understanding the structure accumulates rework cost.

Statistics computation location: The initial implementation computed statistics directly in UI widgets, triggering recomputation on every screen transition and producing noticeable sluggishness. Moving computation to the Provider layer and applying caching resolved it. Deviating from Riverpod's baseline pattern — derived state belongs in the state management layer — produces performance problems that are immediately visible.

Over-engineered reflection templates: When input field count is high, users skip the reflection entirely. "Asking for more information" and "making input more frequent" are in direct conflict; the latter is consistently better for long-term retention metrics.

Applicability

The design principles in this post are not specific to GrowNote.

  • Personal apps where relational data and aggregation queries are core → SQLite + Riverpod is a reasonable starting point.
  • Event-trigger-centric apps (games, real-time interaction) → BLoC remains valid.
  • Single-entity-centric apps (readers, notes) → Hive is sufficient.

The same Flutter stack produced three different optimal combinations — QuietLeaf (key-value, Hive), Minesweeper (event-driven, BLoC), GrowNote (relational, SQLite + Riverpod) — which confirms that stack selection is a function of the problem's nature, not of convention.

Open Questions

  • How far can "minimum input" in reflection templates be reduced? What trade-offs emerge if the reflection prompt itself is generated by AI from that day's records?
  • What is the cost/benefit split when introducing SQLite FTS (Full-Text Search) for tag and content search, accounting for migration overhead?
  • What additional user scenarios are covered by dualizing the export format to include both JSON and SQLite dump?

Series overview: Series index

๋Œ“๊ธ€

๋Œ“๊ธ€ ์“ฐ๊ธฐ