# SQLite Integration for SessionScribe

This directory contains the SQLite database integration for the SessionScribe app.

## Files

- `DatabaseManager.swift`: The main database manager that provides an interface for the app to interact with the database.
- `SQLiteManager.swift`: The SQLite implementation that handles the low-level SQLite operations.

## Setup Instructions

To use SQLite in the project, follow these steps:

1. Open your project in Xcode
2. Select your project in the Project Navigator
3. Select your target
4. Go to the 'Build Phases' tab
5. Expand 'Link Binary With Libraries'
6. Click the '+' button
7. Search for 'libsqlite3.tbd'
8. Select it and click 'Add'

## Required Entitlements

To access the user's main Documents folder, the app requires the following entitlements:

```xml
<key>com.apple.security.files.home-relative-path.read-write</key>
<array>
    <string>/Documents/SessionScribe/</string>
</array>
```

These entitlements are already added to the `SessionScribe.entitlements` file.

## Database Location

The SQLite database is stored in the user's main Documents folder in a subdirectory called "SessionScribe":

```
~/Documents/SessionScribe/notes.sqlite
```

This location makes it easier for users to back up their data manually if needed and is outside the app's sandbox.

### Fallback Mechanism

If the app doesn't have permission to access the user's main Documents folder (for example, if the required entitlements are not properly set up), it will fall back to storing the database in the app's sandboxed Documents directory:

```
~/Library/Containers/[app-bundle-id]/Data/Documents/SessionScribe/notes.sqlite
```

This ensures that the app will still function correctly even if it doesn't have the necessary permissions to access the user's main Documents folder.

## Database Structure

The SQLite database contains a single table called `notes` with the following schema:

```sql
CREATE TABLE IF NOT EXISTS notes(
    id TEXT PRIMARY KEY,
    title TEXT,
    raw_transcript TEXT,
    formatted_notes TEXT,
    recording_path TEXT,
    created_at TEXT,
    updated_at TEXT
);
```

## Migration

The `DatabaseManager` includes functionality to migrate existing in-memory notes to the SQLite database. This migration happens automatically when the app is first run after the SQLite integration is added.

## Error Handling

The SQLite integration includes comprehensive error handling to provide meaningful error messages when database operations fail.

## Usage

The app continues to use the `DatabaseManager` class as before, with no changes required to the rest of the codebase. The `DatabaseManager` now uses SQLite for persistent storage instead of in-memory storage.
