Skip to main content

​
Overview

Simple Manager Mobile uses SQLite for local data storage. The data model is designed to be simple yet flexible, supporting various types of records with optional metadata.

​
Database Schema

​
Records Table

The records table is the primary storage for all record data: 
CREATE TABLE records (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    subtitle TEXT,
    metadata TEXT,
    type TEXT NOT NULL,
    userId TEXT,
    createdAt TEXT,
    updatedAt TEXT,
    isDeleted INTEGER
);

​
Field Specifications

​
id
TEXT
required
Primary key for the record. Uses TEXT type to support UUID or other string-based identifiers.
​
title
TEXT
required
Record title with a maximum length of 50 characters. Cannot be empty or duplicated.
​
subtitle
TEXT
Optional subtitle field for additional context.
​
metadata
TEXT
Optional field for storing JSON or other structured data as text.
​
type
TEXT
required
Required field indicating the record type or category.
​
userId
TEXT
Optional user identifier for multi-user scenarios.
​
createdAt
TEXT
ISO 8601 timestamp stored as text.
​
updatedAt
TEXT
ISO 8601 timestamp stored as text.
​
isDeleted
INTEGER
Boolean flag stored as INTEGER (0 or 1) for soft delete functionality.

​
Data Constraints

The data model enforces several important constraints:
  • Title uniqueness: Record titles cannot be duplicated
  • Title length: Maximum 50 characters
  • Required fields: title and type must always have values
  • Title minimum length: At least 3 characters
  • Type minimum length: At least 3 characters

​
Database Connection

The application uses Expo SQLite for database management: 
import * as SQLite from "expo-sqlite";

export const db = SQLite.openDatabaseSync("simple_manager.db");

​
Design Decisions

​
Why SQLite?

SQLite provides:
  • Local-first data storage
  • No network dependency
  • Fast queries
  • Built-in support through Expo

​
Soft Deletes

The isDeleted flag enables soft deletion, allowing records to be recovered and maintaining data integrity for audit purposes.

​
Text-based Timestamps

Timestamps are stored as TEXT in ISO 8601 format, making them human-readable and easily parseable across different platforms.

Build docs developers (and LLMs) love

Get started for freeTalk to us