12: People and indexes

The project planning sample assigns work to employees, and it leans on indexes to keep lookups fast and data honest. This chapter adds people to the schema, then looks at REFERENCE and DEFINE INDEX, including UNIQUE constraints.

Employee table

Add a table for the people doing the work. Create database/schema/employee.surql:

DEFINE TABLE employee SCHEMAFULL;
DEFINE FIELD name ON employee TYPE string;
DEFINE FIELD email ON employee TYPE option<string> ASSERT $value IS NONE OR string::is_email($value);

Assigning activities to people

An activity will not always have someone assigned yet, so the field on activity needs to allow for that. Add this to database/schema/activity.surql:

DEFINE FIELD assigned_to ON activity TYPE option<record<employee>>;

option means that "nobody assigned yet" is a valid state, not a missing field. If a later requirement says every activity must have an owner, you would tighten this the same way you tightened fields earlier in the course: backfill the data, then ALTER (part 3).

Create an employee and assign them to an existing activity so you have something to query:

CREATE employee:ada SET name = "Ada", email = "ada@example.com";
UPDATE activity:kickoff SET assigned_to = employee:ada;

REFERENCE and what happens on delete

As things stand, nothing stops you deleting employee:ada even though an activity points at her. If you would rather SurrealDB refused that delete, add a REFERENCE clause. Replace the assigned_to line you just wrote rather than adding a second one, for the reason given in part 8: one DEFINE FIELD per table and field name, or the metadata key collides.

DEFINE FIELD assigned_to ON activity TYPE option<record<employee>>
    REFERENCE ON DELETE REJECT;

With employee:ada still assigned to activity:kickoff, the delete is now refused:

DELETE employee:ada;

'Cannot delete `employee:ada` as it is referenced by `activity:kickoff` with an ON DELETE REJECT clause'

REFERENCE is catalog metadata that SurrealDB enforces on delete. That is different from a bare record<> field (part 4), which just stores a pointer and does nothing special when the record it points to disappears.

Indexes for the queries you actually run

Lookup by project name

DEFINE INDEX project_name ON project FIELDS name;

This supports WHERE name = … on a long list of projects. It does not need to be UNIQUE unless project names must be distinct.

Unique milestone per project

DEFINE INDEX milestone_name_per_project ON milestone FIELDS project, name UNIQUE;

UNIQUE indexes stop duplicates at write time, which is exactly what you want for (project, name). Before adding one on a table that already has data, check for duplicates first (part 3).

We can do this by filtering the grouped result with an outer SELECT:

SELECT * FROM (
    SELECT project, name, count() AS total FROM milestone GROUP BY project, name
) WHERE total > 1;

On a small table you can also just read the counts and skip the outer filter:

SELECT project, name, count() AS total FROM milestone GROUP BY project, name;

If either returns records, backfill or rename the duplicates in a data script before you add the index, whether that is a local sync or a rollout start on a shared database.

Activity date ranges

Scheduling queries usually filter on start or end:

DEFINE INDEX activity_start ON activity FIELDS start;

Composite indexes are worth adding once your actual query patterns justify them. See DEFINE INDEX for the full syntax.

ASSERT or a UNIQUE index?

Uniqueness of (project, name) is a cross-row rule, so it belongs on an index, not a handwritten ASSERT over a subquery.

Indexes and SurrealKit

Adding an index is non-destructive, so it is safe on rollout start (or a local sync). Dropping one is destructive, so save that for rollout complete, once nothing depends on it any more.

surrealkit sync --user root --pass secret --ns main --db main   # local
# or
surrealkit rollout plan --name add_planning_indexes

Checkpoint

• •

  REFERENCE ON DELETE REJECT (or whichever delete policy fits)

• •

  DEFINE INDEX for lookups, UNIQUE where duplicates would be a bug