Terms Setup Database Schema

PreviousNext

Database structure, constraints, and data model for IP Terms

Overview

The Terms Setup feature uses a single database table ip_terms to store IP licensing terms. The schema is designed to support multiple term types per account, soft deletion, audit trails, and version tracking.

Table: ip_terms

The ip_terms table stores all IP licensing terms for accounts.

Schema Definition

Location: packages/db/src/schema/ipTerms.ts

The table includes the following columns:

Primary Key

  • id (text, primary key): Unique identifier for the term record. Generated using nanoid() function.

Foreign Keys

  • accountId (text, not null): References accountTable.id. Links the term to the account (seller) that owns it.

Term Type

  • ipTermType (enum, not null): Type of IP term. Must be one of:
    • "VoiceOver" - Voice recordings and audio content
    • "Likeness" - Person's likeness or appearance
    • "Image" - Images and photographs

Compensation Fields

  • compensationType (varchar(255), nullable): Type of compensation model. Must be a valid value from the compensationTypes enum if provided.
  • compensationValue (decimal(13, 2), nullable): Compensation amount. Stored as decimal string for precise currency handling. Must be non-negative if provided.

Approval Fields

  • approvalType (varchar(255), nullable): Type of approval required. Must be a valid value from the approvalTypes enum if provided.
  • ipApprove (boolean, not null, default: false): Whether the term has been approved. Must be explicitly set to true when approved.

Usage and Restrictions

  • permittedUsage (text, nullable): Free-text field describing permitted usage of the IP.
  • additionalRestrictions (text, nullable): Free-text field for additional restrictions on IP usage.
  • additionalShareData (text, nullable): Free-text field for additional data sharing requirements.

Versioning

  • termsVersion (integer, not null, default: 1): Version of the legal terms template used when creating the term. Tracks which version of the legal template was used for compliance purposes.

Audit Fields

  • createdBy (text, not null): User ID who created the term.
  • updatedBy (text, nullable): User ID who last updated the term.
  • createdAt (timestamp with timezone, not null, default: now): When the term was created.
  • updatedAt (timestamp with timezone, not null, default: now): When the term was last updated. Explicitly set on every update (not auto-updated by Drizzle).

Soft Delete

  • deletedAt (timestamp with timezone, nullable): When the term was soft-deleted. NULL means the term is active. Non-NULL means the term is deleted but preserved for audit.

Constraints

Partial Unique Index

Constraint Name: unique_active_account_ip_term_type

Definition: Unique index on [accountId, ipTermType] where deletedAt IS NULL

Purpose:

  • Ensures each account can have only one active term per ipTermType
  • Allows recreating a term after soft-deleting one of the same type
  • Prevents duplicate active terms while preserving deleted records

Behavior:

  • If you try to create a second active term of the same type for the same account, the database will reject it
  • After soft-deleting a term, you can create a new one of the same type
  • The constraint only applies to active (non-deleted) records

Foreign Key Constraint

Constraint: accountId references accountTable.id

Purpose: Ensures referential integrity. A term cannot exist without a valid account.

Behavior:

  • Cannot create a term with a non-existent accountId
  • Cannot delete an account that has active terms (database will prevent deletion)

Data Types

Decimal Storage

The compensationValue field is stored as decimal(13, 2):

  • Precision: 13 total digits
  • Scale: 2 decimal places
  • Storage: Stored as string in database (Drizzle returns as string)
  • Usage: Convert to number using priceUSDollar field in formatted response

Important: Always use priceUSDollar from the formatted response for numeric operations, not ipTerms.compensationValue directly (which is a string).

Enum Types

The ipTermType field uses a PostgreSQL enum type:

  • Type Name: ip_terms_ip_term_type
  • Values: "VoiceOver", "Likeness", "Image"
  • Validation: Database enforces that only these values can be stored

Soft Delete Pattern

The schema implements soft deletion:

How It Works

  1. Active Records: deletedAt IS NULL means the term is active
  2. Deleted Records: deletedAt IS NOT NULL means the term is soft-deleted
  3. Preservation: Deleted records remain in the database for audit purposes

Query Filtering

All database access functions automatically filter out soft-deleted records:

  • getIpTermsById() - Only returns records where deletedAt IS NULL
  • getIpTermsByAccountAndType() - Only returns records where deletedAt IS NULL
  • listIpTermsByAccountId() - Only returns records where deletedAt IS NULL

Soft Delete Operation

When soft-deleting a term:

  1. Set deletedAt to current timestamp
  2. Set updatedBy to user ID who performed deletion
  3. Set updatedAt to current timestamp
  4. Record is NOT physically deleted

Recreation After Deletion

After soft-deleting a term, you can create a new one of the same type because:

  • The partial unique index only applies where deletedAt IS NULL
  • The deleted record doesn't violate the constraint
  • A new active record can be created

Audit Trail

The schema provides complete audit trail capabilities:

Creation Tracking

  • createdBy: Who created the term
  • createdAt: When it was created

Modification Tracking

  • updatedBy: Who last modified the term (set on every update)
  • updatedAt: When it was last modified (explicitly set, not auto-updated)

Deletion Tracking

  • deletedAt: When it was soft-deleted
  • updatedBy: Who performed the deletion (set during soft delete)

Future Enhancement: Audit Table

Consider creating an ip_terms_audit table for detailed change history:

  • Track all field changes (previous and new values)
  • Store who changed what and when
  • Enable full audit trail for legal compliance
  • Support dispute resolution

Access Layer

Important: Never access the ip_terms table directly. Always use the access functions in packages/db/src/access/ipTerms.ts:

Available Functions

  • getIpTermsById(id: string): Get term by ID (excludes soft-deleted)
  • getIpTermsByAccountAndType(accountId: string, ipTermType: IpTermType): Get term by account and type (excludes soft-deleted)
  • listIpTermsByAccountId(accountId: string): List all terms for account (excludes soft-deleted, ordered by ipTermType)
  • createIpTerms(data: NewIpTerms): Create new term
  • updateIpTerms(id: string, data: Partial<UpdateIpTerms>): Update existing term (always sets updatedAt)
  • softDeleteIpTerms(id: string, updatedBy: string): Soft-delete term (sets deletedAt, updatedBy, updatedAt)

Why Use Access Layer?

  • Consistency: All queries automatically filter soft-deleted records
  • Type Safety: Functions use TypeScript types from @zooly/types
  • Maintainability: Changes to query logic happen in one place
  • Security: Prevents accidental access to deleted records

Type Definitions

The schema types are defined in @zooly/types:

IpTerms

The main type inferred from the schema:

interface IpTerms {
  id: string;
  accountId: string;
  ipTermType: "VoiceOver" | "Likeness" | "Image";
  compensationType: string | null;
  compensationValue: string | null; // Decimal stored as string
  approvalType: string | null;
  additionalRestrictions: string | null;
  additionalShareData: string | null;
  permittedUsage: string | null;
  ipApprove: boolean;
  termsVersion: number;
  createdBy: string;
  updatedBy: string | null;
  deletedAt: Date | null;
  createdAt: Date;
  updatedAt: Date;
}

NewIpTerms

Type for creating new terms (omits auto-generated fields):

type NewIpTerms = Omit<IpTerms, "id" | "createdAt" | "updatedAt" | "deletedAt">;

UpdateIpTerms

Type for updating terms (partial with explicit update fields):

type UpdateIpTerms = Partial<Omit<IpTerms, "id" | "accountId" | "createdAt" | "createdBy">> & {
  updatedBy: string;
  updatedAt: Date;
};

Migration

The schema is managed using Drizzle ORM migrations:

  1. Generate Migration: Run npm run db:generate in packages/db
  2. Review SQL: Check generated SQL in packages/db/drizzle/
  3. Apply Migration: Run npm run db:migrate or npm run db:push

The migration will create:

  • The ip_terms_ip_term_type enum type
  • The ip_terms table with all columns
  • The partial unique index
  • The foreign key constraint

Best Practices

  1. Always use access functions - Never query the table directly
  2. Check for soft-deleted records - Access functions handle this automatically
  3. Set updatedBy explicitly - Always provide updatedBy when updating
  4. Use priceUSDollar - Convert compensationValue string to number using formatted response
  5. Validate enum values - Check enum values before database operations
  6. Preserve audit trail - Never hard-delete records, always use soft delete