Build Your First SignalDesk AI Schema

Your first SignalDesk schema will store organizations, customers, tickets, and messages with explicit keys, constraints, and relationships. The goal is not merely to create four tables. It is to make invalid ownership, status, priority, and authorship states difficult or impossible while keeping every seed operation reproducible and verifiable.

What will you be able to do?

Translate simple business rules into PostgreSQL DDL.
Use identity keys, NOT NULL, CHECK, UNIQUE, and foreign keys.
Insert related rows in one transaction.
Read constraint errors as evidence of protected invariants.

Prerequisites

Connect to signaldesk_learning. Confirm the database and the signaldesk schema from the previous chapter.

What is the mental model?

A table is an API for facts. Columns are inputs, constraints are preconditions, and committed rows are accepted facts. The database is the final shared referee because application code, scripts, imports, jobs, and future AI tools may all write data.

Rendering diagram...

Which business rules will the database enforce?

An organization has a unique slug.
A customer email is unique inside an organization, not globally.
A ticket belongs to the same organization recorded with it.
Status and priority come from explicit allowed sets.
A message belongs to a ticket and has a known author kind.
Instants use timestamptz and default to the transaction’s current time.

How do you create the schema?

Run this in the learning database:

sql

BEGIN;

CREATE TABLE signaldesk.organizations (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug text NOT NULL UNIQUE,
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT organizations_slug_format
    CHECK (slug ~ '^[a-z0-9]+(?:-[a-z0-9]+)*$'),
  CONSTRAINT organizations_name_not_blank
    CHECK (btrim(name) <> '')
);

CREATE TABLE signaldesk.customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  organization_id bigint NOT NULL
    REFERENCES signaldesk.organizations(id) ON DELETE RESTRICT,
  email text NOT NULL,
  display_name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT customers_email_not_blank CHECK (btrim(email) <> ''),
  CONSTRAINT customers_name_not_blank CHECK (btrim(display_name) <> ''),
  CONSTRAINT customers_org_email_unique UNIQUE (organization_id, email),
  CONSTRAINT customers_org_id_unique UNIQUE (organization_id, id)
);

CREATE TABLE signaldesk.tickets (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  organization_id bigint NOT NULL,
  customer_id bigint NOT NULL,
  subject text NOT NULL,
  status text NOT NULL DEFAULT 'open',
  priority text NOT NULL DEFAULT 'normal',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT tickets_subject_not_blank CHECK (btrim(subject) <> ''),
  CONSTRAINT tickets_status_allowed
    CHECK (status IN ('open', 'pending', 'resolved', 'closed')),
  CONSTRAINT tickets_priority_allowed
    CHECK (priority IN ('low', 'normal', 'high', 'urgent')),
  CONSTRAINT tickets_customer_same_org
    FOREIGN KEY (organization_id, customer_id)
    REFERENCES signaldesk.customers (organization_id, id)
    ON DELETE RESTRICT,
  CONSTRAINT tickets_org_id_unique UNIQUE (organization_id, id)
);

CREATE TABLE signaldesk.messages (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  organization_id bigint NOT NULL,
  ticket_id bigint NOT NULL,
  author_kind text NOT NULL,
  body text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT messages_author_kind_allowed
    CHECK (author_kind IN ('customer', 'agent', 'assistant', 'system')),
  CONSTRAINT messages_body_not_blank CHECK (btrim(body) <> ''),
  CONSTRAINT messages_ticket_same_org
    FOREIGN KEY (organization_id, ticket_id)
    REFERENCES signaldesk.tickets (organization_id, id)
    ON DELETE CASCADE
);

COMMIT;

The composite foreign keys are intentional: they prevent a ticket or message from pointing across organizations even if application code passes mismatched identifiers. The supporting unique constraints make the referenced column sets valid foreign-key targets.

sql

BEGIN;

WITH new_org AS (
  INSERT INTO signaldesk.organizations (slug, name)
  VALUES ('northstar-health', 'Northstar Health')
  RETURNING id
), new_customer AS (
  INSERT INTO signaldesk.customers (organization_id, email, display_name)
  SELECT id, 'amina@example.test', 'Amina Shah'
  FROM new_org
  RETURNING organization_id, id
), new_ticket AS (
  INSERT INTO signaldesk.tickets
    (organization_id, customer_id, subject, priority)
  SELECT organization_id, id, 'Cannot access the billing portal', 'high'
  FROM new_customer
  RETURNING organization_id, id
)
INSERT INTO signaldesk.messages
  (organization_id, ticket_id, author_kind, body)
SELECT organization_id, id, 'customer', 'The portal redirects me to sign-in.'
FROM new_ticket;

COMMIT;

RETURNING carries generated identities through the statement without guessing sequence values.

Prove it worked

sql

SELECT
  o.slug,
  c.email,
  t.id AS ticket_id,
  t.status,
  t.priority,
  m.author_kind,
  m.body
FROM signaldesk.organizations AS o
JOIN signaldesk.customers AS c ON c.organization_id = o.id
JOIN signaldesk.tickets AS t
  ON t.organization_id = c.organization_id
 AND t.customer_id = c.id
JOIN signaldesk.messages AS m
  ON m.organization_id = t.organization_id
 AND m.ticket_id = t.id;

Then intentionally test one invariant inside a rollbackable transaction:

sql

BEGIN;
INSERT INTO signaldesk.tickets
  (organization_id, customer_id, subject, status)
VALUES (1, 1, 'Invalid status test', 'teleported');
ROLLBACK;

The statement must fail with tickets_status_allowed. The transaction will be aborted until ROLLBACK; that behavior is important.

Why does this matter in AI-native systems?

A classifier may suggest an unknown priority, a tool call may carry a ticket from another tenant, and a summarizer may return blank output. Constraints turn those mistakes into visible failures before corrupted facts are committed. AI can propose; the database still enforces the allowed state space.

Production judgment

Text CHECK constraints are transparent for a small stable set; lookup tables or enums have different migration tradeoffs.
Case-insensitive email identity needs a deliberate normalization policy; the simplified check here is not full email validation.
ON DELETE CASCADE is appropriate for dependent messages only if the product truly permits ticket deletion. Regulated systems may retain or pseudonymize instead.
now() is transaction-stable. updated_at does not update automatically; later chapters design explicit change behavior.
Schema creation should run through versioned migrations, not ad hoc production sessions.

Common mistakes

Symptom	Cause	Fix
Cross-tenant child row is accepted	Foreign key used only the child ID	Include tenant ownership in the key relationship
Seed script guesses generated IDs	Sequence behavior was assumed	Use RETURNING and data-modifying CTEs or separate parameterized statements
Transaction remains “aborted”	Error occurred before rollback	Inspect the first error, then ROLLBACK
Status typos accumulate	Allowed states were only documented	Add a constraint and test rejection

AI Pair-Programmer Prompt

text

Review this SignalDesk PostgreSQL DDL as an invariant checker. For each constraint,
state the business rule it enforces and show one rollbackable negative test. Look for
cross-tenant references, delete behavior, blank values, time semantics, and generated
identity mistakes. Do not execute or rewrite the schema automatically. Return proposed
changes, lock/migration consequences, and verification SQL for my review.

Hands-on exercise

Add an optional resolved_at timestamptz and enforce that resolved or closed tickets have a resolution time while open or pending tickets do not. Decide whether one constraint or two communicates the rule better.

Acceptance criterion: four negative tests reject every invalid status/time combination, and valid open and resolved rows insert inside a transaction you roll back.

Knowledge check

Why use a composite tenant foreign key?
What does RETURNING solve?
Does DEFAULT now() update updated_at on later changes?
What must happen after a statement error aborts a transaction?

Answers

It proves the referenced child belongs to the same tenant as the parent row.
It returns values, including generated identities, from changed rows without guessing.
No. A default applies when the row is inserted unless a value is supplied.
Issue ROLLBACK or roll back to an earlier savepoint before continuing.

Completion checklist

Four core tables exist in signaldesk.
Seed data commits atomically.
The joined verification query returns one coherent ticket thread.
Invalid status and tenant relationships are rejected.
I can explain every delete action and constraint.

Build Your First SignalDesk AI Schema

What will you be able to do?

Prerequisites

What is the mental model?

Which business rules will the database enforce?

How do you create the schema?

Prove it worked

Why does this matter in AI-native systems?

Production judgment

Common mistakes

AI Pair-Programmer Prompt

Hands-on exercise

Knowledge check

Answers

Completion checklist

Primary references

Build Your First SignalDesk AI Schema

What will you be able to do?

Prerequisites

What is the mental model?

Which business rules will the database enforce?

How do you create the schema?

Prove it worked

Why does this matter in AI-native systems?

Production judgment

Common mistakes

AI Pair-Programmer Prompt

Hands-on exercise

Knowledge check

Answers

Completion checklist

Primary references

Build Your First SignalDesk AI Schema

What will you be able to do?

Prerequisites

What is the mental model?

Which business rules will the database enforce?

How do you create the schema?

How do you seed related data atomically?

Prove it worked

Why does this matter in AI-native systems?

Production judgment

Common mistakes

AI Pair-Programmer Prompt

Hands-on exercise

Knowledge check

Answers

Completion checklist

Primary references

Build Your First SignalDesk AI Schema

What will you be able to do?

Prerequisites

What is the mental model?

Which business rules will the database enforce?

How do you create the schema?

How do you seed related data atomically?

Prove it worked

Why does this matter in AI-native systems?

Production judgment

Common mistakes

AI Pair-Programmer Prompt

Hands-on exercise

Knowledge check

Answers

Completion checklist

Primary references