# Database Crm System

> Manage DuckDB CRM data, aggressive relation-linked fields, and synced markdown documents in the workspace. Use when creating or updating objects, fields, entries, foreign-table links, row notes, or entry-linked edit logs.

- Skill: `aspectrr/database-crm-system` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add aspectrr/database-crm-system`
- Raw SKILL.md: https://api.skillmd.com/api/skills/aspectrr/database-crm-system/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Docs & Writing
- Author: aspectrr (https://skillmd.com/u/aspectrr)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/aspectrr/database-crm-system

---


# CRM / Database in Workspace / Guide on handling any data

You manage a CRM workspace stored at `/Users/collinpfeifer/.openclaw-dench/workspace`.
All structured data lives in **DuckDB**. The primary database is `/Users/collinpfeifer/.openclaw-dench/workspace/workspace.duckdb`, but subdirectories may contain their own `workspace.duckdb` that is authoritative for objects in that subtree (hierarchical DB discovery). Shallower databases take priority when objects share the same name. Documents are **markdown files** in `/Users/collinpfeifer/.openclaw-dench/workspace/**`. Organization context will be in `/Users/collinpfeifer/.openclaw-dench/workspace/workspace_context.yaml` if an organisation exists (READ-ONLY).

All actions should look into / edit and work on `/Users/collinpfeifer/.openclaw-dench/workspace/**` by default unless told otherwise. Exceptions to this are the `SOUL.md`, `skills/`, `memory/`, `USER.md`, `IDENTITY.md`, `TOOLS.md`, `AGENTS.md` and `MEMORY.md` and other such files.

All your workspace chats and past conversations are stored in `/Users/collinpfeifer/.openclaw-dench/workspace/.openclaw/web-chat/`.

---

## Table of Contents

1. [Workspace Structure](#workspace-structure)
2. [Startup](#startup)
3. [workspace_context.yaml (READ-ONLY)](#workspace_contextyaml-read-only)
4. [Naming Conventions](#naming-conventions)
5. [Error Handling](#error-handling)
6. [Critical Reminders](#critical-reminders)
7. [Child Skills](#child-skills)

---

## Workspace Structure

```
/Users/collinpfeifer/.openclaw-dench/workspace/
  workspace_context.yaml      # READ-ONLY org context (members, integrations, protected objects)
  workspace.duckdb            # DuckDB database — sole source of truth for structured data
  people/                     # Object directory
    .object.yaml              # Object metadata projection
    onboarding-guide.md       # Document nested under object
  companies/
    .object.yaml
  projects/
    projects.md               # Document content
    tasks/                    # Object nested under document
      .object.yaml
  exports/                      # On-demand CSV/Parquet exports
  WORKSPACE.md                  # Auto-generated schema summary
```

## Startup

On every conversation:

1. Read `/Users/collinpfeifer/.openclaw-dench/workspace/workspace_context.yaml` for org context, members, integrations, protected objects. **NEVER modify this file.**
2. Install duckdb if it doesn't exist: `curl https://install.duckdb.org | sh`
3. If `/Users/collinpfeifer/.openclaw-dench/workspace/workspace.duckdb` does not exist, initialize it with the schema in the **duckdb-operations** child skill.

## workspace_context.yaml (READ-ONLY)

This file is generated by the CRM system and synced via S3. It contains:

- `organization`: id, name, slug, business info
- `members`: Team members with IDs, names, emails, roles. **Use these IDs for "user" type fields** (e.g., "Assigned To").
- `protected_objects`: Objects that MUST NOT be deleted or renamed (e.g., people, companies).
- `integrations`: Connected apps with sync direction, frequency, and field mappings.
- `enrichment`: Whether enrichment is enabled and which provider.
- `defaults`: Default view, date format, naming conventions.
- `credits`: Current credit balance for enrichment/AI operations.

## Naming Conventions

- **Object names**: singular, lowercase, one word ("lead" not "Leads")
- **Field names**: human-readable, proper capitalization ("Email Address" not "email")
- **Be descriptive**: "Phone Number" not "Phone"
- **Be consistent**: Don't mix "Full Name" and "Name" in the same object
- **TRIPLE ALIGNMENT (MANDATORY)**: The DuckDB object `name`, the filesystem directory name, and the `.object.yaml` `name` field MUST all be identical. If any one of these three diverges, the UI will fail to render the object. For example, if DuckDB has `name = 'contract'`, the directory MUST be `contract/` (in workspace) and the yaml MUST have `name: "contract"`. Never use plural for one and singular for another.

### Renaming / Moving Objects

When renaming or relocating an object, you MUST update ALL THREE in a single operation:

1. **DuckDB**: Update `objects.name` (if FK constraints block this, recreate the object with the new name and migrate entries)
2. **Directory**: `mv` the old directory to the new name
3. **`.object.yaml`**: Update the `name` field to match
4. **PIVOT view**: `DROP VIEW IF EXISTS v_{old_name}; CREATE OR REPLACE VIEW v_{new_name} ...`
5. **Verify**: Confirm all three match and the view returns data

Never rename partially. If you can't complete all steps, don't start the rename — explain the constraint to the user first.

## Error Handling

- `UNIQUE constraint` on INSERT: item already exists — use `ON CONFLICT DO NOTHING` or `DO UPDATE`. Treat as success.
- Protected object deletion: check `immutable` column AND `protected_objects` in `workspace_context.yaml`. NEVER delete protected objects.
- Field type change: warn user before changing type on field with existing data.
- Missing required fields: validate before INSERT, report which fields are missing.
- **DuckDB SQL errors**: See the "Common DuckDB Pitfalls" section in the **duckdb-operations** child skill. The most frequent causes: unquoted field names with spaces (use `"Full Name"` not `Full Name`), wrong transaction syntax (`BEGIN TRANSACTION` not `BEGIN`), unescaped single quotes, and PIVOT views without the `IN (...)` field list.
- **"Script not found" on action buttons**: The `.actions/` directory and/or script files were not created. See the **actions** child skill post-creation checklist.

## Relation Fields / Foreign Links — Link Objects Aggressively

**Every time you create or modify objects or fields, check whether relation fields should connect them. Do NOT wait for the user to ask — proactively link objects that are obviously related.**

Relation fields (`type: "relation"`) let an entry in one object reference an entry in another. They render as a searchable dropdown in the UI and are the backbone of any useful CRM.

Treat relation fields as the CRM's default foreign-link primitive. If a field can reasonably point at another object, prefer a `relation` field over `text`, `richtext`, or some copied label string.

### Mandatory auto-linking rules

When you create an object, or add/modify a field on an existing object, scan all existing objects (run `SELECT name FROM objects`) and add relation fields for every obvious link:

| If you're creating... | And this object exists... | Auto-create this relation field |
|---|---|---|
| people / contact | company | "Company" on people → company (`many_to_one`) |
| lead / prospect | company | "Company" on lead → company (`many_to_one`) |
| deal / opportunity | people | "Primary Contact" on deal → people (`many_to_one`) |
| deal / opportunity | company | "Company" on deal → company (`many_to_one`) |
| task | people | "Assigned Contact" on task → people (`many_to_one`) |
| task | project / deal | "Related To" on task → parent (`many_to_one`) |
| case | people or company | "Client" on case → people/company (`many_to_one`) |
| invoice / payment | company | "Company" on invoice → company (`many_to_one`) |
| invoice / payment | deal | "Deal" on invoice → deal (`many_to_one`) |
| property / listing | people | "Agent" on property → people (`many_to_one`) |
| any child concept | its parent concept | relation to parent (`many_to_one`) |

**Use this SQL pattern** (safe — does nothing if the target object doesn't exist):

```sql
INSERT INTO fields (object_id, name, type, related_object_id, relationship_type, sort_order)
SELECT
  (SELECT id FROM objects WHERE name = 'people'),
  'Company',
  'relation',
  (SELECT id FROM objects WHERE name = 'company'),
  'many_to_one',
  3
WHERE EXISTS (SELECT 1 FROM objects WHERE name = 'company')
ON CONFLICT (object_id, name) DO NOTHING;
```

**General rule**: If you're creating object B or field F on object B and object A already exists, ask: "Would an entry in B logically belong to, reference, select, or connect to an entry in A?" If yes, add a `relation` field. Use `many_to_one` by default. Use `many_to_many` only when the field is clearly plural or multi-select (e.g. project → team members).

### Aggressive defaults for linked columns

- If the user says a column should "connect", "link", "reference", "relate", "belong to", "map to", "pick from", or "be tied to" another table/object, create a `relation` field.
- If the proposed field name matches or aliases an existing object, create a `relation` field instead of a text field.
- Do **NOT** model foreign references as bare strings like `Company Name`, `Client Name`, `Project Name`, `Deal Name`, `Owner Name`, or similar if the corresponding object already exists, unless the user explicitly wants a denormalized snapshot.
- If a field refers to a workspace member, use `user`, not `text`.
- If multiple target objects are plausible, choose the best semantic fit instead of falling back to `text`; ask only when the ambiguity would materially change the schema.
- If the target object does not exist yet but the relationship is clearly central to the workflow, prefer creating that object and linking it instead of flattening the data into a string field.

Relation fields **must be created via SQL** — the API does not support the `relation` type.

---

## Critical Reminders

- Handle the ENTIRE CRM operation from analysis to SQL execution to filesystem projection to summary
- **ENTRY DOCUMENTS ARE DEFAULT, NOT OPTIONAL**: Whenever possible, every CRM entry should have a connected markdown document. Create it when the entry is created or on the first mutation that touches the entry if the document does not already exist.
- **LOG EVERY ENTRY MUTATION**: Whenever entry data changes, update the connected document and append a concise timestamped change-log entry describing what changed.
- **NEVER SKIP FILESYSTEM PROJECTION**: After creating/modifying any object, you MUST create/update `{object}/.object.yaml` in workspace AND the `v_{object}` view. If you skip this, the object will be invisible in the sidebar. This is NOT optional.
- **THREE STEPS, EVERY TIME**: (1) SQL transaction, (2) filesystem projection (.object.yaml + directory), (3) verify. An operation is NOT complete until all three are done. See **object-builder** child skill.
- Always check existing data before creating (`SELECT` before `INSERT`, or `ON CONFLICT`)
- Use views (`v_{object}`) for all reads — never write raw PIVOT queries for search
- Never assume field names — verify with `SELECT * FROM fields WHERE object_id = ?`
- **DATE FALLBACK**: If an object has no custom `date` field, use `created_at`/`updated_at` (from `entries`) instead of saying there are no date fields.
- Extract ALL data from user messages — don't leave information unused
- **REPORTS vs DOCUMENTS**: When the user asks for "reports", "analytics", "charts", "graphs", "metrics", "insights", or "breakdown" — use `.report.json` format (see **reports** child skill), NOT markdown. Only use markdown `.md` for SOPs, guides, notes, and prose documents.
- **INLINE CHART ARTIFACTS**: When answering analytics questions in chat, ALWAYS emit a `report-json` fenced code block so the UI renders interactive charts inline.
- **NOTES FIELD vs ENTRY DOCUMENTS**: These are different. `Notes` is a DuckDB `richtext` field stored in `entry_fields`. Entry documents are markdown files linked through the `documents` table. References to "notes" on a row or entry default to the connected entry document unless the user explicitly says `Notes` field/column or needs table/filter/SQL semantics.
- **ENTRY DOCUMENTS**: New entry detail pages should use human-readable filenames (for example `yt-mikemurphy-001.md`, `jane-smith-001.md`, `acme-corp-001.md`) and MUST be registered in DuckDB `documents` with `file_path`, `parent_object_id`, and `entry_id`. Reuse an existing linked document when present; otherwise create one and keep it synced on every mutation. Do NOT default to raw `{entry_id}.md` filenames for new docs. See **documents** child skill.
- **USER FIELDS**: Resolve member name to ID from `workspace_context.yaml` BEFORE inserting
- **ENUM FIELDS**: Use type "enum" with `enum_values` JSON array
- **RELATION FIELDS (PROACTIVE — SEE SECTION ABOVE)**: ALWAYS create relation fields when objects are obviously linked or when a new field is semantically a foreign link. Do not fall back to bare string columns just because they are easier. See the **"Relation Fields / Foreign Links — Link Objects Aggressively"** section above for the full rules and SQL pattern.
- **TAGS FIELDS**: Use type "tags" for free-form string arrays. Value stored as `'["tag1","tag2"]'`
- **URL FIELDS**: Use type "url" for website addresses and links
- **FILE FIELDS**: Use type "file" for file attachments (stores file path or URL)
- **ACTION FIELDS (CRITICAL — READ CAREFULLY)**: Use type `"action"` with `default_value` containing `{"actions":[...]}` JSON. Action fields are excluded from PIVOT views. **When using file-based scripts (`scriptPath`), you MUST also: (1) `mkdir -p` the `.actions/` directory, (2) write every script file referenced in the config, (3) update `.object.yaml` with `action_config`.** If you skip creating the script files, the button will render but clicking it shows "Script not found". See **actions** child skill for the mandatory post-creation checklist and a complete end-to-end example.
- **KANBAN**: Use `default_view = 'kanban'`, set `view_settings.kanbanField: "Status"`, auto-create Status and Assigned To fields
- **CALENDAR**: Use `default_view = 'calendar'`, set `view_settings.calendarDateField` to the date field
- **TIMELINE**: Use `default_view = 'timeline'`, set `view_settings.timelineStartField` and optionally `timelineEndField`
- **VIEW TYPES**: Valid `default_view` values: `table`, `kanban`, `calendar`, `timeline`, `gallery`, `list`
- **FILTER REQUESTS**: When the user asks to filter, narrow, show only, hide, segment, or otherwise change which entries appear in the UI, default to creating/updating a saved view in `.object.yaml` and set `active_view` so the UI updates immediately. Do this even if they did not explicitly ask to "create a view", unless they clearly want a one-off answer only.
- **VIEW COLUMNS**: For saved table views, omit `columns` by default. If `columns` is not present, the table uses the default/all columns. `views[].columns` controls visibility only, not display order. Only write `columns` when the user explicitly asks for a specific visible subset.
- **COLUMN REORDERING**: If the user asks to reorder table columns, do not try to encode that in `views[].columns`. Update the object's field `sort_order` in DuckDB, then regenerate `.object.yaml` so its top-level `fields` list matches the new schema order.
- **COLUMN WIDTHS**: If the user asks to resize or set column widths, set `view_settings.column_widths` (object-level) or `views[].column_widths` (per-view) as a map of field name to pixel width (e.g. `column_widths: { "Full Name": 250, "Status": 150 }`). The UI also auto-persists widths when columns are drag-resized.
- **PROTECTED OBJECTS**: Never delete objects listed in `workspace_context.yaml` `protected_objects`
- **DUCKDB QUOTING**: Field names with spaces MUST be double-quoted in SQL: `"Full Name"`, `"Email Address"`, etc. Without quotes, DuckDB treats them as separate identifiers and the query fails.
- **PIVOT VIEWS**: Always use the `IN (...)` clause to list field names explicitly. Exclude action fields with `AND f.type != 'action'`. See **duckdb-operations** child skill.
- **ONE EXEC CALL**: Batch related SQL in a single transaction
- **workspace_context.yaml**: READ-ONLY. Never modify. Data flows from the CRM UI only.
- **Source of truth**: DuckDB for all structured data. Filesystem for document content and navigation tree. Never duplicate entry data to the filesystem.
- **ENTRY COUNT**: After adding entries, update `entry_count` in `.object.yaml`.
- **NAME CONSISTENCY**: The DuckDB `objects.name`, the filesystem directory name, and `.object.yaml` `name` MUST be identical. See Naming Conventions above.
- **NEVER POLLUTE THE WORKSPACE**: Always keep cleaning / organising the workspace to something more nicely structured. Always look out for bloat and too many random files scattered around everywhere for no reason, every time you do any actions in filesystem always try to come up with the most efficient and nice file system structure inside the workspace.
- **TEMPORARY FILES**: All temporary scripts / code / text / other files as and when needed for processing must go into `tmp/` directory (create it in the workspace if it doesn't exist, only if needed).

---

## Child Skills

This skill covers workspace fundamentals. For specialized operations, see these child skills (all inside the `crm/` skill folder):

| Skill | Path | Covers |
|-------|------|--------|
| **DuckDB Operations** | `crm/duckdb-operations/SKILL.md` | DuckDB schema initialization, field types reference, auto-generated PIVOT views, SQL CRUD operations (create/read/update/delete objects, fields, entries), bulk import/export |
| **Object Builder** | `crm/object-builder/SKILL.md` | Full 3-step workflow (SQL → filesystem → verify), CRM patterns (contact, lead, company, deal, case, property, task), kanban boards, post-mutation checklist, renaming objects |
| **Views & Filters** | `crm/views-filters/SKILL.md` | `.object.yaml` format and template, view type settings (kanban, calendar, timeline, gallery, list), saved views, filter operators by field type, date format rules |
| **Documents** | `crm/documents/SKILL.md` | Document management (markdown files), cross-nesting (documents under objects, objects under documents), default connected entry pages, and entry mutation/edit logs |
| **Reports** | `crm/reports/SKILL.md` | Report generation (`.report.json` format), chart types (bar, line, area, pie, donut, radar, scatter, funnel), panel sizes, filter types, inline chat reports, post-report checklist |
| **Actions** | `crm/actions/SKILL.md` | Action field type (`type: "action"`), executable buttons on entries, server-side script execution in any language (JS, Python, bash, etc.), inline JS SDK, NDJSON stdout protocol, environment variable context, bulk parallel execution, action_runs table, UI rendering in table/kanban/entry panel |

All child skills are seeded into the workspace alongside this parent skill and can be read at `/Users/collinpfeifer/.openclaw-dench/workspace/skills/crm/<child>/SKILL.md`.