systemprompt_cli/commands/cloud/db/
mod.rs

1//! `cloud db` subcommands operating on a remote tenant database.
2//!
3//! Resolves a profile's database URL from its secrets, then routes most
4//! commands through the shared infrastructure [`db`] handlers; backup and
5//! restore are handled locally via `pg_dump`/`pg_restore`.
6
7mod backup;
8mod restore;
9
10use anyhow::{Context, Result, anyhow, bail};
11use clap::{Subcommand, ValueEnum};
12use std::path::PathBuf;
13use std::process::Command;
14use systemprompt_cloud::{ProfilePath, ProjectContext};
15use systemprompt_runtime::DatabaseContext;
16
17use crate::cli_settings::CliConfig;
18use crate::commands::infrastructure::db;
19
20#[derive(Debug, Subcommand)]
21pub enum CloudDbCommands {
22    #[command(about = "Run migrations on cloud database")]
23    Migrate {
24        #[arg(long, help = "Profile name")]
25        profile: String,
26    },
27
28    #[command(about = "Execute SQL query (read-only) on cloud database")]
29    Query {
30        #[arg(long, help = "Profile name")]
31        profile: String,
32        sql: String,
33        #[arg(long)]
34        limit: Option<u32>,
35        #[arg(long)]
36        offset: Option<u32>,
37        #[arg(long)]
38        format: Option<String>,
39    },
40
41    #[command(about = "Execute write operation on cloud database")]
42    Execute {
43        #[arg(long, help = "Profile name")]
44        profile: String,
45        sql: String,
46        #[arg(long)]
47        format: Option<String>,
48    },
49
50    #[command(about = "Validate cloud database schema")]
51    Validate {
52        #[arg(long, help = "Profile name")]
53        profile: String,
54    },
55
56    #[command(about = "Show cloud database connection status")]
57    Status {
58        #[arg(long, help = "Profile name")]
59        profile: String,
60    },
61
62    #[command(about = "Show cloud database info")]
63    Info {
64        #[arg(long, help = "Profile name")]
65        profile: String,
66    },
67
68    #[command(about = "List all tables in cloud database")]
69    Tables {
70        #[arg(long, help = "Profile name")]
71        profile: String,
72        #[arg(long, help = "Filter tables by pattern")]
73        filter: Option<String>,
74    },
75
76    #[command(about = "Describe table schema in cloud database")]
77    Describe {
78        #[arg(long, help = "Profile name")]
79        profile: String,
80        table_name: String,
81    },
82
83    #[command(about = "Get row count for a table in cloud database")]
84    Count {
85        #[arg(long, help = "Profile name")]
86        profile: String,
87        table_name: String,
88    },
89
90    #[command(about = "List all indexes in cloud database")]
91    Indexes {
92        #[arg(long, help = "Profile name")]
93        profile: String,
94        #[arg(long, help = "Filter by table name")]
95        table: Option<String>,
96    },
97
98    #[command(about = "Show cloud database and table sizes")]
99    Size {
100        #[arg(long, help = "Profile name")]
101        profile: String,
102    },
103
104    #[command(about = "Backup cloud database using pg_dump")]
105    Backup {
106        #[arg(long, help = "Profile name")]
107        profile: String,
108
109        #[arg(
110            long,
111            default_value = "custom",
112            help = "Backup format: custom, sql, directory"
113        )]
114        format: BackupFormat,
115
116        #[arg(
117            long,
118            help = "Output file path (default: backups/<profile>-<timestamp>.<ext>)"
119        )]
120        output: Option<String>,
121    },
122
123    #[command(about = "Restore cloud database from a backup file")]
124    Restore {
125        #[arg(long, help = "Profile name")]
126        profile: String,
127
128        #[arg(help = "Path to backup file")]
129        file: String,
130
131        #[arg(short = 'y', long, help = "Skip confirmation prompt")]
132        yes: bool,
133    },
134}
135
136#[derive(Debug, Clone, Copy, ValueEnum)]
137pub enum BackupFormat {
138    #[value(help = "pg_dump custom format (-Fc), supports parallel restore")]
139    Custom,
140    #[value(help = "Plain SQL text format (-Fp), human-readable")]
141    Sql,
142    #[value(help = "Directory format (-Fd), supports parallel dump and restore")]
143    Directory,
144}
145
146impl CloudDbCommands {
147    fn profile_name(&self) -> &str {
148        match self {
149            Self::Migrate { profile }
150            | Self::Query { profile, .. }
151            | Self::Execute { profile, .. }
152            | Self::Validate { profile }
153            | Self::Status { profile }
154            | Self::Info { profile }
155            | Self::Tables { profile, .. }
156            | Self::Describe { profile, .. }
157            | Self::Count { profile, .. }
158            | Self::Indexes { profile, .. }
159            | Self::Size { profile }
160            | Self::Backup { profile, .. }
161            | Self::Restore { profile, .. } => profile,
162        }
163    }
164
165    fn into_db_command(self) -> Option<db::DbCommands> {
166        match self {
167            Self::Migrate { .. } => Some(db::DbCommands::Migrate {
168                allow_checksum_drift: false,
169            }),
170            Self::Query {
171                sql,
172                limit,
173                offset,
174                format,
175                ..
176            } => Some(db::DbCommands::Query {
177                sql,
178                limit,
179                offset,
180                format,
181            }),
182            Self::Execute { sql, format, .. } => Some(db::DbCommands::Execute { sql, format }),
183            Self::Validate { .. } => Some(db::DbCommands::Validate),
184            Self::Status { .. } => Some(db::DbCommands::Status),
185            Self::Info { .. } => Some(db::DbCommands::Info),
186            Self::Tables { filter, .. } => Some(db::DbCommands::Tables { filter }),
187            Self::Describe { table_name, .. } => Some(db::DbCommands::Describe { table_name }),
188            Self::Count { table_name, .. } => Some(db::DbCommands::Count { table_name }),
189            Self::Indexes { table, .. } => Some(db::DbCommands::Indexes { table }),
190            Self::Size { .. } => Some(db::DbCommands::Size),
191            Self::Backup { .. } | Self::Restore { .. } => None,
192        }
193    }
194}
195
196pub async fn execute(cmd: CloudDbCommands, config: &CliConfig) -> Result<()> {
197    let profile_name = cmd.profile_name().to_owned();
198    let db_url = load_cloud_database_url(&profile_name)?;
199    execute_inner(cmd, &profile_name, &db_url, config).await
200}
201
202pub async fn execute_with_database_url(
203    cmd: CloudDbCommands,
204    database_url: &str,
205    config: &CliConfig,
206) -> Result<()> {
207    let profile_name = cmd.profile_name().to_owned();
208    execute_inner(cmd, &profile_name, database_url, config).await
209}
210
211async fn execute_inner(
212    cmd: CloudDbCommands,
213    profile_name: &str,
214    db_url: &str,
215    config: &CliConfig,
216) -> Result<()> {
217    match &cmd {
218        CloudDbCommands::Backup { format, output, .. } => {
219            return backup::execute(profile_name, db_url, *format, output.as_deref());
220        },
221        CloudDbCommands::Restore { file, yes, .. } => {
222            return restore::execute(profile_name, db_url, file, *yes, config);
223        },
224        _ => {},
225    }
226
227    let db_ctx = DatabaseContext::from_url(db_url).await?;
228    let db_cmd = cmd
229        .into_db_command()
230        .ok_or_else(|| anyhow!("Unexpected command variant"))?;
231
232    db::execute_with_db(db_cmd, &db_ctx, config).await
233}
234
235fn load_cloud_database_url(profile_name: &str) -> Result<String> {
236    let ctx = ProjectContext::discover();
237    let profile_dir = ctx.profile_dir(profile_name);
238
239    if !profile_dir.exists() {
240        return Err(anyhow!("Profile '{}' not found", profile_name));
241    }
242
243    let secrets_path = ProfilePath::Secrets.resolve(&profile_dir);
244    let secrets = systemprompt_config::load_secrets_from_path(&secrets_path)
245        .with_context(|| format!("Failed to load secrets for profile '{}'", profile_name))?;
246
247    Ok(secrets.effective_database_url(true).to_owned())
248}
249
250fn ensure_pg_tool(tool: &str) -> Result<()> {
251    match Command::new(tool).arg("--version").output() {
252        Ok(output) if output.status.success() => Ok(()),
253        _ => bail!(
254            "'{}' not found. Install PostgreSQL client tools:\n  apt install postgresql-client",
255            tool
256        ),
257    }
258}
259
260fn find_pg_dump() -> Result<PathBuf> {
261    for version in [17, 16, 15, 14] {
262        let path = PathBuf::from(format!("/usr/lib/postgresql/{}/bin/pg_dump", version));
263        if path.exists() {
264            return Ok(path);
265        }
266    }
267    ensure_pg_tool("pg_dump")?;
268    Ok(PathBuf::from("pg_dump"))
269}
270
271fn find_pg_restore() -> Result<PathBuf> {
272    for version in [17, 16, 15, 14] {
273        let path = PathBuf::from(format!("/usr/lib/postgresql/{}/bin/pg_restore", version));
274        if path.exists() {
275            return Ok(path);
276        }
277    }
278    ensure_pg_tool("pg_restore")?;
279    Ok(PathBuf::from("pg_restore"))
280}
281
282fn adjust_ssl_mode(database_url: &str) -> String {
283    database_url.replace("sslmode=require", "sslmode=prefer")
284}
systemprompt_cli/commands/cloud/db/mod.rs

systemprompt_cli/commands/cloud/db/
mod.rs
