Cosmos DB schema review for SaaS support history

SaaS support dashboard में सबसे खतरनाक bug चुप रहता है: tenant A की search में tenant B की billing note आ जाती है। page चलता है और JSON valid दिखता है। यह लेख सीमा टूटने से पहले Claude Code से Azure Cosmos DB schema review कराता है।

Key Points

Support history में ticket, message, internal note और audit log अलग देखने पड़ते हैं। Cosmos DB में partition key, tenant-aware query, unique key, retention, masking और security देखी जाती है।

Workflow For Support History Schema

Support screen से शुरू करें: ticket list, customer detail, messages, internal notes, audit log और AI summary export। Claude Code checklist बनाता है; contract और privacy इंसान देखते हैं।

Data type	Cosmos DB review	Human review
Ticket	tenantId, partition key, unique external id	contract and priority
Message	body mask, retention, query path	personal data and secrets
Internal note	staff-only boundary	refund and incident wording
Audit log	retention, actor, action, timestamp	compliance and deletion request

What Claude Code Does and What Humans Decide

Claude Code drafts document fields, partition key candidates, query checks, unique key notes, masking rules, and retention notes. Humans decide customer contracts, support text, incident wording, deletion requests, AI usage, and audit retention.

3 Use Cases

Use case 1: Pick a tenant-aware partition key

• Input: tenant count, ticket count per tenant, list queries, large tenant range.
• Output: partition key candidates, query examples, hot-tenant risk, container split option.
• Human review: enterprise contracts, retention, cost, and migration path.

Use case 2: Separate messages and internal notes

• Input: message document, internal note, attachment fields, AI summary target.
• Output: customer message fields, staff-only fields, no-log fields, mask list.
• Human review: personal data, token, incident explanation, refund, legal wording.

Use case 3: Test cross-tenant queries

• Input: list API, search query, admin screen, test tenants, roles.
• Output: missing tenantId query, denial cases, expected 403 behavior.
• Human review: admin search, support impersonation, dedicated enterprise environment.

Copy-Paste Prompt

Act as a Hindi SaaS Azure Cosmos DB schema reviewer.
Goal: prevent cross-tenant support history leaks, weak partition keys, sensitive-text storage, and retention gaps.

Review:
- ticket / message / internal note / audit log documents
- container settings
- partition key candidates
- support list queries
- tenant size ranges
- retention and deletion workflow

Return:
1. tenantId coverage
2. partition key candidates and risks
3. queries missing tenantId
4. support message and internal note boundary
5. unique key candidates
6. sensitive text masking checklist
7. five fields or queries a beginner should inspect today

Working Check Code

// verify-cosmos-saas-schema.mjs
// No dependencies. Run with: node verify-cosmos-saas-schema.mjs
const container = {
  name: "supportHistory",
  partitionKey: "/id",
  uniqueKeys: [],
  ttl: null
};

const documents = [
  {
    id: "ticket-001",
    tenantId: "tenant-a",
    type: "ticket",
    subject: "billing question",
    customerEmail: "[email protected]",
    createdAt: "2026-07-19T09:00:00Z"
  },
  {
    id: "msg-001",
    type: "message",
    ticketId: "ticket-001",
    body: "please check token sk_live_example and invoice",
    createdAt: "2026-07-19T09:02:00Z"
  }
];

const queries = [
  "SELECT * FROM c WHERE c.type = 'ticket' ORDER BY c.createdAt DESC",
  "SELECT * FROM c WHERE c.tenantId = @tenantId AND c.type = 'ticket'"
];

const problems = [];

if (container.partitionKey === "/id") {
  problems.push({ item: "partition key", fix: "use a tenant-aware key such as /tenantId or a tested hierarchical key" });
}
if (!container.uniqueKeys.some((key) => key.paths?.includes("/tenantId") && key.paths?.includes("/externalId"))) {
  problems.push({ item: "unique key", fix: "protect duplicate external ticket ids within a tenant" });
}
if (container.ttl === null) {
  problems.push({ item: "retention", fix: "define retention for support message copies and audit exports" });
}
for (const doc of documents) {
  if (!doc.tenantId) {
    problems.push({ item: "document tenantId", fix: "every support ticket and message needs tenantId" });
  }
  if (/sk_live|token|password|secret/i.test(JSON.stringify(doc))) {
    problems.push({ item: "sensitive text", fix: "mask tokens and secrets before storing support history" });
  }
}
for (const query of queries) {
  if (!/tenantId\s*=\s*@tenantId/.test(query)) {
    problems.push({ item: "query boundary", fix: "include tenantId in customer-facing support queries" });
  }
}

if (problems.length > 0) {
  console.table(problems);
  process.exitCode = 1;
} else {
  console.log("Cosmos DB SaaS schema checklist passed.");
}

Pitfall: Common Failure Cases

गलती तब होती है जब schema storage की आसानी से बनता है, safe query से नहीं। valid JSON भी tenant mix कर सकता है।

The first failure is assuming that a tenantId field alone is enough. The query, API authorization, and tests must also carry the tenant boundary.

The second failure is choosing id as the partition key because every document has one. Support screens usually query by tenant and time, so the key should follow real access patterns.

The third failure is sending raw support text into search or AI summaries without masking.

FAQ

Q. क्या support text raw रखना चाहिए?\n\nA. तभी जब retention और masking rule साफ हो। token, secret, email और contract details अलग संभालें।

Q. Is tenantId always the right partition key?

A. No. It is a strong candidate for many SaaS screens, but large tenants, query patterns, retention, and cost can change the answer.

Q. Should ticket and message be in one container?

A. Sometimes. Keep review simple by comparing query patterns, retention, permissions, and AI-summary use before deciding.

Q. Where should teams go next?

A. Teams that need Cosmos DB schema, partition key, support history, masking, and authorization review can start from ClaudeCodeLab training.

What I Verified

Hindi version में SaaS support screen, tenant isolation, support history, masking और training CTA रखा गया। I checked Cosmos DB partitioning, multitenancy, hierarchical partition keys, data modeling, unique keys, and Cosmos DB security. I also checked the CTA, internal link, executable JavaScript, and locale coverage.