Skip to main content
toni uses SQLite for local-first data storage. The database contains three main tables: restaurants, visits, and want_to_visit, all connected through foreign key relationships.

​
Database Location

By default, toni stores its database at ~/.toni/toni.db. You can override this with the --db flag: 
toni --db /path/to/custom/database.db

​
Schema Overview

The database schema is automatically initialized when you first run toni. All tables use auto-incrementing integer primary keys and timestamps.

​
Restaurants Table

Stores restaurant information, either manually entered or imported from Yelp. 
CREATE TABLE IF NOT EXISTS restaurants (
    id           INTEGER PRIMARY KEY,
    name         TEXT NOT NULL,
    address      TEXT,
    city         TEXT,
    neighborhood TEXT,
    cuisine      TEXT,
    price_range  TEXT CHECK(price_range IN ('$','$$','$$$','$$$$') OR price_range IS NULL),
    latitude     REAL,
    longitude    REAL,
    place_id     TEXT,
    created_at   TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);
​
id
INTEGER
required
Auto-incrementing primary key
​
name
TEXT
required
Restaurant name (required field)
​
address
TEXT
Street address (optional)
​
city
TEXT
City name (optional)
​
neighborhood
TEXT
Neighborhood or district (optional)
​
cuisine
TEXT
Cuisine type (e.g., “Italian”, “Japanese”) (optional)
​
price_range
TEXT
Price indicator: $, $$, $$$, or $$$$ (optional, validated by CHECK constraint)
​
latitude
REAL
GPS latitude coordinate (optional)
​
longitude
REAL
GPS longitude coordinate (optional)
​
place_id
TEXT
Yelp business ID for API integration (optional)
​
created_at
TEXT
required
ISO 8601 timestamp (automatically set to current time)

​
Visits Table

Records individual restaurant visits with ratings and notes. 
CREATE TABLE IF NOT EXISTS visits (
    id            INTEGER PRIMARY KEY,
    restaurant_id INTEGER NOT NULL REFERENCES restaurants(id),
    visited_on    TEXT,
    rating        REAL CHECK(rating BETWEEN 1 AND 10 OR rating IS NULL),
    notes         TEXT,
    would_return  INTEGER CHECK(would_return IN (0,1) OR would_return IS NULL),
    created_at    TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);
​
id
INTEGER
required
Auto-incrementing primary key
​
restaurant_id
INTEGER
required
Foreign key reference to restaurants.id (cascades on delete)
​
visited_on
TEXT
Visit date in ISO 8601 format: YYYY-MM-DD (optional)
​
rating
REAL
Numeric rating from 1.0 to 10.0 (optional, validated by CHECK constraint)
​
notes
TEXT
Free-form notes about the visit (optional)
​
would_return
INTEGER
Boolean flag: 1 for yes, 0 for no, NULL for unspecified (optional)
​
created_at
TEXT
required
ISO 8601 timestamp (automatically set to current time)

​
Want to Visit Table

Tracks restaurants you want to visit with priority levels. 
CREATE TABLE IF NOT EXISTS want_to_visit (
    id            INTEGER PRIMARY KEY,
    restaurant_id INTEGER NOT NULL REFERENCES restaurants(id),
    notes         TEXT,
    priority      INTEGER CHECK(priority BETWEEN 1 AND 5 OR priority IS NULL),
    created_at    TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);
​
id
INTEGER
required
Auto-incrementing primary key
​
restaurant_id
INTEGER
required
Foreign key reference to restaurants.id (cascades on delete)
​
notes
TEXT
Why you want to visit, recommendations, etc. (optional)
​
priority
INTEGER
Priority level from 1 to 5, where 5 is highest priority (optional, validated by CHECK constraint)
​
created_at
TEXT
required
ISO 8601 timestamp (automatically set to current time)

​
Database Indexes

toni creates indexes for common query patterns: 
CREATE INDEX IF NOT EXISTS idx_visits_restaurant_id ON visits(restaurant_id);
CREATE INDEX IF NOT EXISTS idx_visits_visited_on ON visits(visited_on DESC);
CREATE INDEX IF NOT EXISTS idx_want_to_visit_restaurant_id ON want_to_visit(restaurant_id);
CREATE INDEX IF NOT EXISTS idx_want_to_visit_priority ON want_to_visit(priority DESC);
  • idx_visits_restaurant_id: Optimizes lookups of all visits for a specific restaurant
  • idx_visits_visited_on: Enables fast sorting by visit date (descending)
  • idx_want_to_visit_restaurant_id: Optimizes want-to-visit lookups by restaurant
  • idx_want_to_visit_priority: Enables fast sorting by priority (descending)

​
Go Type Definitions

toni uses strongly-typed Go structs for database operations.

​
Core Types

// Restaurant represents a restaurant entity
type Restaurant struct {
    ID           int64
    Name         string
    Address      string
    City         string
    Neighborhood string
    Cuisine      string
    PriceRange   string     // "$", "$$", "$$$", or "$$$$"
    Latitude     *float64   // pointer allows NULL
    Longitude    *float64   // pointer allows NULL
    PlaceID      string     // Yelp business ID
    CreatedAt    time.Time
}

// Visit represents a visit to a restaurant
type Visit struct {
    ID           int64
    RestaurantID int64
    VisitedOn    string     // ISO 8601 date (YYYY-MM-DD)
    Rating       *float64   // pointer allows NULL, value 1.0-10.0
    Notes        string
    WouldReturn  *bool      // pointer allows NULL
    CreatedAt    time.Time
}

// WantToVisit represents a place the user wants to visit
type WantToVisit struct {
    ID           int64
    RestaurantID int64
    Notes        string
    Priority     *int       // pointer allows NULL, value 1-5
    CreatedAt    time.Time
}

​
Input Types

Used when creating new records: 
// NewRestaurant represents data for creating a restaurant
type NewRestaurant struct {
    Name         string
    Address      string
    City         string
    Neighborhood string
    Cuisine      string
    PriceRange   string
    Latitude     *float64
    Longitude    *float64
    PlaceID      string
}

// NewVisit represents data for creating a visit
type NewVisit struct {
    RestaurantID int64
    VisitedOn    string
    Rating       *float64
    Notes        string
    WouldReturn  *bool
}

// NewWantToVisit represents data for creating a want_to_visit entry
type NewWantToVisit struct {
    RestaurantID int64
    Notes        string
    Priority     *int
}

​
View Types

Used for displaying joined data: 
// VisitRow represents a visit with joined restaurant data for list display
type VisitRow struct {
    ID             int64
    VisitedOn      string
    RestaurantName string
    City           string
    Address        string
    PriceRange     string
    Rating         *float64
    WouldReturn    *bool
    Notes          string
    RestaurantID   int64
}

// RestaurantRow represents a restaurant with aggregate stats
type RestaurantRow struct {
    ID           int64
    Name         string
    Address      string
    City         string
    Neighborhood string
    Cuisine      string
    PriceRange   string
    AvgRating    *float64    // average of all visit ratings
    VisitCount   int         // total number of visits
    LastVisit    string      // date of most recent visit
}

// WantToVisitRow represents want_to_visit with joined restaurant data
type WantToVisitRow struct {
    ID             int64
    RestaurantName string
    Address        string
    City           string
    Neighborhood   string
    Cuisine        string
    PriceRange     string
    Priority       *int
    Notes          string
    RestaurantID   int64
    CreatedAt      time.Time
}

​
Referential Integrity

Deleting a restaurant cascades to all associated visits and want-to-visit entries. This operation cannot be undone (though toni provides an undo feature during the session).
When you delete a restaurant, toni executes a transaction: 
// From internal/db/restaurants.go
func DeleteRestaurant(db *sql.DB, id int64) error {
    tx, err := db.Begin()
    if err != nil {
        return fmt.Errorf("failed to begin transaction: %w", err)
    }
    defer tx.Rollback()

    // Delete all visits for this restaurant
    if _, err := tx.Exec("DELETE FROM visits WHERE restaurant_id = ?", id); err != nil {
        return fmt.Errorf("failed to delete visits: %w", err)
    }

    // Delete all want_to_visit entries for this restaurant
    if _, err := tx.Exec("DELETE FROM want_to_visit WHERE restaurant_id = ?", id); err != nil {
        return fmt.Errorf("failed to delete want_to_visit entries: %w", err)
    }

    // Delete the restaurant itself
    if _, err := tx.Exec("DELETE FROM restaurants WHERE id = ?", id); err != nil {
        return fmt.Errorf("failed to delete restaurant: %w", err)
    }

    if err := tx.Commit(); err != nil {
        return fmt.Errorf("failed to commit transaction: %w", err)
    }

    return nil
}

​
Null Handling

Go uses pointers to represent nullable SQL fields: 
// Reading a nullable rating field
var rating sql.NullFloat64
err := db.QueryRow(query, id).Scan(&rating)

var visit Visit
if rating.Valid {
    r := rating.Float64
    visit.Rating = &r  // convert to *float64
} else {
    visit.Rating = nil // NULL in database
}

var latitude interface{}
if restaurant.Latitude != nil {
    latitude = *restaurant.Latitude
}
// latitude will be nil (SQL NULL) if the pointer was nil

_, err := db.Exec(query, name, latitude, longitude)

​
Schema Initialization

The schema is created automatically when you first run toni: 
// From internal/db/db.go
func Open(dbPath string) (*sql.DB, error) {
    db, err := sql.Open("sqlite", dbPath)
    if err != nil {
        return nil, fmt.Errorf("failed to open database: %w", err)
    }

    if err := db.Ping(); err != nil {
        db.Close()
        return nil, fmt.Errorf("failed to ping database: %w", err)
    }

    // Execute schema creation (CREATE TABLE IF NOT EXISTS)
    if _, err := db.Exec(schema); err != nil {
        db.Close()
        return nil, fmt.Errorf("failed to initialize schema: %w", err)
    }

    return db, nil
}
The CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS statements make schema initialization idempotent. Running toni multiple times on the same database is safe.

Build docs developers (and LLMs) love

Get started for freeTalk to us