Database & Local Storage

The Certi-Lock® app uses Expo SQLite with Drizzle ORM for structured data storage, the app document directory for cached images, and AsyncStorage for lightweight key-value caching (banners).

---

SQLite Database

Configuration

The database is configured via Drizzle ORM with the Expo SQLite driver:

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "sqlite",
  driver: "expo",
  schema: "./db/schema.ts",
  out: "./drizzle",
});

Setting	Value
Database File	db.db
Location	App document directory (Expo SQLite managed)
ORM	Drizzle ORM v0.45.1
Driver	Expo SQLite
Migrations	drizzle/ directory (5 migration files)

Initialization

The database is initialized in the root layout via the SQLiteProvider:

// app/_layout.tsx
<SQLiteProvider databaseName="db.db" onInit={migrateDb}>
  {/* App content */}
</SQLiteProvider>

The migrateDb callback runs Drizzle migrations on app startup, ensuring the schema is always current.

---

Schema Definition

Items Table

The items table stores all scanned product data:

// db/schema.ts
import { sqliteTable, int, text } from "drizzle-orm/sqlite-core";

export const items = sqliteTable("items", {
  id:             int().primaryKey({ autoIncrement: true }),
  name:           text().notNull(),
  subtitle:       text().notNull(),
  serialNumber:   text().notNull(),
  imageHash:      text().notNull(),
  imageUrl:       text().notNull(),
  sealingDate:    text().notNull(),
  productDetails: text({ mode: "json" }).notNull(),
  metalType:      text().notNull().default(""),
  shape:          text().notNull().default(""),
});

Column Details

Column	Type	Description
id	INTEGER	Auto-incrementing primary key
name	TEXT	Product display name from Algolia
subtitle	TEXT	Product subtitle/description from Algolia
serialNumber	TEXT	QR/barcode value — the unique product identifier
imageHash	TEXT	SHA256 hash of the sealed image — used as the local filename
imageUrl	TEXT	Product image URL from Algolia (for online fallback display)
sealingDate	TEXT	Date the item was sealed (from Image API response)
productDetails	TEXT (JSON)	JSON object containing product specifications (weight, purity, dimensions, etc.)
metalType	TEXT	Metal type: "gold" or "silver" — used for fallback image selection
shape	TEXT	Product shape: "round" or "bar" — used for fallback image selection

---

Database Operations

Insert Item (save-item.ts)

Creates a new item record after a successful scan:

// Simplified flow
const productData = await getDataFromAlgolia(serialNumber);
const imageData = await getImageFromAPI(serialNumber);
const imageHash = await generateSHA256(imageData);
await saveImageToFileSystem(imageHash, imageData);

await db.insert(items).values({
  name: productData.name,
  subtitle: productData.subtitle,
  serialNumber: serialNumber,
  imageHash: imageHash,
  imageUrl: productData.imageUrl,
  sealingDate: imageData.sealingDate,
  productDetails: productData.details,
  metalType: productData.metalType,
  shape: productData.shape,
});

Query All Items (get-items.ts)

Retrieves all items with optional search filtering:

// With search support
const results = await db
  .select()
  .from(items)
  .where(
    or(
      like(items.name, `%${search}%`),
      like(items.serialNumber, `%${search}%`)
    )
  )
  .orderBy(desc(items.id));

Query Single Item (get-item-by-id.ts)

Retrieves a single item by its primary key:

const result = await db
  .select()
  .from(items)
  .where(eq(items.id, itemId))
  .limit(1);

Update Item (update-item.ts)

Re-fetches product data and image for items older than 24 hours:

const freshData = await getDataFromAlgolia(item.serialNumber);
const freshImage = await getImageFromAPI(item.serialNumber);
const newHash = await generateSHA256(freshImage);

await db
  .update(items)
  .set({
    name: freshData.name,
    imageHash: newHash,
    productDetails: freshData.details,
    // ... other fields
  })
  .where(eq(items.id, itemId));

Delete Item (delete-item.ts)

Removes an item from the database and its cached image from the filesystem:

// Delete cached image file
const item = await getItemById(itemId);
await FileSystem.deleteAsync(
  `${FileSystem.documentDirectory}${item.imageHash}`
);

// Delete database record
await db.delete(items).where(eq(items.id, itemId));

---

Migrations

Drizzle ORM manages schema migrations stored in the drizzle/ directory:

drizzle/
├── 0000_initial_schema.sql        # Initial items table
├── 0001_add_image_hash.sql        # Added imageHash column
├── 0002_add_sealing_date.sql      # Added sealingDate column
├── 0003_add_metal_type_shape.sql  # Added metalType and shape columns
├── 0004_add_product_details.sql   # Added productDetails JSON column
└── meta/
    └── _journal.json              # Migration tracking metadata

Running Migrations

Migrations run automatically on app startup via the SQLiteProvider onInit callback. To create a new migration after modifying the schema:

npx drizzle-kit generate

SQL Import Support

The Metro bundler is configured to support direct .sql file imports:

// metro.config.js
config.resolver.sourceExts.push("sql");

// babel.config.js
plugins: [["inline-import", { extensions: [".sql"] }]];

This allows Drizzle migrations to be imported as inline strings in the JavaScript bundle.

---

Image File Storage

Sealed images are stored in the app's document directory using their SHA256 hash as the filename:

Property	Value
Location	FileSystem.documentDirectory (app-sandboxed)
Naming	SHA256 hash of image content
Access	App-only (not accessible to other apps)
Persistence	Survives app restarts, cleared on app uninstall

Storage Flow

Image API Response
    │
    ▼
expo-crypto SHA256 hash
    │
    ▼
FileSystem.writeAsStringAsync(
  documentDirectory + hash,
  imageBase64,
  { encoding: FileSystem.EncodingType.Base64 }
)
    │
    ▼
Store hash in SQLite items.imageHash

Retrieval

const imagePath = `${FileSystem.documentDirectory}${item.imageHash}`;
const fileExists = await FileSystem.getInfoAsync(imagePath);

if (fileExists.exists) {
  // Load from local cache
  return { uri: imagePath };
} else {
  // Use fallback image based on metalType + shape
  return getFallbackImage(item.metalType, item.shape);
}

---

AsyncStorage (Banner Cache)

Promotional banners use @react-native-async-storage/async-storage for lightweight caching:

Key	Value	TTL
banners_cache	JSON: { data: Banner[], timestamp: number }	24 hours

Cache Strategy

App Opens → Check AsyncStorage
    │
    ├─ Cache < 24h old → Use cached banners
    │
    └─ Cache expired or missing → Fetch from WordPress API
        │
        └─ Save to AsyncStorage with current timestamp

This prevents unnecessary API calls while ensuring banner content stays reasonably fresh.

---

Data Lifecycle

Data	Storage	TTL	Cleared On
Scanned items	SQLite items table	Permanent (until deleted)	Manual delete or app uninstall
Sealed images	Document directory files	Permanent (until deleted)	Item deletion or app uninstall
Promotional banners	AsyncStorage	24 hours	Cache expiry or app data clear
React Query cache	In-memory	Session-based	App restart