# Design Documents ## Table of Contents - [Design Documents](#design-documents) - [Table of Contents](#table-of-contents) - [Introduction](#introduction) - [Motivation or Background](#motivation-or-background) - [Detailed Design](#detailed-design) - [Goals and Non-goals](#goals-and-non-goals) - [Assumptions](#assumptions) - [Active Tenant Criteria](#active-tenant-criteria) - [High-level Flow](#high-level-flow) - [Component Changes](#component-changes) - [1) New Active EMEA Tenant Report Service](#1-new-active-emea-tenant-report-service) - [2) Billing Client Extension](#2-billing-client-extension) - [3) Tenant Candidate Cache Store](#3-tenant-candidate-cache-store) - [4) Domain HQ Enrichment Store](#4-domain-hq-enrichment-store) - [5) Daily Report CSV Store](#5-daily-report-csv-store) - [6) Lark Sheet Client](#6-lark-sheet-client) - [7) Lark Group Notification](#7-lark-group-notification) - [Configuration Design](#configuration-design) - [Error Handling and Retry](#error-handling-and-retry) - [Operational Notes](#operational-notes) - [Test Design](#test-design) - [Functional Tests](#functional-tests) - [Scenario Tests](#scenario-tests) - [Compatibility Tests](#compatibility-tests) - [Impacts \& Risks](#impacts--risks) - [Unresolved Questions](#unresolved-questions) ## Introduction This document describes a daily automation in `devops-work` that scans active external EMEA tenants, identifies tenants that meet the agreed active tenant criteria, writes the result into a newly created Lark spreadsheet, and sends a summary notification to a configured Lark group. The report is a daily snapshot. Each run creates a new spreadsheet instead of overwriting a long-lived spreadsheet. ## Motivation or Background The business team needs a daily list of active EMEA tenants around the Frankfurt/London motion. The criteria combine tenant metadata, current org country, cluster existence, valid credit card binding, and company headquarters inferred from the tenant owner email domain. The implementation should fit the current repository patterns: 1. Run as a backend cron job. 2. Reuse existing tenant, account, core service, billing, LLM enrichment, and Lark integrations. 3. Persist the daily report result as a CSV file in S3 for auditability and retry. 4. Cache domain enrichment results to avoid repeated LLM searches and unstable daily output. ## Detailed Design ### Goals and Non-goals Goals: 1. Scan current tenants with `status=active`, `kind=external`, and `region=EMEA`. 2. Further filter tenants by current AccountService org country. 3. Require at least one undeleted cluster. 4. Require at least one non-expired Stripe card payment method. 5. Require the owner email domain's company headquarters to be in EMEA. 6. Create a new Lark spreadsheet for each daily run. 7. Send a Lark group notification with report summary and spreadsheet link. 8. Persist report CSV files in S3 and enrichment results in local database tables. 9. Support `dryRun` for safe rollout. Non-goals: 1. No cluster type or cluster plan filtering in the first version. 2. No manual remediation workflow for failed rows. 3. No real-time event processing; this is a daily snapshot report. 4. No attempt to infer London from city names. The first version uses AccountService country code `GB`. ### Assumptions This design is based on the following assumptions: 1. `accountCli.GetTenant().Country` returns an ISO-like country code. 2. London-targeted orgs can be identified by country code `GB`. 3. Frankfurt-targeted orgs can be identified by country code `DE`. 4. A tenant with one or more undeleted clusters satisfies the cluster activity criterion. 5. A tenant is treated as credit-card-bound if any active billing config has at least one Stripe payment method with a non-expired `card`. 6. Credit card expiration is evaluated against the current UTC year and month. 7. `org.Email` is the owner/signup email whose domain should be enriched. 8. LLM domain enrichment can return a headquarters country code. If any of these assumptions become false, this design should be revised instead of adding ad hoc exceptions. ### Active Tenant Criteria A tenant is included in the daily report only if all criteria below are true: 1. Local tenant record: - `status = active` - `kind = external` - `region = EMEA` 2. AccountService org country: - `org.Country` is in `accountCountryAllowList` - first version configuration: `["DE", "GB"]` 3. Cluster existence: - the tenant has at least one undeleted cluster - cluster type and service plan are not checked in the first version 4. Billing: - at least one active billing config exists - at least one Stripe payment method under that billing config has a `card` - the card is not expired 5. Company headquarters: - derive email domain from `org.Email` - enrich the domain to obtain `headquarter_country_code` - `region.CountryToRegion(headquarter_country_code) == EMEA` ### High-level Flow For each cron run: 1. Acquire a process-local mutex to avoid overlapping runs. 2. Query candidate tenants through `tenantSvc.ListTenants`. 3. Upsert all first-step candidates into `active_emea_tenant_candidates`, updating only local tenant snapshot fields. 4. For each candidate tenant: 5. Load the cached tenant candidate profile. 6. Refresh AccountService org metadata only when the profile is missing or older than `candidateProfileTTL`. 7. Skip if account country is not `DE` or `GB`. 8. Query core service for undeleted clusters by tenant ID on every run. 9. Skip if cluster count is `0`. 10. Try the cached `billing_config_id` first for Stripe payment methods. 11. If cached billing config is missing or no longer has a valid card, query active billing configs and refresh the cached billing config. 12. Skip if there is no non-expired card. 13. Extract domain from cached account email. 14. Load domain enrichment from local table. 15. If enrichment is missing, expired, or on an old schema version, call LLM enrichment and upsert the result. 16. Skip if headquarters region is not EMEA. 17. Write the included report rows to a daily CSV file in S3 and update candidate last-observed fields. 18. Create a new Lark spreadsheet and write the report header and rows. 19. Send a Lark group notification with summary statistics, spreadsheet link, and S3 CSV key. Pseudo-code: ```go func RunOnce(ctx context.Context) error { if !mutex.TryLock() { return nil } defer mutex.Unlock() run := newReportRun(nowUTC()) candidates := tenantSvc.ListTenants(ctx, ListTenantsParam{ Filter: ListTenantsFilter{ Status: []TenantStatus{TenantStatusActive}, Kind: []TenantKind{TenantKindExternal}, Region: []TenantRegion{TenantRegionEMEA}, }, }) tenantCandidateStore.UpsertSnapshots(candidates) for _, tenant := range candidates { candidate := tenantCandidateStore.GetOrRefreshProfile(ctx, tenant) if !countryAllowed(candidate.AccountCountry) { run.Skip(tenant, "account_country_not_allowed") continue } clusters := coresvcCli.Mgmt.ListClusters(ctx, ListClustersParam{ TenantIDs: []uint64{tenant.Id}, }) if len(clusters) < conf.MinActiveClusterCount { run.Skip(tenant, "no_undeleted_cluster") continue } tenantCandidateStore.UpdateClusterObservation(tenant.Id, clusters) card := findNonExpiredStripeCard(ctx, tenant.Id, candidate.BillingConfigID) if card == nil { run.Skip(tenant, "no_non_expired_card") continue } tenantCandidateStore.UpdateBillingObservation(tenant.Id, card.BillingConfigID) domain := candidate.EmailDomain enrichment := domainEnrichmentStore.GetOrRefresh(ctx, domain) if enrichment.HeadquarterRegion != TenantRegionEMEA { run.Skip(tenant, "headquarter_not_emea") continue } run.Include(buildReportItem(candidate, clusters, card, enrichment)) } csvKey := reportStore.WriteCSV(ctx, run.Date, run.Rows()) spreadsheet := larkSheets.CreateSpreadsheet(ctx, titleFor(run.Date)) larkSheets.WriteRange(ctx, spreadsheet.Token, spreadsheet.SheetID, "A1", run.Rows()) notifyLarkGroup(ctx, run, spreadsheet.URL, csvKey) return nil } ``` ### Component Changes #### 1) New Active EMEA Tenant Report Service Add a new service package, for example: ```text pkg/service/activeemeatenant ``` The service owns: 1. Cron registration. 2. Candidate tenant scanning. 3. Per-tenant filtering. 4. Report CSV persistence. 5. Lark spreadsheet creation and notification. Suggested public shape: ```go type ServiceParam struct { dig.In Conf *config.RootConfig Logger logr.Logger TenantSvc tenant.TenantServiceAPI AccountCli accountcli.Client CoreSvcCli *coresvccli.CoreSvcCli BillingCli billingcli.Client LarkCli *larkcli.LarkClient LLMClient *llm_cli.LLMCli Repo Repository } func NewService(p ServiceParam) (ServiceResult, error) func (s *svc) RunOnce(ctx context.Context) error ``` The cron should use `service.MustNewCronJob` like existing services. #### 2) Billing Client Extension Extend `pkg/infra/clients/billingcli.Client` with: ```go ListStripePaymentMethods( ctx context.Context, tenantID uint64, billingConfigID uint64, operatorEmail string, ) ([]*StripePaymentMethod, error) ``` Endpoint: ```text GET /api/v1/billing/tenants/{tenant_id}/billing_config_id/{billing_config_id}/payments/stripe/payment_methods ``` Response structs: ```go type StripePaymentMethod struct { ID string `json:"id"` Card *StripeCard `json:"card"` BillingDetails *StripeBillingDetails `json:"billing_details"` } type StripeCard struct { Brand string `json:"brand"` Country string `json:"country"` ExpMonth string `json:"exp_month"` ExpYear string `json:"exp_year"` Fingerprint string `json:"fingerprint"` Funding string `json:"funding"` Last4 string `json:"last4"` } type StripeBillingDetails struct { Address *StripeBillingAddress `json:"address"` Email string `json:"email"` Name string `json:"name"` Phone string `json:"phone"` } type StripeBillingAddress struct { City string `json:"city"` Country string `json:"country"` Line1 string `json:"line1"` Line2 string `json:"line2"` PostalCode string `json:"postal_code"` State string `json:"state"` } ``` Card validity helper: ```go func isNonExpiredCard(card *billingcli.StripeCard, now time.Time) bool { if card == nil { return false } expYear, err := strconv.Atoi(strings.TrimSpace(card.ExpYear)) if err != nil { return false } expMonth, err := strconv.Atoi(strings.TrimSpace(card.ExpMonth)) if err != nil || expMonth < 1 || expMonth > 12 { return false } now = now.UTC() if expYear < now.Year() { return false } if expYear == now.Year() && expMonth < int(now.Month()) { return false } return true } ``` A tenant passes the credit card criterion when any active billing config has any payment method with a non-expired `Card`. #### 3) Tenant Candidate Cache Store Add a tenant candidate table for the first-step candidate set and low-volatility profile fields. This table is a cache and an audit aid, not the final active-tenant source of truth. Fields to cache: 1. Local tenant snapshot: tenant ID, name, email, status, kind, region, last candidate time. 2. AccountService profile: account name, account email, account country, email domain, last account sync time. 3. Billing profile: latest valid billing config ID, billing config status, last billing sync time. 4. Last dynamic observations: cluster count, cluster IDs, last cluster sync time, last included time, last skip reason. Refresh policy: 1. The first-step tenant snapshot is updated on every run. 2. AccountService profile is refreshed when missing or older than `candidateProfileTTL`. 3. Cached `billing_config_id` is tried first. If it does not produce a non-expired card, the job falls back to listing active billing configs and updates the cached billing config. 4. Active cluster count is always queried from core service on every run. #### 4) Domain HQ Enrichment Store Add a domain enrichment table to cache LLM results and keep report output stable. Suggested model: ```go type CompanyDomainEnrichment struct { base.Updatable Domain string `gorm:"type:VARCHAR(255);not null;uniqueIndex"` CompanyName string `gorm:"type:VARCHAR(255);not null"` Headquarter string `gorm:"type:VARCHAR(255);not null"` HeadquarterCountryCode string `gorm:"type:VARCHAR(16);not null"` HeadquarterRegion string `gorm:"type:VARCHAR(32);not null"` Vertical string `gorm:"type:VARCHAR(255);not null"` BusinessModel string `gorm:"type:VARCHAR(255);not null"` ConfidenceLevel string `gorm:"type:VARCHAR(32);not null"` RawEnrichment string `gorm:"type:TEXT;not null"` Source string `gorm:"type:VARCHAR(64);not null"` SchemaVersion string `gorm:"type:VARCHAR(32);not null"` ExpiresAt *time.Time `gorm:"index"` } ``` The LLM prompt should require JSON output and include a country code field: ```json { "company_name": "company name or Unknown", "headquarter": "city, region, country or Unknown", "headquarter_country_code": "ISO 3166-1 alpha-2 country code or Unknown", "vertical": "industry vertical or Unknown", "business_model": "B2B/B2C/B2B2C or Unknown", "ai_confidence_level": "high/medium/low" } ``` The report should only include tenants whose `headquarter_country_code` maps to `TenantRegionEMEA` through `region.CountryToRegion`. #### 5) Daily Report CSV Store Write the final included rows to S3 as CSV. The CSV file is the daily report snapshot and can be used for audit or Lark retry without growing MySQL by one row per tenant per day. Suggested key format: ```text active-emea-tenant-report/date=2026-05-09/report_20260509010000.csv ``` The CSV columns match the Lark sheet columns: ```text Report Date,Tenant ID,Tenant Name,Org Email,Email Domain,Account Country,Tenant Region,Cluster Count,Cluster IDs,Billing Config ID,Card Brand,Card Country,Card Last4,Company Name,Headquarter,Headquarter Country Code,Headquarter Region,Confidence Level,Ops URL ``` Use the existing `pkg/infra/asset` abstraction, which writes to the configured S3 asset bucket. #### 6) Lark Sheet Client The current project has Lark IM and document clients, but no Lark spreadsheet writer. Add a client under: ```text pkg/infra/clients/larkcli/sheets ``` Minimum required API: ```go type Sheets interface { CreateSpreadsheet(ctx context.Context, title string, folderToken *string) (*Spreadsheet, error) WriteRange(ctx context.Context, spreadsheetToken string, sheetID string, startCell string, values [][]any) error } type Spreadsheet struct { Token string SheetID string URL string } ``` `larkcli.LarkClient` should include the new client: ```go type LarkClient struct { Raw *lark.Client Notify notify.Notification Contact contact.Contact Task task.Task Chat chat.Chat Document document.Document IM im.IM Sheets sheets.Sheets } ``` The daily job writes one header row plus all included tenant rows. Because a new spreadsheet is created for every run, no clear/overwrite operation is needed in the first version. #### 7) Lark Group Notification Use `larkCli.IM.SendMessage(ctx, "chat_id", conf.NotifyChatID, msg)` for group notification. The notification should include: 1. Report date. 2. Candidate tenant count. 3. Included tenant count. 4. Skip counts by reason. 5. Failed row count. 6. Link to the newly created Lark spreadsheet. 7. S3 CSV key. Example message: ```text Active EMEA tenant report completed. Date: 2026-05-09 Candidates: 120 Included: 18 Skipped: country=40, cluster=20, card=30, hq=10 Failed: 2 Sheet: https://... S3 CSV: active-emea-tenant-report/date=2026-05-09/report_20260509010000.csv ``` ### Configuration Design Add service config: ```go type ActiveEMEATenantReportConfig struct { CronDisable bool `yaml:"cronDisable"` // default false CronSpec string `yaml:"cronSpec"` DryRun bool `yaml:"dryRun"` OperatorEmail string `yaml:"operatorEmail"` AccountCountryAllowList []string `yaml:"accountCountryAllowList"` MinActiveClusterCount *int `yaml:"minActiveClusterCount"` CandidateProfileTTL time.Duration `yaml:"candidateProfileTTL"` EnrichmentTTL time.Duration `yaml:"enrichmentTTL"` EnrichmentSchemaVersion string `yaml:"enrichmentSchemaVersion"` ReportS3Prefix string `yaml:"reportS3Prefix"` LarkFolderToken string `yaml:"larkFolderToken"` LarkAppCode string `yaml:"larkAppCode"` LarkAppSecret string `yaml:"larkAppSecret"` NotifyChatID string `yaml:"notifyChatID"` } ``` Example: ```yaml services: activeEMEATenant: cronDisable: false cronSpec: "0 9 * * *" dryRun: false operatorEmail: "devops@pingcap.com" accountCountryAllowList: ["DE", "GB"] minActiveClusterCount: 1 candidateProfileTTL: "24h" enrichmentTTL: "720h" enrichmentSchemaVersion: "v1" reportS3Prefix: "active-emea-tenant-report" larkFolderToken: "fldxxx" larkAppCode: "cli_xxx" # optional, dedicated bot for this report larkAppSecret: "secret_xxx" # optional, must be set with larkAppCode notifyChatID: "oc_xxx" ``` `cronDisable` defaults to `false` to avoid boolean zero-value merge issues when explicitly setting `cronDisable: false` in environment-specific config. If `larkAppCode` and `larkAppSecret` are both set, the report service uses that dedicated Lark bot for spreadsheet creation, writing, and group notification. If they are empty, it falls back to `clients.lark.global`. `minActiveClusterCount: 0` can be used in dry-run tests to skip the active cluster gate and exercise the billing card and HQ enrichment checks. ### Error Handling and Retry Per-tenant failures should not fail the entire run unless a shared dependency is unavailable. Recommended behavior: 1. Tenant query failure: fail the run. 2. AccountService failure for one tenant: record failed row reason and continue. 3. Core service failure for one tenant: record failed row reason and continue. 4. Billing failure for one tenant: record failed row reason and continue. 5. Enrichment failure for one domain: record failed row reason and continue. 6. S3 CSV write failure: fail the run before creating the Lark spreadsheet. 7. Lark spreadsheet creation/write failure: fail the run after the CSV file is saved. 8. Lark notification failure: return error so the cron job reports failure, but keep the saved CSV file. The S3 CSV file allows retrying the Lark write/notification part without recomputing all tenant eligibility. ### Operational Notes 1. Run in `dryRun` first and log the computed rows without creating a spreadsheet or sending a notification. 2. Keep skip reason metrics in logs for daily troubleshooting. 3. Do not log full card fingerprints or billing addresses. Report output should include only masked card metadata such as brand, card country, and last4. 4. The domain enrichment table should expire rows using `expires_at` and schema version. Changing prompt output should bump `enrichmentSchemaVersion`. 5. If `account.Country` later uses `UK` instead of `GB`, add it to `accountCountryAllowList` by configuration. ## Test Design ### Functional Tests 1. Candidate tenant query builds filters for active, external, EMEA. 2. `DE` and `GB` account countries pass the country filter. 3. Non-allowed countries are skipped. 4. Cluster count `0` skips the tenant. 5. Cluster count `1` passes the cluster criterion. 6. Active billing config with non-expired card passes. 7. Expired card is skipped. 8. Missing card is skipped. 9. HQ country code mapping to EMEA passes. 10. HQ country code mapping to NA/APAC/JAPAN/UNKNOWN skips. 11. Daily report CSV contains expected headers and values. 12. Lark spreadsheet rows contain expected headers and values. 13. Lark notification contains expected summary counts, sheet link, and S3 CSV key. ### Scenario Tests 1. Full successful run with mixed included/skipped tenants. 2. Domain enrichment cache hit. 3. Domain enrichment cache expired and refreshed. 4. Billing API error for one tenant does not stop the whole run. 5. Lark write failure after CSV persistence returns error. 6. Dry run performs filtering but does not create Lark spreadsheet or send notification. ### Compatibility Tests 1. Existing tenant sync and tenant APIs continue to work. 2. Existing billing client methods continue to work. 3. Existing Lark IM/Notify/Document clients continue to work after adding `Sheets`. 4. Database migration is forward-compatible and adds only new tables. 5. Mock generation is updated for the extended billing client. ## Impacts & Risks Impacts: 1. Adds a daily scan job that calls AccountService, CoreService, BillingService, LLM, and Lark APIs. 2. Adds two local tables for enrichment cache and tenant candidate cache. 3. Writes one CSV report file per successful non-dry-run execution to the configured asset S3 bucket. 3. Adds a new billing client method and a new Lark sheet client. Risks: 1. AccountService country code may differ from `GB`. Mitigation: keep `accountCountryAllowList` configurable. 2. LLM enrichment may return incorrect headquarters country. Mitigation: cache raw response, confidence, and country code; allow manual inspection. 3. LLM cost may increase if cache TTL is too short. Mitigation: use long TTL and schema versioning. 4. Stripe payment method response may change. Mitigation: parse only required fields and treat malformed card expiration as invalid. 5. Daily full scans may create load spikes. Mitigation: page tenants and limit per-tenant concurrency. 6. Lark spreadsheet API permissions may be missing. Mitigation: validate permissions in staging and fail clearly. ## Unresolved Questions 1. Whether `accountCli.GetTenant().Country` always uses `GB` for the United Kingdom. The first implementation uses `GB` and keeps the list configurable. 2. The exact Lark folder where daily spreadsheets should be created. 3. Whether the report should include failed tenant rows in a separate sheet or only in logs and database.