steampipe plugin install vercel

Table: vercel_project - Query Vercel Projects using SQL

A Vercel Project is a workspace where you can deploy your applications or websites. Each project is linked to a Git repository and contains settings for deployments, domains, environment variables, and more. Projects can be owned by an individual or a team, and they provide the basis for continuous deployment in Vercel.

Table Usage Guide

The vercel_project table provides insights into projects within Vercel. As a DevOps engineer, explore project-specific details through this table, including project names, types, and owners. Utilize it to uncover information about projects, such as their configurations, associated Git repositories, and their continuous deployment settings.

Examples

List all projects

Explore the various projects, including their respective frameworks and last updated dates, to keep track of the latest changes and developments. This can help in understanding the current state of each project and facilitate strategic planning.

select
name,
framework,
updated_at
from
vercel_project;
select
name,
framework,
updated_at
from
vercel_project;

Projects not updated in the last year

Identify projects that have not seen any updates in the past year. This can be useful to determine which projects may be inactive or outdated.

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

Latest deployments

Gain insights into the most recent project deployments, including when they were created, by whom, and their associated URLs. This is particularly useful for tracking project updates and ensuring accountability within your team.

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;
select
datetime(
(json_extract(d.value, '$.createdAt')) / 1000,
'unixepoch'
) as created_at,
name,
json_extract(d.value, '$.url') as url,
json_extract(json_extract(d.value, '$.creator'), '$.username') as creator_username
from
vercel_project as p,
json_each(latest_deployments) as d
order by
created_at desc;

Current production target by project

This query is useful for gaining insights into the production targets of different projects. It arranges them in order of their names, providing a clear view of the production status, including the GitHub repository details, which can aid in project management and progress tracking.

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;
select
name,
datetime(
(json_extract(targets, '$.production.createdAt')) / 1000,
'unixepoch'
) as created_at,
json_extract(targets, '$.production.url') as url,
json_extract(targets, '$.production.meta.githubOrg') as github_org,
json_extract(targets, '$.production.meta.githubRepo') as github_repo,
json_extract(targets, '$.production.meta.githubCommitSha') as github_commit_sha,
json_extract(
targets,
'$.production.meta.githubCommitAuthorName'
) as github_commit_author_name,
json_extract(targets, '$.production.meta.githubCommitMessage') as github_commit_message
from
vercel_project
order by
name;

List all project environment variables

Explore the environmental variables associated with each project to understand how configurations are set and managed. This is useful for auditing and maintaining consistency across various projects.

select
name,
e ->> 'key',
e ->> 'target'
from
vercel_project as p,
jsonb_array_elements(env) as e;
select
name,
json_extract(e.value, '$.key'),
json_extract(e.value, '$.target')
from
vercel_project as p,
json_each(env) as e;

Environment variables that are not encrypted

Gain insights into the environment variables within your Vercel project that are not encrypted. This query is useful for identifying potential security risks in your project's configuration.

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

Production environment variables older than 90 days

Explore which production environment variables have not been updated in the last 90 days. This can help identify potential areas of neglect or outdated configurations in your project.

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';
select
name,
json_extract(e.value, '$.key'),
json_extract(e.value, '$.target'),
datetime(
(json_extract(e.value, '$.createdAt') / 1000),
'unixepoch'
)
from
vercel_project as p,
json_each(env) as e
where
json_extract(e.value, '$.target') = 'production'
and datetime(
(json_extract(e.value, '$.createdAt') / 1000),
'unixepoch'
) < datetime('now', '-90 days');

Projects by framework environment variables older than 90 days

Explore which frameworks are most commonly used in your Vercel projects. This can help you understand the popularity and usage of different frameworks within your projects.

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

Schema for vercel_project

NameTypeOperatorsDescription
_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.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- vercel

You can pass the configuration to the command with the --config argument:

steampipe_export_vercel --config '<your_config>' vercel_project