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 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.