How to Build a Social Interaction Tracker CLI Tool with Node.js and SQLite in 2025

Developers often struggle with tracking personal experiments and habit data outside of their work projects. Whether you're running a 30-day challenge to improve social skills, tracking gym visits, or logging daily interactions, you need a lightweight tool that works from the terminal. This guide shows you how to build a command-line interface (CLI) application using Node.js and SQLite to track social interactions, inspired by a real-world experiment of talking to 35 strangers at the gym over one month.

Why Build a CLI Tool for Social Tracking?

While apps like Notion or spreadsheets work for some people, developers often prefer terminal-based workflows. A custom CLI tool offers several advantages:

• Speed: Log entries in seconds without leaving your terminal
• Privacy: All data stored locally in SQLite database
• Customization: Tailor fields and reports to your specific experiment
• Learning: Practice Node.js, database design, and CLI development

This tutorial walks through building a production-ready interaction tracker that supports:

• Adding daily interaction logs with custom fields
• Querying interactions by date range or criteria
• Generating weekly summary reports
• Exporting data to CSV for analysis

Prerequisites and Tech Stack

Before starting, ensure you have:

• Node.js 18+ installed (check with node --version)
• npm or yarn package manager
• Basic understanding of async/await and Promises
• Familiarity with SQL basics

Tech stack:

• Node.js: Runtime environment
• SQLite3: Lightweight embedded database
• Commander.js: CLI framework for parsing arguments
• Inquirer.js: Interactive prompts for user input
• date-fns: Date manipulation library

Step 1: Initialize the Project Structure

Create a new directory and initialize your Node.js project:

mkdir interaction-tracker
cd interaction-tracker
npm init -y

Install required dependencies:

npm install commander inquirer sqlite3 date-fns
npm install --save-dev @types/node

Create the project structure:

interaction-tracker/
├── bin/
│   └── cli.js
├── src/
│   ├── database.js
│   ├── commands/
│   │   ├── add.js
│   │   ├── list.js
│   │   └── report.js
│   └── models/
│       └── interaction.js
├── data/
│   └── interactions.db
└── package.json

Step 2: Set Up SQLite Database Schema

Create src/database.js to handle database initialization and connections:

const sqlite3 = require('sqlite3').verbose();
const path = require('path');

const DB_PATH = path.join(__dirname, '../data/interactions.db');

class Database {
  constructor() {
    this.db = new sqlite3.Database(DB_PATH, (err) => {
      if (err) {
        console.error('Database connection failed:', err.message);
      }
    });
    this.initialize();
  }

  initialize() {
    const schema = `
      CREATE TABLE IF NOT EXISTS interactions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT NOT NULL,
        location TEXT NOT NULL,
        opening_line TEXT,
        duration_minutes INTEGER,
        outcome TEXT,
        notes TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `;
    
    this.db.run(schema, (err) => {
      if (err) {
        console.error('Schema creation failed:', err.message);
      }
    });
  }

  run(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.run(sql, params, function(err) {
        if (err) reject(err);
        else resolve({ id: this.lastID, changes: this.changes });
      });
    });
  }

  all(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.all(sql, params, (err, rows) => {
        if (err) reject(err);
        else resolve(rows);
      });
    });
  }

  close() {
    return new Promise((resolve, reject) => {
      this.db.close((err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }
}

module.exports = new Database();

This schema matches the experiment structure: tracking date, location (gym), opening line used, conversation duration, outcome, and additional notes.

Step 3: Build the Add Command with Interactive Prompts

Create src/commands/add.js to handle adding new interaction logs:

const inquirer = require('inquirer');
const { format } = require('date-fns');
const db = require('../database');

async function addInteraction(options) {
  const questions = [
    {
      type: 'input',
      name: 'date',
      message: 'Date of interaction (YYYY-MM-DD):',
      default: format(new Date(), 'yyyy-MM-dd'),
      validate: (input) => {
        return /^\d{4}-\d{2}-\d{2}$/.test(input) || 'Invalid date format';
      }
    },
    {
      type: 'input',
      name: 'location',
      message: 'Location:',
      default: 'gym'
    },
    {
      type: 'input',
      name: 'opening_line',
      message: 'Opening line used:',
    },
    {
      type: 'number',
      name: 'duration_minutes',
      message: 'Duration (minutes):',
      default: 5
    },
    {
      type: 'list',
      name: 'outcome',
      message: 'Outcome:',
      choices: ['positive', 'neutral', 'negative', 'no-response']
    },
    {
      type: 'input',
      name: 'notes',
      message: 'Additional notes:',
    }
  ];

  const answers = await inquirer.prompt(questions);
  
  const sql = `
    INSERT INTO interactions (date, location, opening_line, duration_minutes, outcome, notes)
    VALUES (?, ?, ?, ?, ?, ?)
  `;
  
  try {
    const result = await db.run(sql, [
      answers.date,
      answers.location,
      answers.opening_line,
      answers.duration_minutes,
      answers.outcome,
      answers.notes
    ]);
    console.log(`✓ Interaction #${result.id} logged successfully`);
  } catch (err) {
    console.error('Failed to log interaction:', err.message);
  }
}

module.exports = { addInteraction };

Step 4: Create List and Report Commands

Create src/commands/list.js for querying interactions:

const db = require('../database');
const { format } = require('date-fns');

async function listInteractions(options) {
  let sql = 'SELECT * FROM interactions';
  const params = [];
  const conditions = [];

  if (options.date) {
    conditions.push('date = ?');
    params.push(options.date);
  }

  if (options.outcome) {
    conditions.push('outcome = ?');
    params.push(options.outcome);
  }

  if (conditions.length > 0) {
    sql += ' WHERE ' + conditions.join(' AND ');
  }

  sql += ' ORDER BY date DESC';

  try {
    const interactions = await db.all(sql, params);
    
    if (interactions.length === 0) {
      console.log('No interactions found.');
      return;
    }

    console.table(interactions.map(i => ({
      ID: i.id,
      Date: i.date,
      Location: i.location,
      Duration: `${i.duration_minutes}m`,
      Outcome: i.outcome
    })));
  } catch (err) {
    console.error('Query failed:', err.message);
  }
}

module.exports = { listInteractions };

Step 5: Implement Weekly Summary Report

Create src/commands/report.js to generate analytics:

const db = require('../database');

async function generateReport() {
  const queries = {
    total: 'SELECT COUNT(*) as count FROM interactions',
    avgDuration: 'SELECT AVG(duration_minutes) as avg FROM interactions',
    byOutcome: 'SELECT outcome, COUNT(*) as count FROM interactions GROUP BY outcome',
    byWeek: `
      SELECT 
        strftime('%Y-W%W', date) as week,
        COUNT(*) as count,
        AVG(duration_minutes) as avg_duration
      FROM interactions
      GROUP BY week
      ORDER BY week DESC
    `
  };

  try {
    const [total] = await db.all(queries.total);
    const [avgDuration] = await db.all(queries.avgDuration);
    const byOutcome = await db.all(queries.byOutcome);
    const byWeek = await db.all(queries.byWeek);

    console.log('\n📊 Interaction Report\n');
    console.log(`Total interactions: ${total.count}`);
    console.log(`Average duration: ${avgDuration.avg.toFixed(1)} minutes\n`);
    
    console.log('By outcome:');
    console.table(byOutcome);
    
    console.log('\nBy week:');
    console.table(byWeek);
  } catch (err) {
    console.error('Report generation failed:', err.message);
  }
}

module.exports = { generateReport };

Step 6: Configure the CLI Entry Point

Create bin/cli.js as your main executable:

#!/usr/bin/env node

const { Command } = require('commander');
const { addInteraction } = require('../src/commands/add');
const { listInteractions } = require('../src/commands/list');
const { generateReport } = require('../src/commands/report');

const program = new Command();

program
  .name('interact')
  .description('Track social interactions from the command line')
  .version('1.0.0');

program
  .command('add')
  .description('Log a new interaction')
  .action(addInteraction);

program
  .command('list')
  .description('List all interactions')
  .option('-d, --date <date>', 'Filter by date (YYYY-MM-DD)')
  .option('-o, --outcome <outcome>', 'Filter by outcome')
  .action(listInteractions);

program
  .command('report')
  .description('Generate summary report')
  .action(generateReport);

program.parse(process.argv);

Update package.json to make the CLI globally accessible:

{
  "name": "interaction-tracker",
  "version": "1.0.0",
  "bin": {
    "interact": "./bin/cli.js"
  },
  "scripts": {
    "start": "node bin/cli.js"
  }
}

Install globally for development:

npm link

Common Issues and Solutions

Issue 1: SQLite Database Locked Error

Problem: Multiple concurrent writes cause "database is locked" errors.

Solution: Implement a connection pool or use PRAGMA journal_mode=WAL for better concurrency:

this.db.run('PRAGMA journal_mode=WAL');

Issue 2: Date Timezone Inconsistencies

Problem: Dates display incorrectly across timezones.

Solution: Always store dates in ISO format and use date-fns with explicit timezone handling:

const { formatInTimeZone } = require('date-fns-tz');
const userTimezone = Intl.DateTimeFormat().resolvedOptions().timeZone;

Comparison: CLI Tools vs Web Apps for Personal Tracking

| Feature | CLI Tool | Web App | Mobile App | |---------|----------|---------|------------| | Setup Time | 1-2 hours | 8-12 hours | 20+ hours | | Data Privacy | Local only | Requires backend | Cloud sync required | | Developer Workflow | Seamless | Context switch | Not accessible | | Customization | Full control | Limited by framework | Platform restrictions | | Data Export | Direct SQL access | API dependent | Often restricted | | Offline Support | Always works | Service worker needed | Platform dependent |

Extending Your Tracker

Once you have the basic tracker working, consider these enhancements:

1. CSV Export: Add csv-writer package to export data for spreadsheet analysis
2. Visualization: Integrate asciichart for terminal-based graphs
3. Reminders: Use node-cron to send daily logging reminders
4. Backup: Implement automatic SQLite backup to cloud storage with aws-sdk
5. Multi-user: Add user accounts if sharing with accountability partners

Deployment and Distribution

To share your CLI tool with others:

1. Publish to npm: npm publish
2. Create a GitHub release with pre-built binaries using pkg
3. Add to Homebrew for macOS distribution
4. Deploy a web version using Vercel or Render if users prefer browser access

Conclusion

Building a custom CLI tool for tracking personal experiments demonstrates practical Node.js development skills while solving a real problem. This interaction tracker takes approximately 2-3 hours to build and provides a foundation for any data logging project. The combination of SQLite for persistence, Commander.js for CLI structure, and Inquirer.js for interactive prompts creates a professional-grade tool that integrates seamlessly into developer workflows.

The original 35-stranger experiment required consistent daily logging—exactly what this tool facilitates. Whether you're tracking social interactions, coding sessions, or any other measurable habit, this pattern scales to handle thousands of entries with sub-second query performance.

By mastering CLI development with Node.js and SQLite, you gain transferable skills applicable to build tools, automation scripts, and internal developer tooling that companies like Vercel and DigitalOcean use extensively in their infrastructure.