Found while investigating #598 (account dedupe in lead_conversion). Out of scope there; filing separately.
What
src/objects/account.object.ts spells account-name uniqueness as a declared index:
indexes: [
{ fields: ['name'], unique: true },
...
]normalizeDeclaredIndex() in @objectstack/driver-sql keeps a declared index's columns verbatim — it does not tenant-scope them. Only field-level unique: true gets the (organization_id, ...) composite treatment (uniqueIndexesFromFields(), framework#3696).
So the physical index this produces is:
uniq_crm_account_name UNIQUE (name) <-- platform-wide
whereas crm_contact.email, which declares the same intent as a field-level unique: true, produces:
uniq_crm_contact_organization_id_email UNIQUE (organization_id, email) <-- per-tenant
Reproduced against the shipped driver (17.0.0-rc.1):
import { expectedIndexes } from '@objectstack/driver-sql';
expectedIndexes({
table: 'crm_account',
fields: { name: { type: 'text' } },
tenantField: 'organization_id',
declaredIndexes: [{ fields: ['name'], unique: true }],
physicalColumns: new Set(['id', 'organization_id', 'name']),
});
// => [{ name: 'uniq_crm_account_name', columns: ['name'], unique: true }]Why it matters
The second organization to create an account called "Acme Corp" is rejected by the database. Account name is also the seed data's external-id / upsert key, so this bites the very first multi-tenant install.
This is exactly the failure mode src/objects/lead.object.ts already documents and deliberately avoids:
Declaring the single-column index too made the platform-wide constraint win and left the per-tenant one unreachable (framework#3991) — two organizations must be able to work the same lead address independently.
crm_lead and crm_product learned that lesson; crm_account still carries the platform-wide spelling.
Suggested fix
Move the uniqueness declaration onto the field, matching crm_contact.email / crm_lead.email / crm_product.sku:
name: Field.text({ label: 'Account Name', required: true, unique: true, ... }),
// and drop `{ fields: ['name'], unique: true }` from indexes[]Note this is a schema change on existing data, and it is not free:
- The new
uniq_crm_account_organization_id_name index is a create_index drift entry (category: 'safe').
- The old
uniq_crm_account_name becomes an orphan → drop_index, category: 'destructive', which the boot-time reconciler skips and only os migrate apply --allow-destructive clears (docs/MAINTENANCE.md §3.1).
Leaving the old index in place is not harmless here: it is strictly tighter than the new one, so it keeps enforcing the platform-wide rule and the fix silently does nothing. Whoever picks this up should decide the migration story before writing code — same class of question as #598 scope 1.
Acceptance
- Two organizations can each create an account named "Acme Corp".
- Within one organization, a duplicate account name is still rejected.
- The upgrade path for existing deployments is written down (which index is created, which is dropped, and by what command).
Found while investigating #598 (account dedupe in
lead_conversion). Out of scope there; filing separately.What
src/objects/account.object.tsspells account-name uniqueness as a declared index:normalizeDeclaredIndex()in@objectstack/driver-sqlkeeps a declared index's columns verbatim — it does not tenant-scope them. Only field-levelunique: truegets the(organization_id, ...)composite treatment (uniqueIndexesFromFields(), framework#3696).So the physical index this produces is:
whereas
crm_contact.email, which declares the same intent as a field-levelunique: true, produces:Reproduced against the shipped driver (17.0.0-rc.1):
Why it matters
The second organization to create an account called "Acme Corp" is rejected by the database. Account name is also the seed data's external-id / upsert key, so this bites the very first multi-tenant install.
This is exactly the failure mode
src/objects/lead.object.tsalready documents and deliberately avoids:crm_leadandcrm_productlearned that lesson;crm_accountstill carries the platform-wide spelling.Suggested fix
Move the uniqueness declaration onto the field, matching
crm_contact.email/crm_lead.email/crm_product.sku:Note this is a schema change on existing data, and it is not free:
uniq_crm_account_organization_id_nameindex is acreate_indexdrift entry (category: 'safe').uniq_crm_account_namebecomes an orphan →drop_index,category: 'destructive', which the boot-time reconciler skips and onlyos migrate apply --allow-destructiveclears (docs/MAINTENANCE.md§3.1).Leaving the old index in place is not harmless here: it is strictly tighter than the new one, so it keeps enforcing the platform-wide rule and the fix silently does nothing. Whoever picks this up should decide the migration story before writing code — same class of question as #598 scope 1.
Acceptance