database-crm-system

name	database-crm-system
description	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.
metadata	{"openclaw":{"inject":true,"always":true,"emoji":"📊"}}

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
2. Startup
3. workspace_context.yaml (READ-ONLY)
4. Naming Conventions
5. Error Handling
6. Critical Reminders
7. 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):

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 field/column or needs table/filter/SQL semantics.

---

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.