Circle V2 API Docs
    Preparing search index...

    Function buildBaseCtes

    • Shared CTE foundation for analytics queries.

      Produces three CTEs:

      • base_runs — deduplicated (latest run per patient × template) with metadata; patients with no runs appear with NULL run columns when no run-specific filters (dateRange, auditTemplateIds) are active.
      • run_scores — per-run pass/total/pct aggregates (excludes summary + not_applicable)
      • run_data — base_runs enriched with score columns

      Both the metrics and review-dashboard analytics layers build on top of run_data.

      Parameters

      Returns QueryCreatorWithCommonTableExpression<
          DrizzleToKyesely & {
              base_runs: Partial<Omit<unknown, never>> & {
                  audit_template_id: string & { __brand: "AuditTemplateId" } | null;
                  audit_template_name: string | null;
                  case_id: CaseId | null;
                  client_id: PatientId;
                  client_name: string;
                  creator_name: string;
                  creator_profile_id: ProfileId | null;
                  facility_id: FacilityId | null;
                  facility_name: string;
                  is_chart_review: boolean;
                  is_patient_case: boolean | null;
                  primary_provider_name: string;
                  primary_provider_profile_id: ProfileId | null;
                  provider_name: string;
                  provider_profile_id: ProfileId | null;
                  run_created_at: DbDate | null;
                  run_id: AuditRunId | null;
                  trigger_id: string | null;
              };
          } & {
              run_scores: {
                  pass_count: unknown;
                  run_id: string & { __brand: "AuditRunId" }
                  | null;
                  score_pct: unknown;
                  total_count: unknown;
              };
          } & {
              run_manual_counts: {
                  manual_pending_count: unknown;
                  run_id: AuditRunId
                  | null;
              };
          },
          "run_data",
          (
              qb: QueryCreator<
                  DrizzleToKyesely & {
                      base_runs: Partial<Omit<unknown, never>> & {
                          audit_template_id: string & { __brand: "AuditTemplateId" } | null;
                          audit_template_name: string | null;
                          case_id: CaseId | null;
                          client_id: PatientId;
                          client_name: string;
                          creator_name: string;
                          creator_profile_id: ProfileId | null;
                          facility_id: FacilityId | null;
                          facility_name: string;
                          is_chart_review: boolean;
                          is_patient_case: boolean | null;
                          primary_provider_name: string;
                          primary_provider_profile_id: ProfileId | null;
                          provider_name: string;
                          provider_profile_id: ProfileId | null;
                          run_created_at: DbDate | null;
                          run_id: AuditRunId | null;
                          trigger_id: string | null;
                      };
                  } & {
                      run_scores: {
                          pass_count: unknown;
                          run_id: string & { __brand: "AuditRunId" }
                          | null;
                          score_pct: unknown;
                          total_count: unknown;
                      };
                  } & {
                      run_manual_counts: {
                          manual_pending_count: unknown;
                          run_id: AuditRunId
                          | null;
                      };
                  },
              >,
          ) => SelectQueryBuilder<
              DrizzleToKyesely & {
                  base_runs: Partial<Omit<unknown, never>> & {
                      audit_template_id: string & { __brand: "AuditTemplateId" } | null;
                      audit_template_name: string | null;
                      case_id: CaseId | null;
                      client_id: PatientId;
                      client_name: string;
                      creator_name: string;
                      creator_profile_id: ProfileId | null;
                      facility_id: FacilityId | null;
                      facility_name: string;
                      is_chart_review: boolean;
                      is_patient_case: boolean | null;
                      primary_provider_name: string;
                      primary_provider_profile_id: ProfileId | null;
                      provider_name: string;
                      provider_profile_id: ProfileId | null;
                      run_created_at: DbDate | null;
                      run_id: AuditRunId | null;
                      trigger_id: string | null;
                  };
              } & {
                  run_scores: {
                      pass_count: unknown;
                      run_id: string & { __brand: "AuditRunId" }
                      | null;
                      score_pct: unknown;
                      total_count: unknown;
                  };
              } & {
                  run_manual_counts: {
                      manual_pending_count: unknown;
                      run_id: AuditRunId
                      | null;
                  };
              } & {
                  br: Partial<Omit<unknown, never>> & {
                      audit_template_id: string & { __brand: "AuditTemplateId" } | null;
                      audit_template_name: string | null;
                      case_id: CaseId | null;
                      client_id: PatientId;
                      client_name: string;
                      creator_name: string;
                      creator_profile_id: ProfileId | null;
                      facility_id: FacilityId | null;
                      facility_name: string;
                      is_chart_review: boolean;
                      is_patient_case: boolean | null;
                      primary_provider_name: string;
                      primary_provider_profile_id: ProfileId | null;
                      provider_name: string;
                      provider_profile_id: ProfileId | null;
                      run_created_at: DbDate | null;
                      run_id: AuditRunId | null;
                      trigger_id: string | null;
                  };
              } & {
                  rs: Nullable<
                      {
                          pass_count: unknown;
                          run_id: string & { __brand: "AuditRunId" }
                          | null;
                          score_pct: unknown;
                          total_count: unknown;
                      },
                  >;
              } & {
                  rmc: Nullable<
                      { manual_pending_count: unknown; run_id: AuditRunId
                      | null },
                  >;
              },
              "br"
              | "rs"
              | "rmc",
              Partial<Omit<unknown, never>> & {
                  audit_template_id: string & { __brand: "AuditTemplateId" } | null;
                  audit_template_name: string | null;
                  case_id: CaseId | null;
                  client_id: PatientId;
                  client_name: string;
                  creator_name: string;
                  creator_profile_id: ProfileId | null;
                  facility_id: FacilityId | null;
                  facility_name: string;
                  is_chart_review: boolean;
                  is_patient_case: boolean | null;
                  primary_provider_name: string;
                  primary_provider_profile_id: ProfileId | null;
                  provider_name: string;
                  provider_profile_id: ProfileId | null;
                  run_created_at: DbDate | null;
                  run_id: AuditRunId | null;
                  trigger_id: string | null;
              } & {
                  manual_pending_count: number;
                  pass_count: number;
                  score_pct: number;
                  total_count: number;
              },
          >,
      >

    Settings

    Member Visibility