# Nis

Per-user personal CRM backed by SQLite — tracks contacts, companies, websites, activities, and notes with full-text search and a sales pipeline.

---

## Table of Contents

1. [Overview](#overview)
2. [Features](#features)
3. [Database Schema](#database-schema)
4. [Usage](#usage)
5. [API Reference](#api-reference)
6. [Internals](#internals)
7. [Related Documentation](#related-documentation)

---

## Overview

`Nis` is a pure functional module (no GenServer) that provides a per-user CRM system. Each user gets their own SQLite database (`nis_{username}.db`) via Sqler, ensuring complete data isolation between users.

The module manages six entity types — contacts, companies, websites, activities, notes, and tasks — with cross-entity features like full-text search (FTS5), tag-based filtering, stage-based pipeline tracking, and automatic follow-up scheduling.

Key design decisions:
- **Per-user isolation** — each user's CRM lives in a separate SQLite file
- **Lazy database init** — `ensure_db/1` starts the Sqler instance on first access with a 30-minute idle timeout
- **FTS indexing** — every entity is indexed in an FTS5 virtual table on create/update
- **Cascading deletes** — deleting an entity removes its notes, activity links, and FTS entries
- **Stage change auditing** — stage transitions on contacts/companies auto-create tagged notes
- **Optimistic locking** — all updates go through `Sqler.update/3` with `updated_at`

## Features

| Feature | Description |
|---------|-------------|
| Contacts CRUD | Create, read, update, delete contacts with rich metadata |
| Companies CRUD | Track organizations with deal values and pipeline stages |
| Websites CRUD | Link URLs to contacts and companies |
| Activities CRUD | Recurring activities with entity linking |
| Notes CRUD | Timestamped notes attached to any entity type |
| Full-text search | FTS5-powered search across all entity types |
| Pipeline view | Contacts and companies grouped by sales stage |
| Follow-ups | Due/overdue items across all entity types |
| Birthdays | Contacts with birthdays in the next 7 days |
| Stats | Activity metrics over a time range |
| Tag filtering | JSON-based tags with SQLite `json_each` queries |
| Auto-reschedule | Updating `last_contact_at` recalculates `next_contact_at` based on `contact_every` |
| Bulk stage update | Move multiple entities to a new pipeline stage at once |

## Database Schema

Each user's database contains these tables:

| Table | Purpose | Key Fields |
|-------|---------|------------|
| `nis_contacts` | People | name, company_id, priority, stage, contact_every, birthday |
| `nis_companies` | Organizations | name, deal_value, expected_close_at, stage |
| `nis_websites` | URLs | url, purpose, company_id, contact_id |
| `nis_activities` | Trackable actions | title, status, recurrence, links |
| `nis_activity_links` | Activity ↔ entity M2M | activity_id, entity_type, entity_id |
| `nis_notes` | Timestamped notes | entity_type, entity_id, content, channel, is_next_contact |
| `nis_tasks` | Task items | title, priority, status, due_at, after_at, recur |
| `nis_task_links` | Task ↔ entity M2M | task_id, entity_type, entity_id |
| `nis_fts` | FTS5 virtual table | entity_type, entity_id, text |

> IDs are millisecond timestamps (Sqler convention). `id / 1000` gives the Unix creation time in seconds.

## Usage

### Setting Up a User Database

```elixir
Nis.ensure_db("james")
# Creates nis_james.db if it doesn't exist, starts Sqler process
```

### Contacts

```elixir
{:ok, id} = Nis.create_contact("james", %{
  name: "Bob Smith",
  company_id: 123,
  priority: 5,
  stage: "outreach",
  contact_every: "weekly",
  tags: ["client", "vip"]
})

{:ok, contact} = Nis.get_contact("james", id)

contacts = Nis.list_contacts("james", %{stage: "outreach", priority_min: 3, limit: 50})
```

### Companies

```elixir
{:ok, id} = Nis.create_company("james", %{
  name: "Acme Corp",
  deal_value: 50000,
  stage: "demo",
  tags: ["enterprise"]
})

companies = Nis.list_companies("james", %{tag: "enterprise", sort: "priority"})
```

### Full-Text Search

```elixir
results = Nis.search("james", "acme OR enterprise")
# => [%{"entity_type" => "company", "entity_id" => 123, "snippet" => "...", "name" => "Acme Corp"}]
```

### Pipeline

```elixir
stages = Nis.pipeline("james")
# => [%{"stage" => "outreach", "entities" => [...], "total_value" => 50000, "count" => 3}, ...]
```

### Follow-ups

```elixir
%{"overdue" => overdue, "today" => today, "upcoming" => upcoming} = Nis.followups("james")
```

### Log an Interaction

```elixir
Nis.log_interaction("james", %{
  contact_id: 42,
  content: "Discussed pricing proposal",
  channel: "phone",
  next_action: "Send follow-up email",
  next_action_due: 1709078400
})
```

## API Reference

### `ensure_db/1`

Ensures the user's Sqler database process is running. Starts it if not.

**Parameters:**
- `username` (string) — the user's username

**Returns:** `:ok`

```elixir
Nis.ensure_db("james")
```

### `setup_database/1`

Creates all tables, indexes, and the FTS5 virtual table for a user.

**Parameters:**
- `username` (string)

**Returns:** `:ok`

```elixir
Nis.setup_database("james")
```

### `db/1`

Returns the registered name for a user's Sqler process.

**Parameters:**
- `username` (string)

**Returns:** atom — e.g. `:nis_db_james`

```elixir
db_name = Nis.db("james")
# => :nis_db_james
```

### `list_contacts/2`

List contacts with optional filters and sorting.

**Parameters:**
- `username` (string)
- `filters` (map, optional) — supported keys: `:q`, `:tag`, `:sort`, `:stage`, `:priority_min`, `:company_id`, `:limit`, `:last_contact_before`, `:next_contact_before`

**Returns:** list of contact maps

```elixir
Nis.list_contacts("james", %{stage: "outreach", tag: "vip", limit: 20})
```

### `get_contact/2`

Fetch a single contact by ID.

**Parameters:**
- `username` (string)
- `id` (integer)

**Returns:** `{:ok, map}` or `{:error, :not_found}`

```elixir
{:ok, contact} = Nis.get_contact("james", 1709000000000)
```

### `create_contact/2`

Create a new contact. Auto-indexes in FTS.

**Parameters:**
- `username` (string)
- `params` (map) — requires `:name`, optional: `:nickname`, `:company_id`, `:background`, `:location`, `:address`, `:contact_medium`, `:relation`, `:birthday`, `:priority`, `:stage`, `:contact_every`, `:referred_by`, `:interests`, `:tags`

**Returns:** `{:ok, id}` or `{:error, reason}`

```elixir
{:ok, id} = Nis.create_contact("james", %{name: "Alice", priority: 3, tags: ["friend"]})
```

### `update_contact/3`

Update a contact with optimistic locking. Creates a stage change note if stage changes. Auto-reschedules `next_contact_at` if `last_contact_at` changes and `contact_every` is set.

**Parameters:**
- `username` (string)
- `id` (integer)
- `params` (map)

**Returns:** `{:ok, updated_at}` or `{:error, reason}`

```elixir
{:ok, _} = Nis.update_contact("james", id, %{stage: "follow_up", priority: 7})
```

### `delete_contact/2`

Delete a contact. Cascades: removes notes, activity links, FTS entry, and nullifies website references.

**Parameters:**
- `username` (string)
- `id` (integer)

**Returns:** `{:ok, count}` or `{:error, reason}`

```elixir
{:ok, 1} = Nis.delete_contact("james", id)
```

### `list_companies/2`

List companies with optional filters.

**Parameters:**
- `username` (string)
- `filters` (map, optional) — keys: `:q`, `:tag`, `:sort`, `:stage`, `:priority_min`, `:limit`, `:last_contact_before`, `:next_contact_before`

**Returns:** list of company maps

```elixir
Nis.list_companies("james", %{stage: "demo", sort: "priority"})
```

### `get_company/2`

Fetch a company by ID.

**Returns:** `{:ok, map}` or `{:error, :not_found}`

```elixir
{:ok, company} = Nis.get_company("james", id)
```

### `create_company/2`

Create a company. Auto-indexes in FTS.

**Parameters:**
- `username` (string)
- `params` (map) — requires `:name`, optional: `:location`, `:purpose`, `:interest`, `:priority`, `:stage`, `:deal_value`, `:expected_close_at`, `:tags`

**Returns:** `{:ok, id}`

```elixir
{:ok, id} = Nis.create_company("james", %{name: "TechCo", deal_value: 25000, stage: "research"})
```

### `update_company/3`

Update a company with optimistic locking. Creates stage change note on stage transitions.

**Returns:** `{:ok, updated_at}` or `{:error, reason}`

```elixir
{:ok, _} = Nis.update_company("james", id, %{stage: "closed_won"})
```

### `delete_company/2`

Delete a company. Cascades: nullifies contact and website references, removes notes, activity links, FTS entry.

**Returns:** `{:ok, count}`

```elixir
{:ok, 1} = Nis.delete_company("james", id)
```

### `list_websites/2`

List websites with optional filters.

**Parameters:**
- `username` (string)
- `filters` (map, optional) — keys: `:q`, `:tag`, `:sort`, `:company_id`, `:contact_id`, `:limit`

**Returns:** list of website maps

```elixir
Nis.list_websites("james", %{company_id: 123})
```

### `get_website/2`

Fetch a website by ID.

**Returns:** `{:ok, map}` or `{:error, :not_found}`

```elixir
{:ok, website} = Nis.get_website("james", id)
```

### `create_website/2`

Create a website entry.

**Parameters:**
- `username` (string)
- `params` (map) — requires `:url`, optional: `:purpose`, `:company_id`, `:contact_id`, `:tags`

**Returns:** `{:ok, id}`

```elixir
{:ok, id} = Nis.create_website("james", %{url: "https://example.com", company_id: 123})
```

### `update_website/3`

Update a website with optimistic locking.

**Returns:** `{:ok, updated_at}` or `{:error, reason}`

```elixir
{:ok, _} = Nis.update_website("james", id, %{purpose: "Landing page"})
```

### `delete_website/2`

Delete a website. Cascades notes, activity links, FTS.

**Returns:** `{:ok, count}`

```elixir
{:ok, 1} = Nis.delete_website("james", id)
```

### `list_activities/2`

List activities with optional filters. Supports entity-type filtering via JOIN.

**Parameters:**
- `username` (string)
- `filters` (map, optional) — keys: `:q`, `:tag`, `:sort`, `:status`, `:entity_type`, `:entity_id`, `:limit`, `:completed_after`, `:completed_before`

**Returns:** list of activity maps

```elixir
Nis.list_activities("james", %{status: "open", entity_type: "contact", entity_id: 42})
```

### `get_activity/2`

Fetch an activity by ID, including its entity links.

**Returns:** `{:ok, map}` (with `"links"` key) or `{:error, :not_found}`

```elixir
{:ok, activity} = Nis.get_activity("james", id)
# activity["links"] => [%{"activity_id" => ..., "entity_type" => "contact", "entity_id" => 42}]
```

### `create_activity/2`

Create an activity with optional entity links.

**Parameters:**
- `username` (string)
- `params` (map) — requires `:title`, optional: `:description`, `:status`, `:recurrence`, `:tags`, `:links` (list of `%{entity_type, entity_id}`)

**Returns:** `{:ok, id}`

```elixir
{:ok, id} = Nis.create_activity("james", %{
  title: "Weekly sync",
  recurrence: "weekly",
  links: [%{entity_type: "contact", entity_id: 42}]
})
```

### `update_activity/3`

Update an activity. If `:links` is provided, replaces all existing links.

**Returns:** `{:ok, updated_at}` or `{:error, reason}`

```elixir
{:ok, _} = Nis.update_activity("james", id, %{status: "done"})
```

### `complete_activity/2`

Complete an activity. If it has a recurrence, auto-creates the next instance with the next due date.

**Returns:** `{:ok, %{completed_id: id}}` or `{:ok, %{completed_id: id, next_id: next_id}}`

```elixir
{:ok, %{completed_id: 123, next_id: 456}} = Nis.complete_activity("james", 123)
```

### `delete_activity/2`

Delete an activity. Cascades notes, links, FTS.

**Returns:** `{:ok, count}`

```elixir
{:ok, 1} = Nis.delete_activity("james", id)
```

### `list_notes/2`

List notes with optional filters. Enriches each note with `parent_next_contact_at`.

**Parameters:**
- `username` (string)
- `filters` (map, optional) — keys: `:entity_type`, `:entity_id`, `:tag`, `:limit`, `:since`

**Returns:** list of note maps

```elixir
Nis.list_notes("james", %{entity_type: "contact", entity_id: 42, limit: 20})
```

### `get_note/2`

Fetch a note by ID, enriched with parent's `next_contact_at`.

**Returns:** `{:ok, map}` or `{:error, :not_found}`

```elixir
{:ok, note} = Nis.get_note("james", id)
```

### `create_note/2`

Create a note. If `is_next_contact: 1`, clears the previous next-action note for the same entity and updates the parent's `next_contact_note_id` and optionally `next_contact_at`.

**Parameters:**
- `username` (string)
- `params` (map) — optional: `:entity_type`, `:entity_id`, `:content`, `:reason`, `:url`, `:channel`, `:tags`, `:is_next_contact`, `:next_contact_at`

**Returns:** `{:ok, id}`

```elixir
{:ok, id} = Nis.create_note("james", %{
  entity_type: "contact",
  entity_id: 42,
  content: "Discussed pricing",
  channel: "phone",
  is_next_contact: 1,
  next_contact_at: 1709164800
})
```

### `update_note/3`

Update a note with optimistic locking.

**Returns:** `{:ok, updated_at}` or `{:error, reason}`

```elixir
{:ok, _} = Nis.update_note("james", id, %{content: "Updated note text"})
```

### `delete_note/2`

Delete a note. If it was the `last_contact_note_id` or `next_contact_note_id` on its parent entity, clears those references.

**Returns:** `{:ok, count}`

```elixir
{:ok, 1} = Nis.delete_note("james", id)
```

### `search/2`

Full-text search across all entity types using FTS5.

**Parameters:**
- `username` (string)
- `query_text` (string) — FTS5 query syntax (supports `OR`, `AND`, `NOT`, phrase matching)

**Returns:** list of `%{"entity_type", "entity_id", "snippet", "name"}`

```elixir
results = Nis.search("james", "acme enterprise")
```

### `due/1`

Get all entities with overdue `next_contact_at` (or `due_at` for tasks).

**Returns:** map with keys `"contacts"`, `"companies"`, `"websites"`, `"activities"`, `"tasks"`

```elixir
%{"contacts" => overdue_contacts, "tasks" => overdue_tasks} = Nis.due("james")
```

### `followups/1`

Get follow-up items grouped into overdue, today, and upcoming (next 4 days).

**Returns:** `%{"overdue" => [...], "today" => [...], "upcoming" => [...], "counts" => %{...}}`

```elixir
followups = Nis.followups("james")
```

### `birthdays/1`

Get contacts with birthdays in the next 7 days.

**Parameters:**
- `username` (string)

**Returns:** list of contact maps

```elixir
Nis.birthdays("james")
```

### `pipeline/1`

Get contacts and companies grouped by sales pipeline stage.

**Returns:** list of `%{"stage" => stage, "entities" => [...], "total_value" => n, "count" => n}`

Stages are ordered: first_contact → research → outreach → follow_up → demo → closed_won → closed_lost.

```elixir
stages = Nis.pipeline("james")
```

### `company_contacts/2`

Get all contacts belonging to a company.

**Parameters:**
- `username` (string)
- `company_id` (integer)

**Returns:** list of contact maps sorted by priority DESC

```elixir
contacts = Nis.company_contacts("james", company_id)
```

### `log_interaction/2`

Log an interaction with a contact — creates a note, updates `last_contact_at`, and optionally creates a next-action note.

**Parameters:**
- `username` (string)
- `params` (map) — requires `:contact_id`, `:content`; optional: `:channel`, `:tags`, `:next_action`, `:next_action_due`

**Returns:** `{:ok, note_id}` or `{:ok, %{note_id: id, next_note_id: id}}`

```elixir
{:ok, %{note_id: 1, next_note_id: 2}} = Nis.log_interaction("james", %{
  contact_id: 42,
  content: "Demo call",
  channel: "video",
  next_action: "Send proposal",
  next_action_due: 1709164800
})
```

### `stats/3`

Get CRM activity statistics for a time range.

**Parameters:**
- `username` (string)
- `since` (integer) — Unix seconds start
- `until_at` (integer) — Unix seconds end

**Returns:** map with keys `"notes_created"`, `"activities_completed"`, `"contacts_added"`, `"overdue_count"`, `"stage_changes"`

```elixir
{:ok, stats} = Nis.stats("james", 1708992000, 1709078400)
```

### `bulk_stage_update/3`

Move multiple entities to a new pipeline stage.

**Parameters:**
- `username` (string)
- `stage` (string)
- `entities` (list) — each `%{entity_type: "contact"|"company", entity_id: id}`

**Returns:** list of result maps

```elixir
results = Nis.bulk_stage_update("james", "follow_up", [
  %{entity_type: "contact", entity_id: 42},
  %{entity_type: "company", entity_id: 99}
])
```

### `index_fts/4`

Index (or re-index) an entity in the FTS5 table.

**Parameters:**
- `username` (string)
- `entity_type` (string)
- `entity_id` (integer)
- `text` (string)

```elixir
Nis.index_fts("james", "contact", 42, "Bob Smith client vip")
```

### `delete_fts/3`

Remove an entity's FTS index entry.

```elixir
Nis.delete_fts("james", "contact", 42)
```

### `build_task_fts_text/1`

Build FTS text from a task map. Used by NisTask for indexing.

**Returns:** string

```elixir
text = Nis.build_task_fts_text(%{"title" => "Fix bug", "tags" => ["urgent"]})
```

### `tasks_table/0`, `task_links_table/0`, `task_fields/0`, `task_json_fields/0`, `task_link_fields/0`

Accessors for task table constants, used by the `NisTask` module.

```elixir
Nis.tasks_table()       # => "nis_tasks"
Nis.task_fields()       # => ["id", "updated_at", "title", ...]
```

## Internals

### Filter Pipeline

List functions build WHERE clauses through a composable pipeline:

```
build_*_where(filters)
  │
  ├─ maybe_add_tag_filter    — JSON tag matching via json_each
  ├─ maybe_add_filter        — simple equality/comparison clauses
  ├─ maybe_add_fts_filter    — subquery into FTS table for text search
  └─ finalize_where          — join clauses with AND, reverse params
```

### Contact Every Auto-Reschedule

When `last_contact_at` is updated on a contact with `contact_every` set, `next_contact_at` is automatically recalculated:

| contact_every | Offset |
|---------------|--------|
| weekly | 7 days |
| biweekly | 14 days |
| monthly | 30 days |
| quarterly | 90 days |

### Stage Change Auditing

When a contact or company's `stage` field changes, a note is automatically created with:
- `reason`: `"old_stage -> new_stage"`
- `tags`: `["stage_change"]`

This provides a full audit trail queryable via the notes API.

## Related Documentation

- [NisTask](nis_task.md) — task management within the NIS database
- [NisRest](nis_rest.md) — REST API bridge for NIS
- [NisTaskRest](nis_task_rest.md) — REST API bridge for NIS tasks
- [NIS User Guide](nis-user-guide.md) — end-user guide
- [Sqler](sqler.md) — underlying database layer

---

*Source: `lib/nis.ex` — Last updated: 2026-02-27*