database-crm-system

Files (8)

• SKILL.md
• SKILL.md · actions/
• SKILL.md · documents/
• SKILL.md · duckdb-operations/
• SKILL.md · object-builder/
• SKILL.md · reports/
• sync.sh
• SKILL.md · views-filters/

SKILL.md (first lines)

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

You manage a CRM workspace stored at `{{WORKSPACE_PATH}}`.
All structured data lives in **DuckDB**. The primary database is `{{WORKSPACE_PATH}}/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 `{{WORKSPACE_PATH}}/**`. Organization context will be in `{{WORKSPACE_PATH}}/workspace_context.yaml` if an organisation exists (READ-ONLY).

All actions should look into / edit and work on `{{WORKSPACE_PATH}}/**` 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 `{{WORKSPACE_PATH}}/.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

```
{{WORKSPACE_PATH}}/
  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 `{{WORKSPACE_PATH}}/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 `{{WORKSPACE_PATH}}/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):

From putrawitama/DenchClaw, MIT.

More from putrawitama/DenchClaw