Add Notion Database Sync Utilities

Overview

Add utilities for syncing Notion databases to various local formats, enabling offline access, backup, and integration with other tools.

Exposure: All functionality will be available both as Effect API (for programmatic use) and CLI commands (for command-line use).

Proposed Utilities

1. SQLite Sync

Sync Notion database to a local SQLite database with automatic schema mapping.

Effect API:

import { Effect } from 'effect'
import { NotionToSqlite } from '@overeng/notion-effect-sync'

const program = Effect.gen(function* () {
  yield* NotionToSqlite.sync({
    databaseId: 'abc123...',
    sqlitePath: './data/tasks.db',
    tableName: 'tasks',
    // Auto-create table schema from Notion properties
    autoMigrate: true,
    // Sync mode: full | incremental
    mode: 'incremental',
  })
})

CLI:

# One-time sync
notion-sync sqlite abc123... --output ./data/tasks.db --table tasks

# Incremental sync
notion-sync sqlite abc123... --output ./data/tasks.db --mode incremental

# Watch mode
notion-sync sqlite abc123... --output ./data/tasks.db --watch

Features:

• Automatic table creation from Notion schema
• Incremental sync based on last_edited_time
• Type-safe column mapping
• Support for relations (foreign keys)
• Option to preserve raw Notion JSON in a column

---

2. Markdown Sync

Sync Notion pages/databases to local markdown files.

Effect API:

import { Effect } from 'effect'
import { NotionToMarkdown } from '@overeng/notion-effect-sync'

const program = Effect.gen(function* () {
  yield* NotionToMarkdown.syncDatabase({
    databaseId: 'abc123...',
    outputDir: './docs/tasks',
    // Template for filename: {id}, {title}, {Status}, etc.
    filenameTemplate: '{title}.md',
    // Include frontmatter with properties
    includeFrontmatter: true,
    // Sync nested blocks
    recursive: true,
  })
})

CLI:

# Sync database to markdown files
notion-sync markdown abc123... --output ./docs/tasks --filename-template '{title}.md'

# Include frontmatter with properties
notion-sync markdown abc123... --output ./docs --frontmatter

# Watch for changes
notion-sync markdown abc123... --output ./docs --watch

Frontmatter example:

---
id: page-id-123
title: My Task
status: In Progress
due_date: 2025-01-15
tags: [work, urgent]
---

# My Task

Page content here...

---

3. Two-Way Sync

Enable bidirectional sync for supported formats.

Effect API:

import { Effect } from 'effect'
import { NotionTwoWaySync } from '@overeng/notion-effect-sync'

const program = Effect.gen(function* () {
  yield* NotionTwoWaySync.markdown({
    databaseId: 'abc123...',
    localDir: './docs',
    // Conflict resolution: notion-wins | local-wins | manual
    conflictResolution: 'manual',
    // Watch for changes
    watch: true,
  })
})

CLI:

# Two-way markdown sync
notion-sync two-way markdown abc123... --dir ./docs --conflict-resolution manual

# Watch mode with auto-sync
notion-sync two-way markdown abc123... --dir ./docs --watch

Features:

• Detect local file changes and sync to Notion
• Detect Notion changes and sync to local
• Conflict resolution strategies
• Watch mode for continuous sync
• Support for file-based locking

---

4. Export Utilities

Batch export databases to various formats.

Effect API:

import { Effect } from 'effect'
import { NotionExport } from '@overeng/notion-effect-sync'

const program = Effect.gen(function* () {
  // Export to JSON
  yield* NotionExport.toJson({
    databaseId: 'abc123...',
    output: './export/tasks.json',
    pretty: true,
  })

  // Export to CSV
  yield* NotionExport.toCsv({
    databaseId: 'abc123...',
    output: './export/tasks.csv',
    // Flatten nested properties
    flattenRelations: true,
  })
})

CLI:

# Export to JSON
notion-sync export json abc123... --output ./export/tasks.json --pretty

# Export to CSV
notion-sync export csv abc123... --output ./export/tasks.csv --flatten-relations

# Export multiple databases from config
notion-sync export --config .notion-sync.json

---

CLI Structure

All sync functionality will be available via a unified CLI (either as part of notion-effect-schema-gen or a new notion-sync CLI):

# Core sync commands
notion-sync sqlite <database-id> [options]
notion-sync markdown <database-id> [options]
notion-sync two-way <format> <database-id> [options]
notion-sync export <format> <database-id> [options]

# Config-based batch operations
notion-sync --config .notion-sync.json
notion-sync --config .notion-sync.json --watch

# Status and introspection
notion-sync status <database-id>
notion-sync diff <database-id> --local ./data/tasks.db

Config file example (.notion-sync.json):

{
  "syncs": [
    {
      "type": "sqlite",
      "databaseId": "abc123...",
      "output": "./data/tasks.db",
      "mode": "incremental"
    },
    {
      "type": "markdown",
      "databaseId": "def456...",
      "outputDir": "./docs/projects",
      "filenameTemplate": "{title}.md",
      "frontmatter": true
    }
  ]
}

---

Implementation Considerations

Schema Mapping

• Reuse existing schema generation from @overeng/notion-effect-schema-gen
• Support custom property transformers
• Handle complex types (relations, rollups, formulas)

Incremental Sync

• Track last_edited_time for changed pages
• Store sync metadata (last sync timestamp, checksums)
• Efficient queries using Notion API filters

Error Handling

• Graceful handling of API rate limits
• Retry logic with exponential backoff
• Clear error reporting for schema mismatches

CLI Integration

• Use @effect/cli for command-line interface
• Support config file for multiple sync tasks
• Watch mode for continuous sync
• Progress indicators for long-running operations

Effect API Design

• All operations return Effect<Result, Error, Dependencies>
• Proper service dependencies (NotionClient, FileSystem, etc.)
• Composable operations for building custom sync workflows
• Stream-based processing for large datasets

---

Use Cases

1. Offline Access: Work with Notion data without internet connection
2. Backup: Regular automated backups of critical databases
3. Integration: Query Notion data using SQL or consume as markdown in static site generators
4. Version Control: Track changes to Notion pages in git (via markdown)
5. Data Analysis: Use SQL tools to analyze Notion data
6. Migration: Easy export when moving away from Notion

---

Package Structure

Option 1: Single package @overeng/notion-effect-sync

• All sync utilities in one package
• Single CLI: notion-sync
• Simpler dependency management

Option 2: Separate packages

• @overeng/notion-effect-sqlite-sync
• @overeng/notion-effect-markdown-sync
• Each with its own CLI or unified via plugin system

Recommendation: Start with Option 1 for simplicity, can split later if needed.

---

Open Questions

• ✅ Expose via Effect API + CLI (confirmed)
• Should this extend notion-effect-schema-gen CLI or be a separate notion-sync CLI?
• Which sync formats are most valuable initially? (SQLite, Markdown, JSON, CSV, others?)
• Should two-way sync be in scope for v1 or separate v2?
• How to handle binary content (images, files)?

---

Related

• #12 - Rich Text utilities (needed for markdown conversion)
• #13 - Blocks to Markdown converter (needed for page content sync)
• #14 - Recursive block fetching (needed for full page sync)
• #15 - Database Metadata Helpers (needed for relations and options)

Start by reviewing the proposed Effect API and unified CLI structure, then read related issues #12, #13, #14, and #15 for the listed prerequisites. Before implementation, narrow the initial format and scope; this issue is not done until those decisions produce a defined first milestone.