About the Database Annotation

Use the @db annotation to define how the C3 Agentic AI Platform stores, indexes, and manages the persisted data of an entity Type. The Ann.Db Type defines the properties you can set on the annotation. On this page, you look up the most common fields, copy example annotations for time-series data and for searchable relational data, and verify that an annotation works.

Copy link to this section

Database annotation fields

Set these fields to determine how the platform persists data for an entity Type. The following table lists each field with what it does, the data store it applies to, and an example. The Default behavior column says what happens if you do not set the field. The table covers the most common fields. For the full list, including the index, order, archive, and shortIdPrefix fields that the examples on this page use, see the Ann.Db Type.

The default SQL store database depends on your environment. A default single-node environment uses H2. Shared environments and single-node environments with a shared database use PostgreSQL. For details, see the Default databases section of Specify a KV Store.

Copy link to this section

Set persistence order

The persistenceOrder field sorts objects within each partition key. Follow these rules when you set it:

• Include every field of a unique index in persistenceOrder. If a unique index field is missing from the persistence order of a Partitionable Type, the platform reports an error.
• Use fields of primitive types when possible, such as a datetime field named timestamp. Primitive fields give better performance than fields whose type is another Type.
• Use Meta fields in the persistence order with caution. If the persistence order includes a Meta field, the platform disables de-duplication.

Copy link to this section

Example annotations

Choose @db settings by how your app writes and reads the Type's data. The following examples show two common cases.

Copy link to this section

High-volume, time-series data

The following annotation serves high-volume, time-series data, such as sensor readings, log entries, or measurement data points:

@db(datastore='cassandra',
    partitionKeyField='parent',
    persistenceOrder='start',
    persistDuplicates=false,
    compactType=true,
    shortId=true,
    shortIdReservationRange=100000)
entity type WindTurbineMeasurement mixes Partitionable<string> {
  // Declare the parent and start fields that the annotation names.
  // ...
}

This annotation defines the following for the WindTurbineMeasurement Type:

• mixes Partitionable<string>: Makes the Type Partitionable. A Partitionable Type must set partitionKeyField.
• datastore='cassandra': Specifies the Cassandra datastore. This value requires a store configuration named cassandra. Without a cassandra store configuration, the Type uses the default KV store for your environment. See Specify a KV Store.
• partitionKeyField='parent': Stores all measurements for a single wind turbine in the same partition, which speeds up queries for that turbine.
• persistenceOrder='start': Sorts measurements within a partition by time, which speeds up time-range queries.
• compactType=true and shortId=true: Reduce the overall storage footprint.
• persistDuplicates=false: Removes duplicate measurements, so the same measurement is stored only once.

Copy link to this section

Searchable relational data

For Types such as User, PurchaseOrder, or a custom Project Type, data integrity, searchability, and relational queries can matter more than write speed. For example:

@db(unique=['purchaseOrderNumber'], index+=['customer', 'orderDate'])
entity type PurchaseOrder {
  purchaseOrderNumber: !string
  customer: !Customer
  orderDate: !datetime
  // ...
}

This annotation omits the datastore field, so the Type uses the app's default SQL store.

This annotation defines the following for the PurchaseOrder Type:

• unique=['purchaseOrderNumber']: Creates a unique index on the purchase order number.
• index+=['customer', 'orderDate']: Adds two indexes, one on customer and one on orderDate, to speed up queries by customer or by order date.

Copy link to this section

Verify an annotation

After you apply a @db annotation, verify that it has the effect you expect. This section is for developers who want to confirm an annotation in a development environment before they rely on it.

Copy link to this section

Prerequisites

You need a C3 AI development environment. For more information, see Create an Environment in C3 AI Studio.

Copy link to this section

Verification workflow

Complete the following workflow to verify an annotation:

1. Deploy the Type changes to your environment, for example by syncing your package. Schema changes, such as a new unique index or an archive table, take effect after you deploy.
2. Create test records that the annotation affects.
3. Run the operation that the annotation changes, such as a fetch, create(), or remove() call.
4. Check that the result matches your expectation.
5. Remove the test records when you finish.

Each of the following examples defines a Type with one annotation and gives code to run in the C3 AI Console. The Console shows console.log output in its Logs pane.

Copy link to this section

Verify a sort order (order)

The order field on a collection field sets the order in which the platform returns the collection's values. The following example sorts a parent's children collection by name in descending order:

/**
 * A child entity that refers to its parent.
 */
entity type MyChild {
  parent: MyParent
}

/**
 * A parent entity whose children collection is sorted by name in descending order.
 */
entity type MyParent {
  /**
   * The child objects whose parent field refers to this object.
   */
  @db(order='descending(name)')
  children: [MyChild](parent)
}

Run the following code in the C3 AI Console:

var parent = MyParent.make({ id: 'parent-1' }).create();
MyChild.make({ id: 'child-a', parent: parent, name: 'Alpha' }).create();
MyChild.make({ id: 'child-z', parent: parent, name: 'Zulu' }).create();

var fetchedParent = MyParent.forId('parent-1').get('children.name');
var firstChildName = fetchedParent.children.first().name;

console.log('First child name:', firstChildName);
// Expected output: First child name: Zulu

This code creates a parent and two children named Alpha and Zulu, then fetches the parent with the names of its children. The first child is Zulu, because the annotation sorts the collection by name in descending order. MyChild does not declare name or id, because every entity Type has both fields.

Copy link to this section

Verify a unique index (unique)

The unique field lists unique indexes. The following example lists uniqueName as a unique index:

/**
 * An entity with a unique index on the uniqueName field.
 */
@db(unique=['uniqueName'])
entity type MyUniqueType {
  /**
   * A name that is listed as a unique index.
   */
  uniqueName: !string
}

Run the following code in the C3 AI Console:

try {
  MyUniqueType.make({ id: 'A', uniqueName: 'test-name' }).create();
  console.log('First object created successfully.');

  MyUniqueType.make({ id: 'B', uniqueName: 'test-name' }).create(); // This line should fail

  console.error('Test Failed: Unique constraint was not enforced.');

} catch (e) {
  // Inspect e.message to confirm that the error names the duplicate value.
  console.log('Test Passed: Caught expected exception: ' + e.message);
}

The first create() call succeeds. The second create() call uses the same uniqueName value and throws an exception whose message names the duplicate value.

Copy link to this section

Verify archive on remove (archive)

When you set archive=true, the platform archives instances instead of physically removing them when you call remove(). You can restore an archived instance with unremove(). Do not create a new instance with the ID of an archived instance.

/**
 * An entity whose instances the platform archives on remove.
 */
@db(archive=true)
entity type MyArchivableType {
  /**
   * A data field that the archive preserves with the rest of the object.
   */
  name: string
}

Run the following code in the C3 AI Console:

var obj = MyArchivableType.make({ id: 'archive-test' }).create();
obj.remove();

var afterRemove = MyArchivableType.forId('archive-test');
console.assert(!afterRemove, 'Object should be gone after removal.');

MyArchivableType.make({ id: 'archive-test' }).unremove();
var afterUnremove = MyArchivableType.forId('archive-test');

console.assert(afterUnremove, 'Object should be restored after unremove.');
console.log('Archive and unremove test passed.');

This code creates and removes an instance and confirms that the instance is no longer found. The code then restores the instance with unremove() and confirms that the instance is found again. In the restore call, make builds an in-memory object with the archived ID, and unremove() restores the archived instance with that ID.

Copy link to this section

See also

• Ann.Db
• Specify a KV Store
• Cassandra Database
• PostgreSQL Database
• Annotations