turbot/vercel

GitHub
steampipe plugin install vercelsteampipe plugin install vercel

Table: vercel_project

List projects in your account.

Examples

List all projects

select
name,
framework,
updated_at
from
vercel_project;

Projects not updated in the last year

select
name,
framework,
updated_at
from
vercel_project
where
updated_at < now() - interval '1 year';

Latest deployments

select
to_timestamp((d ->> 'createdAt') :: bigint / 1000) as created_at,
name,
d ->> 'url' as url,
d -> 'creator' ->> 'username' as creator_username
from
vercel_project as p,
jsonb_array_elements(latest_deployments) as d
order by
created_at desc;

Current production target by project

select
name,
to_timestamp(
(targets -> 'production' ->> 'createdAt') :: bigint / 1000
) as created_at,
targets -> 'production' ->> 'url' as url,
targets -> 'production' -> 'meta' ->> 'githubOrg' as github_org,
targets -> 'production' -> 'meta' ->> 'githubRepo' as github_repo,
targets -> 'production' -> 'meta' ->> 'githubCommitSha' as github_commit_sha,
targets -> 'production' -> 'meta' ->> 'githubCommitAuthorName' as github_commit_author_name,
targets -> 'production' -> 'meta' ->> 'githubCommitMessage' as github_commit_message
from
vercel_project
order by
name;

List all project environment variables

select
name,
e ->> 'key',
e ->> 'target'
from
vercel_project as p,
jsonb_array_elements(env) as e;

Environment variables that are not encrypted

select
name,
e ->> 'key',
e ->> 'type'
from
vercel_project as p,
jsonb_array_elements(env) as e
where
e ->> 'type' != 'encrypted';

Production environment variables older than 90 days

select
name,
e ->> 'key',
e ->> 'target',
to_timestamp((e ->> 'createdAt') :: bigint / 1000)
from
vercel_project as p,
jsonb_array_elements(env) as e
where
e -> 'target' ? 'production'
and to_timestamp((e ->> 'createdAt') :: bigint / 1000) < now() - interval '90 days';

Projects by framework environment variables older than 90 days

select
framework,
count(id)
from
vercel_project
group by
framework
order by
count desc;

.inspect vercel_project

Projects in the Vercel account.

NameTypeDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
accountidtextAccount ID for the project.
aliasjsonb
analyticsjsonbAnalytics information, if enabled for the project.
auto_expose_system_envsbooleanIf true then system environment variables are exposed for use.
build_commandtextThe build command for this project.
created_attimestamp with time zoneTime when the project was created.
dev_commandtextThe dev command for this project.
directory_listingbooleanIf true then the project is listed in the Vercel directory.
envjsonbEnvironment variables for the project.
frameworktextFramework used in the project, e.g. nextjs.
idtextID of the project.
install_commandtextThe install command for this project.
latest_deploymentsjsonbInformation about the latest deployments of the project.
linkjsonbDetails of the link from this project to a source code repository.
livebooleanIf true, the project is live.
nametextName of the project.
node_versiontextNode version used by the project, e.g. 16.x.
output_directorytextDirectory where output of the build will go.
password_protectionjsonbPassword protection information, if enabled.
permissionsjsonbPermissions settings.
public_sourcebooleanIf true, the project is linked to a public source.
root_directorytextRoot directory for the build process.
serverless_function_regiontextRegion where serverless functions will be deployed.
source_files_outside_root_directorybooleanIf true then source files are outside the root directory.
sso_protectionjsonbSSO protection information, if enabled.
targetsjsonbTargets of the build.
updated_attimestamp with time zoneTime when the project was last updated.